How Can the Following aj Be Optimised? aj[`sym`time;t;q]

Question

How can the following as-of join be optimised, given in-memory tables t (trades) and q (quotes)?

aj[`sym`time; t; q]

For example:

t: ([] sym:`AAPL`MSFT`AAPL; time:09:30:01 09:30:02 09:30:05; qty:100 200 300)
q: ([] sym:`MSFT`AAPL`AAPL`MSFT; time:09:30:00 09:30:00 09:30:04 09:30:01; bid:410.0 185.1 185.3 410.2)

Solution

/ Step 1: sort the quotes by sym, then by time within sym
q: `sym`time xasc q

/ Step 2: apply the grouped attribute to sym in the quote table
update `g#sym from `q

/ Step 3: check
attr q`sym      / `g

/ Step 4: run the join, reading only the quote columns you need
aj[`sym`time; t; select sym, time, bid from q]

Explanation

aj[symtime; t; q] is the most common temporal join in kdb+ tick systems — typically attaching the prevailing quote to each trade.

Lay out the right-hand table as KX recommends. KX's reference for aj recommends that, for an in-memory right-hand table, the first join column (sym) has the grouped attribute and the remaining column (time) is sorted within each sym. (For an on-disk table, use the parted attribute on sym instead; KX notes that g# does not help on disk.) KX warns that departing from this layout incurs a severe performance penalty. Sorting with `sym`time xasc and then applying g# to sym gives exactly that layout.

The sorted attribute (s#) on time is not what aj needs. After sorting by sym then time, the time column as a whole is not in ascending order, so s# could not even be applied to it.

Order the join columns correctly. KX also stresses that the columns in the first argument must be in the right order — exact-match column(s) first, the as-of column (time) last — or performance suffers severely.

Select only the columns you need from q. aj copies the matching values of every non-join column of q into the result. If you only need bid, pass select sym, time, bid from q rather than the whole table.

Restrict q to the relevant time range. If q covers a whole day but t covers only a short window, filtering q first means less data to search:

q2: select from q where time within (min t`time; max t`time)

Be careful when doing this: a quote that arrived before the first trade may be the prevailing quote for it, so include a margin before min ttime` (or the last quote per sym before the window) if you need those matches.