cancel
Showing results for 
Search instead for 
Did you mean: 

aj with more than one syms?

joshmyzie2
New Contributor
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 have`p#sym with something likeq) aj[`sym`time; 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[`sym1`sym2`time; t; select from q where date 15.01.01]even if my quotes table has been sorted by `sym1`sym2`time.Is there any way to make aj faster other than merging sym1 and sym2 intoa single column and applying `p#sym1sym2?Thanks,Josh
1 REPLY 1

charlie
New Contributor II
New Contributor II
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 update`g#a from`b;

/ compare different approaches
\ts r1:raze{aj[`b`time;select from b where a=x;select from a where a=x]}each distinct b`a

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

\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