Re: Pagination on query from on-disk/partitioned data (HDB)

[megan_mcp]

Hi ,

A suggestion could be to potentially go with a 2 step approach where you first find what the indices are (per partition) for the filter clause being applied – so not pulling the full result into memory. Then, based on all the indices returned, split these up into appropriate pages:

q)n:1000000 
q)tbl:([]time:n?.z.t;sym:n?`3;price:n?100f) 
q).Q.dpft[`:.;;`sym;`tbl]@' 2024.02.12 2024.02.13 / write out two partitions `tbl`tbl 
q)delete tbl from `. `. 
q)l . 
q)s:-100?sym / filter on 100 random symbols 
q) q)pages:10 
q)pageFilters:ungroup select idx:{ceiling[count[x]%y] cut x}[x;pages] by date from select date,i from tbl where sym in s / simple where clause used here on sym 
q) 
q).Q.cn tbl 
q).Q.pn tbl| 10000000 10000000 
q) 
q)pageTable:{.Q.ind[tbl;(sum .Q.pn[`tbl] where date=x[`date])+x[`idx]]} 
q)pageTable pageFilters[0] / page 0 
date sym time price 
------------------------------------ 
2024.02.12 bbb 03:28:48.269 62.03014 
2024.02.12 ghl 05:24:11.985 19.59907 
2024.02.12 iah 11:03:44.330 8.724017 
q)pageTable pageFilters[1] / page 1 etc 
date sym time price 
------------------------------------ 
2024.02.12 iog 07:02:54.903 77.50292 
2024.02.12 kec 08:49:43.130 41.2317 
2024.02.12 kgi 10:16:09.595 34.10485 
q) 
q)pageTable each pageFilters / page all filters

 

https://code.kx.com/q/ref/dotq/#qind-partitioned-index
https://code.kx.com/q/ref/dotq/#cn-count-partitioned-table
https://code.kx.com/q/ref/dotq/#pn-partition-counts 

Let me know if this works!

Many thanks,

Megan