title: SQLite Performance for querying large amounts of tick data
body: <p>I have a database with a decently large amount of tick data of all 229 stocks in the S&amp;P/TSX Composite Index. For reference, a single day's worth of data is about 13 million rows.</p>
  <p>here's a snippet of data:</p>
  <pre><code>ID  TICKER  TIME                     TYPE      VALUE  SIZE  EXCHANGE  CONDN_CODES  BUY_BRK  SELL_BRK
  --  ------  -----------------------  --------  -----  ----  --------  -----------  -------  --------
  15  ABX CN  2021-05-07T13:30:00.000  BEST_BID  29.21  918   T
  16  ABX CN  2021-05-07T13:30:00.000  BEST_BID  29.21  917   T
  17  ABX CN  2021-05-07T13:30:00.000  BEST_BID  29.21  927   T
  18  ABX CN  2021-05-07T13:30:00.000  BEST_BID  29.21  928   T
  19  ABX CN  2021-05-07T13:30:00.000  TRADE     29.21  100   T         OPA,XT       85       85
  20  ABX CN  2021-05-07T13:30:00.000  TRADE     29.21  100   T         OPA,XT       79       79
  21  ABX CN  2021-05-07T13:30:00.000  TRADE     29.21  200   T         OPA,XT       79       79
  22  ABX CN  2021-05-07T13:30:00.000  TRADE     29.21  100   T         OPA,XT       79       79
  </code></pre>
  <p>One of the things that's interesting is analyzing whether a trade occurred on the bid side, or on the offer side. So I wrote a query that will append the latest best bid, and the latest best ask, to each trade. When running the query, the output looks like this:</p>
  <pre><code>ID      TICKER  TIME                     TYPE   VALUE  SIZE  EXCHANGE  CONDN_CODES  BUY_BRK  SELL_BRK  LATEST_BBID  LATEST_BASK
  ------  ------  -----------------------  -----  -----  ----  --------  -----------  -------  --------  -----------  -----------
  267795  AC CN   2021-05-07T13:45:03.000  TRADE  24.5   100   T                      2        1         24.5         24.51
  267797  AC CN   2021-05-07T13:45:03.000  TRADE  24.5   100   C                      1        79        24.5         24.51
  267803  AC CN   2021-05-07T13:45:03.000  TRADE  24.5   1     B         MN           80       79        24.49        24.5
  267817  AC CN   2021-05-07T13:45:05.000  TRADE  24.5   200   T                      79       80        24.49        24.5
  267834  AC CN   2021-05-07T13:45:07.000  TRADE  24.52  50    B         MN           80       212       24.5         24.52
  267837  AC CN   2021-05-07T13:45:07.000  TRADE  24.5   100   T                      2        79        24.5         24.51
  267858  AC CN   2021-05-07T13:45:08.000  TRADE  24.48  100   X                      79       9         24.48        24.5
  267859  AC CN   2021-05-07T13:45:08.000  TRADE  24.48  100   O                      1        9         24.48        24.5
  </code></pre>
  <p>So here's the problem:</p>
  <p>The query is pretty slow. I run it on a per-stock basis, and each query takes a couple of seconds. Iterating over 229 different stocks, doing some calculations, and then outputting some summary stats take around 5 minutes, which is too slow for my liking.</p>
  <p>the query is written as follows (ticker and dates are hardcoded for ease of reading):</p>
  <pre><code>SELECT 
      ID, TICKER, TIME, TYPE, VALUE, SIZE, EXCHANGE, CONDN_CODES, BUY_BRK, SELL_BRK, 
      (SELECT VALUE from TICKDATA AS td1 WHERE td1.ID = (SELECT max(ID) FROM TICKDATA AS td2 WHERE td2.TICKER = tickdata.TICKER AND td2.ID &lt; tickdata.ID AND TYPE = &quot;BEST_BID&quot;) ) AS LATEST_BBID,
      (SELECT VALUE from TICKDATA AS td1 WHERE td1.ID = (SELECT max(ID) FROM TICKDATA AS td2 WHERE td2.TICKER = tickdata.TICKER AND td2.ID &lt; tickdata.ID AND TYPE = &quot;BEST_ASK&quot;) ) AS LATEST_BASK
  FROM tickdata
  WHERE 
      TICKER = &quot;AC CN&quot; AND 
      TYPE = &quot;TRADE&quot; AND
      TIME &gt; &quot;2021-05-07T13:45:00.00&quot; AND
      TIME &lt;= &quot;2021-05-07T14:00:00.00&quot;;
  </code></pre>
  <p>so what i tried next is using a window function, written as follows:</p>
  <pre><code>SELECT
      ID, TICKER, TIME, TYPE, VALUE, SIZE, EXCHANGE, CONDN_CODES, BUY_BRK, SELL_BRK,
      LAG(VALUE) 
          OVER
              (
                  PARTITION BY TICKER
                  ORDER BY (CASE WHEN TYPE = &quot;BEST_BID&quot; THEN 1 ELSE 2 END), ID
              ) AS LATEST_BBID
  FROM tickdata
  WHERE 
      TICKER = &quot;AC CN&quot; AND 
      TYPE = &quot;TRADE&quot; AND
      TIME &gt; &quot;2021-05-07T13:45:00.00&quot; AND
      TIME &lt;= &quot;2021-05-07T14:00:00.00&quot;;
  </code></pre>
  <p>but the window function is even slower!</p>
  <p>so my question comes down to the following:</p>
  <p>-Can I re-write the query or window function in such a way as to substantially increase performance?</p>
  <p>-If there is no way to do this with a query or window function in a performant way, can/should I use something like a trigger to automatically update the latest best bid / latest best offer during the INSERT operation? After that I could just SELECT * and get all the data without SQLite performing any calculations on the fly.</p>
  <p>-Lastly, if that wouldn't work either, should I use some other type of database?</p>
  <p>thank you for your help!</p>