CODEWITHSUNDEEP
sqliteandroid-sqlite
Squonk
Squonk

Reputation: 48871

Making this SQLite query more efficient

I develop an Android app which allows a user to view a TV Guide in various ways. One of the views is a Now/Next view which is basically a list with separators (showing channel names) with each separator followed by the current / next TV show details.

The problem I'm having is with testing my own line-up of 67 channels it takes anything from 20-30 seconds to generate the view (horribly slow for mobile apps) and I know some of my users have ~200+ channels.

The SQL query I use to get the Now/Next details for 1 channel is as follows...

SELECT
    b.oid AS _id,
    b.channel_oid,
    b.title,
    b.start_time,
    b.end_time
FROM epg_event b,
    (
        SELECT
            MIN(a.start_time) AS start_time,
            a.channel_oid,
            a.end_time
        FROM epg_event a
        WHERE a.start_time  > datetime('now')
          AND a.channel_oid = 10029
        GROUP BY a.channel_oid
        UNION
        SELECT
            MIN(a.start_time) AS start_time,
            a.channel_oid,
            a.end_time
        FROM epg_event a
        WHERE a.start_time <= datetime('now')
          AND a.end_time    > datetime('now')
          AND a.channel_oid = 10029
        GROUP BY a.channel_oid
    ) c
WHERE c.start_time  = b.start_time
  AND c.channel_oid = b.channel_oid
ORDER BY b.start_time

That seems pretty complex for a query where I just want to pull 2 records where the start time of the first is before (or equal to) the current time and end time is after current time (Now) and the second record has a start time which matches the end time of the first (Next).

I'm just wondering if there is a more efficient way to query for this sort of scenario.

The epg_event table is created with the following (in order to illustrate the schema)...

CREATE TABLE [EPG_EVENT] (
    [oid] integer PRIMARY KEY,
    [title] varchar(50),
    [subtitle] varchar(50),
    [description] varchar(50),
    [start_time] datetime,
    [end_time] datetime,
    [channel_oid] int,
    [unique_id] varchar(50),
    [rating] varchar(50),
    [original_air_date] datetime,
    [season] int,
    [episode] int,
    [dvb_service_event_id] int,
    [dvb_table_version] int,
    [genres] varchar(50)
)

The query shown will return...

_id, channel_oid, title, start_time, end_time
10467376, 10029, Ripper Street, 2013-01-20 21:00:00, 2013-01-20 22:00:00
10467377, 10029, BBC News; Regional News and Weather, 2013-01-20 22:00:00, 2013-01-20 22:25:00

Upvotes: 0

Views: 288

Answers (1)

mvp
mvp

Reputation: 116177

As far as I can tell, this part of your query is supposed to get any show that either still being aired or has not started yet:

    SELECT
        MIN(a.start_time) AS start_time,
        a.channel_oid,
        a.end_time
    FROM epg_event a
    WHERE a.start_time  > datetime('now')
      AND a.channel_oid = 10029
    GROUP BY a.channel_oid
    UNION
    SELECT
        MIN(a.start_time) AS start_time,
        a.channel_oid,
        a.end_time
    FROM epg_event a
    WHERE a.start_time <= datetime('now')
      AND a.end_time    > datetime('now')
      AND a.channel_oid = 10029
    GROUP BY a.channel_oid

But, these 2 conditions are equivalent to condition that show has not ended yet, or to:

    SELECT
        MIN(a.start_time) AS start_time,
        a.channel_oid,
        a.end_time
    FROM epg_event a
    WHERE a.end_time    > datetime('now')
      AND a.channel_oid = 10029
    GROUP BY a.channel_oid

Now, here you filter by known channel_oid and yet GROUP BY it - why? It should be simplified as

    SELECT
        MIN(a.start_time) AS start_time,
        a.channel_oid,
        a.end_time
    FROM epg_event a
    WHERE a.end_time > datetime('now')
      AND a.channel_oid = 10029

As far as I can tell, you are probably better off replacing datetime('now') with CURRENT_TIMESTAMP. This yields your final SQL as:

SELECT
    b.oid AS _id,
    b.channel_oid,
    b.title,
    b.start_time,
    b.end_time
FROM epg_event b,
    (
        SELECT
            MIN(a.start_time) AS start_time,
            a.channel_oid,
            a.end_time
        FROM epg_event a
        WHERE a.end_time > CURRENT_TIMESTAMP
          AND a.channel_oid = 10029
    ) c
WHERE c.start_time  = b.start_time
  AND c.channel_oid = b.channel_oid
ORDER BY b.start_time

To make it work fast, make sure to create proper indexes. This answer can give you some ideas how to optimize it, but on first look, you should have at least following compound indexes on epg_event table: (channel_oid, start_time) and (channel_oid, end_time).

Upvotes: 1

Related Questions