Suggestion to improve guide load time

[teookie]

I just made this "mod" and holy cow my EPG loads instantaneously now. I mean, it only took 5 or 6 seconds before but now it's so fast I cant pull up the guide quick enough to see any empty slots.

Thanks.

[gEd]

from checking my logs, tv listings used to take 16 seconds to do its thing, it now takes 1 sec. Not sure of the real world difference as I "only" have 12 channels but "every little helps.."

Sounds like a good find for those with lots of channels.

On that point, I must confess that I really have no idea what I would do with 100's of channels! I get bored scrolling down though 12 channels in the EPG.

[scb147]

I did this last night, and I had to use the same SQL line as dennit:

Code:

create index programme_epg on PROGRAMME (channel_oid, start_time)

I was using SQLiteSpy as well, so it must have something to do with using that.

Seems to work as well... I don't know if one is better than the other, but my loading time is definitely quicker.

[wtg]

I don't know a whole lot about SQLite specifically, but database optimizers in general usually prefer a unique index to a non-unique one. I just assumed the two would be unique so I created mine that way out of habit.

Since it's obviously not unique for some people, better for anyone who tries this to create it without the unique condition now than to do so and have an EPG update fail should it try to load non-unique items later. I'll edit the first post to mention this.

[scb147]

I don't know a whole lot about SQLite specifically, but database optimizers in general usually prefer a unique index to a non-unique one. I just assumed the two would be unique so I created mine that way out of habit.

Since it's obviously not unique for some people, better for anyone who tries this to create it without the unique condition now than to do so and have an EPG update fail should it try to load non-unique items later. I'll edit the first post to mention this.

I wonder what sub thinks about this. He knows the database better than anyone else, so he should be able to tell us if the unique condition is a problem or not.

Like I said, it could just be a SQLiteSpy thing...

[wtg]

No, I don't think it's SQLiteSpy.

The index gets created by the SQLite database engine regardless of the client used to issue the command. According to Dennit, he simply didn't get the database error message that a unique index couldn't be created when he used the SQLite Database Browser that I recommended.

It seems strange to me that a channel could have more than one program with the same start_time, but maybe EPG data gets messed up occasionally and this happens. Better to have the index defined as non-unique so this doesn't cause a problem than have it be unique and then cause an EPG update to fail.

[dennit]

According to Dennit, he simply didn't get the database error message that a unique index couldn't be created when he used the SQLite Database Browser that I recommended.

Correct.

[HarryH3]

How would one undo the creation of a unique index? Or convert it to non-unique?

[dennit]

How would one undo the creation of a unique index? Or convert it to non-unique?

This should work:
drop index programme_epg

if it doesn't, use this:
drop index programme_epg on PROGRAMME

Edit: I'd suggest you vacuum the database after you drop the index. SQLiteSpy (http://www.yunqa.de/delphi/sqlitespy/) has a 'Vacuum database' menu option

[User]

Huge improvement loading my 45Mb database. I created a non unique index. It looked like Sub aproved so I gave it a try. I'll see how the epg update goes. Thanks for the tip wtg.