Showing results for 
Search instead for 
Did you mean: 

upsert into a sorted table

New Contributor
Hello,I have a large order book table. I want to be able to do fast upsertsas well as fast selects on the top few levels when ordered by price.In the following example, the table is keyed on the order id and sortedby price. How can I efficiently upsert a new entry without having toresort the entire table?q)n:100000; t:update `g#id from `px xasc ([id:neg[n]?0Ng] px:n?1f; qty:n?100)q)meta tc | t f a---| -----id | g gpx | f sqty| j q)`t upsert (rand 0Ng;0.12345;10)`tq)meta tc | t f a---| -----id | g gpx | f qty| j Thanks,Josh

New Contributor
Hi Josh,

You could keep a shorter table, s, and upsert into both t and s - but only upsert into s when the order is 'at the top' then resort.
when s gets too big, prune it.   hopefully, it's not too big a job to query s first, then if that fails, check t.

it's an optimization similar to the rdb/hdb separation (except there's duplication and sorting)
ta, jack

New Contributor
Hi Josh,

I saw the idea of keying the order book by `px here:
I assume that key maintains the sort automatically - haven't tested yet.
In your case, perhaps it would make sense to have composite key `px`id.

Let me know how that works for you, I am solving similar problem.