Sort a column for all dates for a partitoned table

Community Support
1

Hi all, I have a partitoned table with data worth more than a year and it has a time column. I notice that the time range query like below are very slow or times out > 5 sec. select from trade where date = 2017.08.03, time within (08:00:00;08:01:00) meta trade c t f a date d sym s p time n …and few more columns I read online that setting sort attribute might help in such cases. So I tried to set the sort attribute using dbmaint.q but got the below error. setattrcol[:.;trade;time;s] ERROR:s-fail s-fail invalid attempt to set sorted attribute I think the above error is because the time column is not sorted and I need to sort the time column for all the dates so that the sort attribute can be applied to the time column. Is there a way to sort the time column for all the dates instead of manually sorting each date? Secondly, going forward, is there any way to ensure the time column is always sorted in realtime tick data as well as hostorical? Is there a way to force sorting on a particular column on a save down? Appreciate any help with resolving this!!! Thanks, Ankur

2

Sorting can be done whenever a table is updated/saved down. It’s a matter of changing the upd or related functions to add in the sorting step. Alternatively, for hdb, you can choose to sort the on-disk data after it’s been saved down.

3

Hi Ankur,

You can use xasc to sort data directly on disk:

https://code.kx.com/wiki/Reference/xasc#Sorting\_data\_on\_disk

You can wrap it in a lambda and use .Q.par to build the filepath for each partition and table you’re interested in and sort each of those based on the column you want:

    q){[c;d;t;p]c xasc .Q.par[d;p;t]}[`time;`:.;`trade] each date

:./2018.04.02/trade:./2018.04.03/trade`:./2018.04.05/trade

It is worth pointing out, however, that usually on-disk data has the parted attribute (p) applied to the sym column, because queries are more likely to use the sym column after the date clause. This is evident in the functions .Q.dpft and .Q.hdpf where the f in each is the field to apply the p attribute to.

https://code.kx.com/wiki/DotQ/DotQDothdpf

Hope this helps,
James

4

Thanks for the reply James.=20 This worked for me. But as you had pointed out, the sym column lost it=E2= =80=99s parted attribute. So, are we saying we can have attribute only one = column? I tried to set the parted attribute back on sym column and it faile= d giving me u-fail error(invalid attempt to set unique attribute). Is there= any way to restore this attribute back? Lastly, I was planning to add the = parted attribute on couple of other columns to optimize the query further b= ut it seems it would reset the previous attribute? Thanks again.

5

Thanks Flying. I checked my r.q and it seems I am using the standard script which has the following u.end: / end of day: save, clear, hdb reload .u.end:{t:tables.;t@:where g=attr each t@:sym;.Q.hdpf[$“:”,.u.x 1;:.;x;sym];@[;sym;g#] each t;}; Any suggestions on how this can be modified to sort on certain column before save it down to disk?