Suggestion to improve guide load time

[wtg]

sub,

My system is pretty slow to load the TV Guide after it's started up. With almost 1000 cable channels and 14 days of EPG data, there are over 142,000 rows in the gbpvr database's PROGRAMME table. On my system it takes about 6-8 minutes for the EPG data to fully load.

Now it just so happens that my cable system's HD channels are all in the 900-999 channel range and being spoiled to HD, more often than not when I'm starting up pvrx2 on my system - either PC or PCH - I'm then waiting almost that full 6-8 minutes for the guide to load so I can watch an HD channel. Frustrating, especially because the PCH's other features have me getting in and out of gbpvr more often now.

So tonight while poking around in the gbpvr database I noticed an opportunity to improve this. Figuring that the long load time was due to gbpvr needing to look up program information one channel at a time in start_time order, I added an index on PROGRAMME's channel_oid and start_time fields to see if it would help. The performance improvement was dramatic. The entire EPG data loads now in approximately 10 seconds!

The new index added approximate 6 meg to my 50 meg database and I'm sure it will slow down the EPG load some, but it seems well worth it.

Here's the index statement I used:

create index programme_epg on PROGRAMME (channel_oid, start_time)

For anyone else who might be interested in trying this, but doesn't now how:

• Let's assume sub doesn't point out a problem with doing this and I don't post back tomorrow explaining my overnight EPG load ran for 36 hours.
  
• There are a few free tools available for accessing SQLite databases. I downloaded the tool SQLite Database Browser.
  
• Shut down the recording service and exit gbpvr.
  
• The database is in the gbpvr install directory, called gbpvr.db3. Copy this file to a backup that you can restore in case you screw it up.
  
• Open the gbpvr.db3 database using the database browser.
  
• In the Execute SQL window, run the following command:
  

[INDENT][INDENT]create index programme_epg on PROGRAMME (channel_oid, start_time)
[/INDENT][/INDENT]

• Save your changes and exit the database.
  
• Restart the recording service and pvrx2. If all went well you should notice the EPG loads significantly faster now.
  

Just thought I'd share. Thanks again for the great program.
Tim

P.S. I created a unique index originally, but a couple people since have found the combo of channel_oid and start_time aren't unique in their database, so I removed the unique keyword from the instructions above. The index is just as effective without the condition.

[scb147]

I know you just attempted this, but have you setup a season recording yet? I'm just curious because I have ~200-250 channels for 14 days. If I setup a season recording, GBPVR freezes at least 30 seconds, and up to a couple minutes.

I was wondering if you don't this has reduced your recording setup time.

[wtg]

scb147,

I can't say for sure if this index would help that. It's been a while since I setup a new season recording so I don't recall what it was like before. I did just add a new one after selecting an episode in the Guide and it was basically instant.

Tim

[sub]

Ok, I'll add this index in the next release. I've only got about 50 channels here, and the listing load pretty much instantly, but I know it'd help the small percentage of users that have a really large number of channels. The only downside I'm aware of is a slightly slower EPG update time.

[scb147]

scb147,

I can't say for sure if this index would help that. It's been a while since I setup a new season recording so I don't recall what it was like before. I did just add a new one after selecting an episode in the Guide and it was basically instant.

Tim

Thanks for your response. I will try it out and see if it helps or not.

How much longer did it make your EPG update?

[wtg]

Not sure exactly since I don't know how long it took before. I stopped and restarted my recording service too many times last night while playing around so I don't have an old log, but today's EPG update took 5 minutes.

[dennit]

Ok, I'll add this index in the next release. I've only got about 50 channels here, and the listing load pretty much instantly, but I know it'd help the small percentage of users that have a really large number of channels. The only downside I'm aware of is a slightly slower EPG update time.

I'm going to try this tonight, as I have a large database (46 MB) and speed issues are a concern for me.

When you make this change, if it's not hard, I'd like to have direct tuning and EPG Guide jump to 4 and 5 digit channels added. Currently, it will only direct jump to 3 digit channels (999) and I have channel numbers as high as 19999 (5 digits).

Thanks!

[dennit]

With almost 1000 cable channels and 14 days of EPG data, there are over 142,000 rows in the gbpvr database's PROGRAMME table. ... On my system it takes about 6-8 minutes for the EPG data to fully load. The entire EPG data loads now in approximately 10 seconds!

I just made this change too.

The SQLite Database Browser gave no errors during the attempt to create the index, but didn't seem to do anything (file size did not change). I then tried SQLite Spy ( I like it a bit better.) It gave me an error #19, saying the index columns were not unique. I changed to a non-unique index with:

create index programme_epg on PROGRAMME (channel_oid, start_time)

I have 167,000 rows in PROGRAMME, which previously took 3 minutes 7 seconds to load. After adding the index, it reduced the load time to 5 seconds. (It was so fast, I didn't get a good measure!) My database file size increased by 7MB, from 64 to 71MB.

Thank you, thank you, thank you!

[wtg]

Strange (to me anyway) that the channel_oid and start_time aren't unique, but its good info for sub if he's going to make the change standardly.

Glad it worked for you.

[dennit]

Strange (to me anyway) that the channel_oid and start_time aren't unique, but its good info for sub if he's going to make the change standardly.

Glad it worked for you.

I thought it was strange too, but it may be due to my constant work directly in the database with sql commands. I've been deleting by oid in the channel and source_channel_mapping tables when my xml files add channels I don't really receive, then adjusting the EPG xml creation files to rename or not retrieve those channels. It's possible that I've left some non-unique data in programme with all that work. I suspect when I've got it tuned, the non-uniques will disappear. Then again, it appears the index does not need to be unique.

I've also had trouble stopping active recordings - and I suspect this was related to the timing issues of my large db. I have hopes that your index will help with this issue too.

Thanks again for the tip.