Timing a kdb Query

Often I get asked:

• How can I time how long to run a query?
• Does kdb have a profiler?
• How can I tell which query is faster?

The answer to all these is \ts as we'll look at below.

Timing a Query

First let's create a table t.

q)n:1200300 q)t:([] a:til n; s:n?`3; ex:n?4?`3; size:n?100; price:n?1000.) q)t a s ex size price ----------------------- 0 ddd ifb 37 537.5576 1 bbp ifb 11 819.6064 2 dpk peg 80 923.2379 3 opd noi 6 464.3017 4 fcf keo 46 21.71885 5 chd peg 95 979.3188 6 kck ifb 52 89.70381 7 omo keo 29 345.0159 ..

To time how long a query takes to run we could use the system t or \t command. Running a command prefixed with \t causes it to return the time that it took to run in milliseconds. We can then go one step further and use the \t:n command to run the same query n times and return the total time in milliseconds. As shown below:

q)select from t where s=`ddd a s ex size price --------------------------- 0 ddd ifb 37 537.5576 443 ddd ifb 37 383.565 1154 ddd peg 12 385.8024 4393 ddd peg 83 632.635 6913 ddd ifb 74 938.7186 7934 ddd ifb 42 551.4558 16015 ddd noi 99 411.9647 16595 ddd ifb 54 234.2965 .. q)\t select from t where s=`ddd 3 q)\t select from t where s=`ddd 3 q)/ 3 milliseconds is too quick to really know. Run it 100 times q)\t:100 select from t where s=`ddd 293 q)\t:100 select from t where s=`ddd 297

Finding the memory required for a Query

If we want to profile both how much time and how much memory a query takes to run we can use \ts:n. This returns two values, the time taken in milliseconds and the space required in bytes.

q)\ts select from t where s=`ddd 4 2101600 q)\ts:100 select from t where s=`ddd 298 2101536

One area that we have used this is as part of the qunit test framework for kdb, we log how much memory and time a test takes to run. The framework also allows specifying the maximum time/memory that a unit test should take.

Another interesting idea to try is automatically breaking functions into statements and timing each part, or creating a tool that profiles each line. qStudio provides a basic version of this: