2

I have created a trade database on my local machine, partitioned by date.

select from trade where date=x

Takes about 100 ms, but when I do:

select from trade where date=x,sym=`alpha

it takes ~1-3 seconds.

Is there a trick I'm missing (using `g#sym gives an error 'par).

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Naveen Sharma
  • 1,057
  • 12
  • 32

1 Answers1

1

You can use dbmaint.q to apply attributes to to partitioned tables. See here . Specifially look at the setattrcol function. Adding an attribute will speed up the query significantly. I would suggest using a p# on the id column if possible. This should be faster for look-ups but is more difficult to maintain.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
user1895961
  • 1,176
  • 9
  • 22
  • I tried setattrcol[`:/kdb/testdb;`trade;`sym;`p#]. It gives me a type error. I tried it with sample db given with dbmaint.q and it worked fine but with my sample db it gives an error of type. Am trying to check if i missed something while creating the sample db. if you know any obvious mistakes, pls advise. – Naveen Sharma May 11 '13 at 09:29
  • Can you give the meta of the table? In order to apply p#, you need to ensure the sym column is ordered correctly, i.e. all records for each sym beside each other. One other thing, i believe in your call above, you do not need the #. is should be just [:/kdb/testdb;trade;sym;p] (with the necessary back ticks). – user1895961 May 11 '13 at 12:51
  • The meta is as follows: c t f a date "d" s trdId "f" sym "s" symType "s" expiry "d" option "s" corpAction "s" strike "f" time "v" price "f" quantity "f" – Naveen Sharma May 11 '13 at 14:38
  • you are right, its not grouped by sym. do i need to do a `g#. or should i add a `g# in the script creating the database before its written to the disk. i tried only p (without the #) as you mentioned but it gave me an error of u-fail.Studio Hint: Possibly this error refers to cannot apply `u# to data (not unique values), e.g `u#1 1. – Naveen Sharma May 11 '13 at 14:43
  • Set the attribute after it's written to disk. The g# would not require any maintenance, i.e. an update will not remove the attribute. If you wanted to use the p#, the easiest way to make sure all the sym records are beside each other would be to sort, although if it's a large data set, this would take some time. It would also need to be sorted, and the attribute re-applied after every update. – user1895961 May 11 '13 at 14:55
  • You don't need to sort the table to apply the g#. You should set the attribute using the proc from dbmaint.q as above. If you want to use the p#, you need to ensure all records for each sym are beside each other in each partition. One way of doing this is to sort it. The p# should give you better performance, however it will be lost on updates, while the g# will not. SO depending on how you use the db, you need to weigh up the pros and cons. – user1895961 May 11 '13 at 15:28
  • tried setattrcol[`:/kdb/testdb;`trade;`sym;`p] after sorting. this time it gives an error of stdout. – Naveen Sharma May 11 '13 at 15:32
  • stdout is a function defined in the dbmaint.q script, so it looks like its missing in your process. Have you loaded the entire script? – user1895961 May 11 '13 at 16:16
  • thanks a bunch. appreciate your patience in guiding me through. works like a charm now. 20-30 ms – Naveen Sharma May 11 '13 at 16:31