Need help with some real-world SQL query results

[bgowland]

I posted here in Developers as I need some helped from tech minded folk.

I'm trying to find a more efficient way of handling large amounts of EPG data when a user has a lot of TV channels configured for npvr.

At the moment I create 7 individual XML files for nDroid (one for each day) using a query similar to the following...

Code:

SELECT * FROM EPG_EVENT WHERE end_time > '2013-02-02 00:00:00' AND start_time < '2013-02-02 23:59:59' ORDER BY channel_oid

With my setup of 67 channels, it returns 1060 results for 1 day but if I create a query for a single channel for an entire week similar to...

Code:

SELECT * FROM EPG_EVENT WHERE end_time > '2013-02-02 00:00:00' AND start_time < '2013-02-08 23:59:59' AND channel_oid = 10029 ORDER BY start_time

...I get 260 results which is less than 1/4.

So...a little request for help - if anyone is happy to spend a few minutes playing with SQL queries on their npvr database, I'd appreciate some comparisons between the number of results returned with the two queries above. I'm particularly interested in anyone with a lot of channels (perhaps 120+ and definitely 150-200 or more). Obviously the second query will need adjusting for a valid channel_oid.

Cheers,
Brian

[whurlston]

start time < 2013-02-08

[bgowland]

start time < 2013-02-08

:confused:
Sorry, I don't get your point.

[whurlston]

You changed the start time parameter between the first and second query:

Code:

SELECT * FROM EPG_EVENT WHERE end_time > '2013-02-02 00:00:00' AND start_time < '2013-02-[color=#FF0000][B]02[/B][/color] 23:59:59' ORDER BY channel_oid

Code:

SELECT * FROM EPG_EVENT WHERE end_time > '2013-02-02 00:00:00' AND start_time < '2013-02-[color=#FF0000][B]08[/B][/color] 23:59:59' AND channel_oid = 10029 ORDER BY start_time

Was this intentional? I thought you only wanted to limit it to one channel but keep other the parameters the same.

[mvallevand]

I get about 9-60 shows a day per channel and I have 178 channels so I don't really know what you are looking for.

Martin

[bgowland]

@ whurlston - Yes. The first is for all programmes, all channels for 1 day. The second query is for all programmes, 1 channel for 7 days.

I'm trying to work out if there is a significant difference between doing it the second way, per channel for 7 days, rather than per day for all channels.

[bgowland]

I get about 9-60 shows a day per channel and I have 178 channels so I don't really know what you are looking for.

Martin

If you could run both queries (the second with a valid channel_oid for your setup) and let me know the number of results in both cases, it would be useful.

[whurlston]

@ whurlston - Yes. The first is for all programmes, all channels for 1 day. The second query is for all programmes, 1 channel for 7 days.

I'm trying to work out if there is a significant difference between doing it the second way, per channel for 7 days, rather than per day for all channels.

That's not exactly what those queries are saying. In the first, you are getting everything that starts before 2013-02-02 23:59:59 and ends after 2013-02-02 00:00:00 (24 hours before your start time).

The second is wanting anything that starts after the 02-08 but ends after 02-02 (one week earlier).

I think you want to change "end_time" to "start_ time" on both queries.

[mvallevand]

If you could run both queries (the second with a valid channel_oid for your setup) and let me know the number of results in both cases, it would be useful.

5174 for 178 channels on the 2nd (UTC) I guess the avg is 30ish

With 9-60 per day that could mean 56 - 420 for one channel_oid

Martin

[whurlston]

Here are query strings for what I believe you want to do:

Get all programs that start on 02/02 (don't care when they end)

Code:

SELECT * FROM EPG_EVENT WHERE start_time >= '2013-02-02 00:00:00'  AND start_time <= '2013-02-02 23:59:59' ORDER BY channel_oid

Get all programs that start anywhere from 02/02 to 02/08 (don't care when they end) for channel_oid 10029

Code:

SELECT * FROM EPG_EVENT WHERE start_time >= '2013-02-02 00:00:00' AND start_time <= '2013-02-08 23:59:59' AND channel_oid = 10029 ORDER BY start_time

Notice ">=" and "<=". This makes the listed times inclusive so that you don't miss the ones that start exactly at midnight.

If you want to limit by when they end, that can be added easily.