upsert into a sorted table

Community Support
1

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

2

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

3

Hi Josh,

I saw the idea of keying the order book by `px here: http://www.firstderivatives.com/downloads/q\_for\_Gods\_Nov\_2012.pdf

I assume that key maintains the sort automatically - haven’t tested yet.

In your case, perhaps it would make sense to have composite key pxid.

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

Thanks,

Daniel