aj with more than one syms?

Community Support
1

If I follow the recommendations onhttp://code.kx.com/wiki/Reference/aj#RemarksI can join trades to partitioned on-disk quotes very quickly if i havep#sym with something likeq) aj[symtime; t; select from q where date 15.01.01]but if I want to use a second sym, the query is much slower (500x):q) aj[sym1sym2time; t; select from q where date 15.01.01]even if my quotes table has been sorted by sym1sym2time.Is there any way to make aj faster other than merging sym1 and sym2 intoa single column and applying p#sym1sym2?Thanks,Josh

2

best is to avoid 3col aj; it does a linear search on the 2nd col for each row.

So try something like the following (red highlight should be best)

$more a.q

/ setup example data

n: 50000000;

names: 4000?`8;

a:(time:asc n?(“t”$1000*til 3602); a:`g#n?8000; b:n?names; c:n?10f);

b:raze{update time: x[time], a: x[a] from ( b: names)} each 10?a; //2-3 seconds for 40K rows

\ts updateg#a fromb;

/ compare different approaches

\ts r1:raze{aj[btime;select from b where a=x;select from a where a=x]}each distinct b`a


\ts r2:raze{aj[btime;select from b where a=x;update g#b from select from a where a=x]}each distinct ba


\ts r:aj[`a`b`time;b;a]

$ q a.q -g 1

KDB+ 3.3t 2014.12.04 Copyright (C) 1993-2014 Kx Systems

m64/ 

0 1377296

263 2985776

18 2986048

1163 1836464

q)r~/:(r1;r2)

11b