I have tested with a sample database and I can get a slight improvement if I the use `p#
attribute:
update `p#inst from `inst`time xasc price_hist_5min_bars
In this case `p#
has advantages over `g#
as the symbols are in order and not dispersed. See the wiki entry on attributes for more information.
Better performance can be achieved if you restructure the table. Using the following table as an example:
q)n:10000000
q)t:2!update `p#sym from `sym`time xasc ([]sym:n?-100?`3;time:.z.d+n?1D;price:n?1000f)
We can set a benchmark using select
:
q)\ts:1000 select from t where sym=`bak,time=2017.11.29D23:59:59.520923942
210 1180496
You can restructure by grouping all of the time and price values for each sym, sorting on time:
q)show r:select `s#time,price by sym from t
sym| time ..
---| --------------------------------------------------------------------------------------------..
aan| `s#2017.11.29D00:00:00.131421536 2017.11.29D00:00:00.214382261 2017.11.29D00:00:00.914720445..
...
Working with this restructured data, we can return the price with:
q)r[`bak][`price] r[`bak][`time]?2017.11.29D23:59:59.520923942
948.3733
q)\ts:1000 r[`bak][`price] r[`bak][`time]?2017.11.29D23:59:59.520923942
4 1824
Where the index of the record within the sym grouping is given by:
q)r[`bak][`time]?2017.11.29D23:59:59.520923942
100638
Obviously the above example only returns and atom and not a table. If you wished to have a table you could try something like this:
q)flip (),/:@[;`sym;:;`bak]flip[r`bak]r[`bak][`time]?2017.11.29D23:59:59.520923942
time price sym
------------------------------------------
2017.11.29D23:59:59.520923942 948.3733 bak
q)\ts:1000 flip (),/:@[;`sym;:;`bak]flip[r`bak]r[`bak][`time]?2017.11.29D23:59:59.520923942
7 2688
But it depends on how you want your output to look.