NextPVR Forums
  • Home
  • New Posts
  • Wiki
  • Members
  • Help
  • Search
  • Register
  • Login
  • Home
  • Wiki
  • Members
  • Help
  • Search
NextPVR Forums Public Developers v
« Previous 1 … 8 9 10 11 12 … 93 Next »
Need help with some real-world SQL query results

 
  • 0 Vote(s) - 0 Average
Need help with some real-world SQL query results
bgowland
Offline

Posting Freak

Posts: 4,535
Threads: 376
Joined: Dec 2004
#1
2013-02-01, 02:21 AM
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
Offline

Posting Freak

Posts: 7,885
Threads: 102
Joined: Nov 2006
#2
2013-02-01, 02:48 AM
start time < 2013-02-08
bgowland
Offline

Posting Freak

Posts: 4,535
Threads: 376
Joined: Dec 2004
#3
2013-02-01, 02:52 AM
whurlston Wrote:start time < 2013-02-08
:confused:
Sorry, I don't get your point.
whurlston
Offline

Posting Freak

Posts: 7,885
Threads: 102
Joined: Nov 2006
#4
2013-02-01, 03:03 AM
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
Offline

Posting Freak

Ontario Canada
Posts: 45,396
Threads: 868
Joined: May 2006
#5
2013-02-01, 03:04 AM
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
Offline

Posting Freak

Posts: 4,535
Threads: 376
Joined: Dec 2004
#6
2013-02-01, 03:06 AM
@ 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
Offline

Posting Freak

Posts: 4,535
Threads: 376
Joined: Dec 2004
#7
2013-02-01, 03:14 AM
mvallevand Wrote: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
Offline

Posting Freak

Posts: 7,885
Threads: 102
Joined: Nov 2006
#8
2013-02-01, 03:18 AM
bgowland Wrote:@ 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
Offline

Posting Freak

Ontario Canada
Posts: 45,396
Threads: 868
Joined: May 2006
#9
2013-02-01, 03:39 AM
bgowland Wrote: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
Offline

Posting Freak

Posts: 7,885
Threads: 102
Joined: Nov 2006
#10
2013-02-01, 03:40 AM
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.
« Next Oldest | Next Newest »

Users browsing this thread: 1 Guest(s)

Pages (2): 1 2 Next »


Possibly Related Threads…
Thread Author Replies Views Last Post
  What's wrong with this SQL query? bgowland 2 1,078 2008-02-03, 08:30 PM
Last Post: bgowland
  SQLite vs Access query differences fla 6 4,485 2007-02-05, 09:07 PM
Last Post: psycik
  SQLite GUI-based admin & query building? Braklet 3 1,444 2006-10-30, 02:45 PM
Last Post: Braklet
  Query live preview mode fla 3 1,723 2006-07-12, 12:18 AM
Last Post: fla

  • View a Printable Version
  • Subscribe to this thread
Forum Jump:

© Designed by D&D, modified by NextPVR - Powered by MyBB

Linear Mode
Threaded Mode