Need help with some real-world SQL query results

[whurlston]

Totally misread the first post. I thought you were having trouble with your query strings.

Results of queries:

Your 1 day: 2693
My 1 day: 2673
Your 1 week single channel: 212
My 1 week single channel: 212
Your 1 week all channels: 18626
My 1 week all channels: 18608

I will compare the data returned later to see why the differences.

[whurlston]

Actually, I don't need to test. My query would not get programs that started before midnight but are still playing on the wanted day. Yours does... which is better.

I need to stop posting while on medication.

[bgowland]

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

Thanks. Sorry I didn't explain my reasons further. I'm trying to make things more memory-friendly. Using your figures it would be better to process one channel at a time with a chunk of 420 than processing 5174 for one day.

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)

The query is correct for the 1st day of the EPG. The logic is "Get all programmes which start on 1st February and are still running on the 2nd February but also get all programmes which start on the 2nd of February regardless of when they end". The end_time > 2013-02-02 00:00:00 part ensures no programme which ended before 2nd February is included. I need to include 'currently running' programmes from the day before the 1st day just in case they've only been on-air for a few minutes and somebody would want to watch / record them.

[bgowland]

Actually, I don't need to test. My query would not get programs that started before midnight but are still playing on the wanted day. Yours does... which is better.

I need to stop posting while on medication.

We cross-posted. Yes exactly - I need the ones still playing. Thanks for the results in your previous post though. With those and Martin's it looks like I'll get more friendly memory usage processing things per channel rather than per day.

[whurlston]

I totally read your first post as saying that you were only getting 1/4th of the expected results with the second query so I was looking for problems in the query string.

FWIW, I have 98 channels in my db.

[mvallevand]

You could save a log of space by normalizing the database to a better form. Typically in North America there are a log of duplicates with our database. When I take the weekly database query which has 5154 (not 5174) entries and group by unique_id it reduces to 2025 rows.

Martin

[bgowland]

FWIW, I have 98 channels in my db.

Thanks that's useful for a ball park comparison which is all I'm really looking for at the moment.

You could save a log of space by normalizing the database to a better form. Typically in North America there are a log of duplicates with our database. When I take the weekly database query which has 5154 (not 5174) entries and group by unique_id it reduces to 2025 rows.

Martin

I'm not sure what you mean Martin (or if I do I'm not sure it would help). We don't have unique_id entries in the UK (at least I don't in my DB) and I suspect that's the case for a lot of other countries. I can think of a good way of optimising things if all TV shows had a unique_id so they could be grouped but it would also depend on number of repeats in any given week.

I've got a new XML schema which should (hopefully) improve things in a number of ways.

Cheers,
Brian

[mvallevand]

I understand that was proposing was primarily and option here in Canada and the US, and just use the oid as unique_id when it wasn't valid. Basically it was two tables that you join at the client

channel info, time data, oid, unique_id
unique_id, show info

Martin