Performance Benchmarks

What the Numbers Actually Look Like

Attributes are often described qualitatively ("faster lookups", "less memory"). This section gives concrete measurements so you can see the real magnitudes.

The tables below come from KX's white paper Columnar database and query optimization (on code.kx.com). It uses simulated NYSE TAQ data for S&P 500 stocks — roughly 10,000 trades and 50,000 quotes for each stock each day — in a database partitioned by date. Absolute timings depend on the hardware; the relative differences are what matter.

A Simple Self-Contained Benchmark

You can see the effect of the sorted attribute yourself on a 100-million-item list:

x: til 100000000           / 100 million items, no attribute
\t x ? last x              / linear scan: tens of milliseconds or more

s: `s#x                    / apply the sorted attribute
\t s ? last s              / binary search: ~0 ms

(Run the first timing before applying `s#: because x is already sorted, `s#x sets the attribute on x itself rather than on a copy — see Gotchas & Edge Cases.)

To find the last value, ? on the unattributed list has to check every item. With the sorted attribute it uses binary search, which needs only about 27 comparisons for 100 million items (log₂ 100,000,000 ≈ 27). The exact time of the first search depends on your machine.

Measuring with \ts

\t reports elapsed time; \ts reports elapsed time and the memory used to evaluate the expression ("workspace"):

t: ([] sym:10000000?`AAPL`GOOG`MSFT; price:10000000?100.0)

\t select from t where sym=`AAPL        / milliseconds
\ts select from t where sym=`AAPL       / milliseconds, bytes

Workspace is often a better warning sign than time alone. A query that takes 5ms but allocates hundreds of megabytes can exhaust memory if many users run it at once, or if it is run over a wide date range.

Sorted Attribute on Time

From the white paper: an in-memory table, filtering on a time range.

With s#, the query binary-searches for the ends of the range, so time and workspace stay constant as the table grows. Without it, workspace grows with the table, in steps — because kdb+'s buddy memory allocator allocates in powers of two.

Grouped Attribute on Sym

From the white paper: an in-memory table, selecting all rows for one symbol.

That is roughly 15–24× faster, and over 100× less workspace.

Parted Attribute in a Partitioned Database

From the white paper: selecting one symbol for one date, as the number of date partitions in the database grows, with the two constraints in either order.

Two things stand out:

• With p# on sym and the date constraint first, the query cost stays constant however many dates the database holds.
• Putting sym first is slower in every case, and gets worse as the database grows.

Unique Attribute

From the white paper: selecting one row by value from an in-memory table.

Below about a million rows, a scan is fast enough that u# makes little difference to the time. Above that, the gap grows linearly with the table.

Memory Overhead by Attribute Type

See The Four Attributes for a way to measure this yourself.

Why the Order of Constraints Matters

In a partitioned database, date is a virtual column: it is not stored in any file, but derived from the partition directory names.

When date is the first constraint, kdb+ uses it to choose which partitions to read at all, before touching any column data. When date comes later, the earlier constraints are evaluated across every partition first, and kdb+ has to create the date values as an in-memory vector so it can compare them — which costs extra work and memory, and gets worse as more partitions are added. That is why the white paper's sym-first timings grow with the number of dates, while the date-first timings stay flat.

The rule: in a partitioned database, always put the partition column (date) first in the where clause.