Prepare a Table to Maximise Window Join Performance

Question

How should the trades table be prepared to maximise the performance of a window join?

For example, with a two-symbol version of the trades table:

trades:([] sym:`A`B`A`B; time:09:30:00.000 09:30:05.000 09:30:10.000 09:30:15.000; price:100.5 50.2 100.7 50.1)

Solution

`sym`time xasc `trades

update `p#sym from `trades

Explanation

wj/wj1 works by matching on the grouping column(s) (here sym) and then scanning forward through time within each group to find the rows that fall inside each window — so two things make that scan efficient:

• Sort by the match column, then by time — `sym`time xasc `trades sorts trades by sym first, then time within each sym, so every group's rows are both contiguous and time-ordered. This is the same sortedness requirement a binary-search-based join relies on more generally — without it, the window scan has to search the whole table for matches instead of a contiguous slice.

• Apply the parted attribute to the match column — update `p#sym from `trades tells q that equal sym values are already grouped into contiguous blocks (which is now true, since the table is sorted), letting it jump straight to a symbol's block instead of scanning from the start.

KX's reference for wj states that the right-hand (lookup) table should be sorted by sym then time, with the parted attribute on sym, as above. It also warns that window joins over multiple symbols should only be used with this kind of p# schema: with the grouped attribute (`g#) typical of a real-time database, the results are undefined.