Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Need help with some real-world SQL query results

  1. #11
    Join Date
    Nov 2006
    Location
    Louisville, KY, USA
    Posts
    7,688
    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.

  2. #12
    Join Date
    Nov 2006
    Location
    Louisville, KY, USA
    Posts
    7,688
    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.

  3. #13
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,484
    Quote Originally Posted by mvallevand View Post
    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.

    Quote Originally Posted by whurlston View Post
    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.

  4. #14
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,484
    Quote Originally Posted by whurlston View Post
    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.

  5. #15
    Join Date
    Nov 2006
    Location
    Louisville, KY, USA
    Posts
    7,688
    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.

  6. #16
    Join Date
    May 2006
    Location
    Canada
    Posts
    20,733
    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

  7. #17
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,484
    Quote Originally Posted by whurlston View Post
    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.

    Quote Originally Posted by mvallevand View Post
    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

  8. #18
    Join Date
    May 2006
    Location
    Canada
    Posts
    20,733
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •