SQLite – Retrieve Data from Two Tables with Same Structure

optimizationperformancequery-performancesqlite

I have a SQLite database where I fetch data from two tables with the following query.

select ie.* 
from (select * 
      from History where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from 
      union all 
      select * 
      from Pending where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from
     ) ie 
order by TimeStampCome desc LIMIT 100 OFFSET 1

Is this the best and most efficient way? I have stored alarm objects in the database. So there can easily be more than 1 million entries.

Best Answer

The outer-level ORDER BY will probably cause the entire combined row set to be sorted – at least to the extent that will enable the server to find the last 100 TimeStampCome entries starting from the penultimate one. Sorting a million entries cannot be cheap, of course.

I would try reducing the combined set by applying a LIMIT to each leg of the UNION ALL query. Note that an ORDER BY cannot be applied directly to an individual SELECT that is a UNION leg. You will have to use more nesting – either a derived table or a CTE – to apply the ORDER BY and then select from that derived table/CTE. Here is a derived table solution:

SELECT
  ie.*
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          *
        FROM
          History
        WHERE
          Station = @station
          AND TimeStampCome <= @till
          AND TimeStampCome >= @from
        ORDER BY
          TimeStampCome DESC
          LIMIT 101
      ) AS s
    UNION ALL
    SELECT
      *
    FROM
      (
        SELECT
          *
        FROM
          Pending
        WHERE
          Station = @station
          AND TimeStampCome <= @till
          AND TimeStampCome >= @from
        ORDER BY
          TimeStampCome DESC
          LIMIT 101
      ) AS s
  ) AS ie
ORDER BY
  TimeStampCome DESC
  LIMIT 100
  OFFSET 1
;

And this is a variation that uses CTEs, if you are using a SQLite version that supports Common Table Expressions:

WITH
  HistoryFiltered AS
  (
    SELECT
      *
    FROM
      History
    WHERE
      Station = @station
      AND TimeStampCome <= @till
      AND TimeStampCome >= @from
    ORDER BY
      TimeStampCome DESC
      LIMIT 101
  ),
  PendingFiltered AS
  (
    SELECT
      *
    FROM
      Pending
    WHERE
      Station = @station
      AND TimeStampCome <= @till
      AND TimeStampCome >= @from
    ORDER BY
      TimeStampCome DESC
      LIMIT 101
  )
SELECT
  ie.*
FROM
  (
    SELECT * FROM HistoryFiltered
    UNION ALL
    SELECT * FROM PendingFiltered
  ) AS ie
ORDER BY
  TimeStampCome DESC
  LIMIT 100
  OFFSET 1
;

Each individual SELECT is using LIMIT 101 rather than LIMIT 100, because at first it is unknown which of them will end up having the latest TimeStampCome entry that will be omitted by the outer OFFSET 1. Therefore, each source row set must provide as much data as necessary for the ultimate SELECT.

Related Question