Search code examples
kdb

Point in time queries in kdb

I often need to select the last row in a partitioned table, up to a certain time. This is what I use:

select last A,last B,last C from mytable where date=2013.05.23,ts<10:30:00,A in (`Bob`Jane)

This query probably loads all `Bob`Jane rows up to 10:30:00, then returns the last row. Is there a more efficient way to run point-in-time queries in kdb?

Some benchmark data for my table:

/select all data
\t t: select ...
1724i

count t
2225311i

\t select ... ts<15:00:00
2040i

\t select ... ts<12:00:00
1092i

\t select ... ts<10:00:00
521i
Solution

  • Another alternative is to use the following:

    select A,B,C from mytable where date=2013.05.23,ts<10:30:00,A in (`Bob`Jane),i=last i

    Although I doubt there will be any performance improvement from what you currently have.

    What's more important is the type of table (in memory/ splayed/ partitioned (including how it's partitioned)) and the attributes (if any) that are used.