1
$\begingroup$

I am currently saving a sub 1 sec snapshot of an orderbook to my SQL db. However I have quite the trouble on figuring out the architecture of this DB

What I'm currently doing is saving a table with the data and naming the table the current epoch. As you can see this is a horrible way of doing it.

What would you guys recommend on doing to make this more efficient and less messy.

The data i'm currently storing is only: time,size,price Thanks in advance you guys are awesome!

$\endgroup$
2
$\begingroup$

Have you considered storing it by adding the additional fields ('level' or 'depth') and ('type'), then you can have a table which looks like this;

time    size    price    depth    type
xxx     100     100.03   1        bid
xxx     2000    100.025  2        bid
xxx     0       100.02   3        bid
xxx     33      100.035  1        offer
xxx     45      100.04   2        offer
xxx     550     100.045  3        offer

Additionally you can even compare the next timesnap with the previous and only create a new entry if data changes. The smaller your time difference the greater the data storage efficiency will be. For example suppose the next time step there were only two changes; a new level 3 bid and the offer had been reduced at level 1 due to trading or ortherwise, then you only create 2 rows;

xxx+1    250    100.02   3      bid
xxx+1    23     100.035  1      offer

You can always index the table for a given timestamp with a simple SQL;

SELECT TOP 1 * 
    FROM table 
    WHERE type='bid' AND level=1 AND time <= xxx+1
    ORDER BY time DESC
| improve this answer | |
$\endgroup$
  • $\begingroup$ First of all thank you a lot for the HQ response Maybe a dumb question but wouldn't this cause it to be very slow when trying to read data from X-Y? $\endgroup$ – QuantKan Mar 3 at 6:47
  • $\begingroup$ actually instead of 'bid' and 'offer' use 'b' and 'o' or TRUE and FALSE to save memory and possibly make the search more efficient $\endgroup$ – Attack68 Mar 3 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.