How Would You Troubleshoot a Slow In-Memory Query?

Question

A query against an in-memory RDB table is running much slower than expected. Walk through how you would diagnose and fix it.

Solution

/ Step 1: Time the query
\t select from trade where sym=`AAPL

/ Step 2: Check the attribute on the filter column
attr trade`sym     / should be `g — if ` then missing

/ Step 3: Apply the attribute if missing
@[`trade; `sym; `g#]
\t select from trade where sym=`AAPL    / re-time after fix

/ Step 4: Check table size
count trade                             / large tables amplify any inefficiency
meta trade                              / check column types and existing attributes

/ Step 5: Look for common slow patterns
\t select from trade where sym=`AAPL, price > 150.0    / compound filter

/ Step 6: If filtering on a computed column, pre-compute it
update spread: ask - bid from `quote    / add column once
\t select from quote where sym=`AAPL, spread > 0.05   / now indexed on sym first

Explanation

Slow in-memory queries in kdb+ fall into a small number of root causes:

1. Missing g# on sym — a very common cause. Without it, where sym=`AAPL scans every row of the table. With g#, kdb+ goes directly to AAPL's position list — for a 10M-row table with 5,000 symbols averaging 2,000 rows each, that is 2,000 rows instead of 10 million.

Always check attr trade`sym first. If it returns anything other than `g, apply g# and re-time.

2. Filtering on a computed expression. where price%size > 5.0 requires computing price%size for every row before filtering — no attribute can help here. Pre-compute the column with update:

update ratio: price%size from `trade
/ Now filter on the pre-computed column (attribute can be applied)
select from trade where sym=`AAPL, ratio > 5.0

3. like pattern matching. where sym like "AAPL*" always does a full scan — pattern matching cannot use any attribute. Use exact sym equality (where sym=`AAPL) instead, or restrict to a smaller set using the indexed sym first before applying the pattern filter.

4. Joining large tables without the right layout. For aj, KX recommends that the quote table be sorted by time within sym, with g# on sym in memory; departing from this carries a severe performance penalty. For lj, the right-hand table must be keyed — and for large lookups by a single key column, u# on that key helps.

5. Large result sets being further processed. If the query itself is fast but the downstream aggregation is slow (select wavg[size; price] by sym from trade), the bottleneck is not the filter but the aggregation over a large result set. Use more specific filters to reduce the result before aggregating.