Slow to load initially the genres view (1 Viewer)

[RBwaBD]

Hi,

using MP1.1.0 beta 1, latest tvseries plugin, bluewide 3 skin. (although problem was there before so not a version issue).

I have a lot of series (about 800, with 10000+ total episodes).

When loading up the initial screen (I start with genres view) it takes upto 30-40 seconds to show the genres logos. Also, it only ever shows 2 of the logos (last 2 I think). I need to back out to main menu and come back in, then all logos show.

After that everything incl submenus etc. load up briskly. Moreover, even if I exit MP but come back in right away, the genres view loads quickly and without error as well (probably stays loaded in memory).

I think this must be because too much of the database info is loaded into memory upfront. Would be more optimal to load things either in background or on as needed basis.

Would this entail complete reprogramming of the engine?

thanks! this is a great plugin.

 

[Guzzi]

Did you find a solution for that? Have you ever tried to use DB via a RAMdisk to check if this avoids that problem?

 

[Guzzi]

AW: Slow to load initially the genres view

Hi Damien,
I have seen, that you have moved the issue 23 to target 2.6.

Issue 21: Not all episodes are displayed in the configuration window when there is a large number of episodes in database ...

Did you ever try to give longer timeouts for SQL-query or using more buffer memory? I think default timeout is 30 s. and cannot be changed "from outside" - so would require change in code, right?
I tried the statement in SQL-commandline and it took approx 166 seconds, but completed successfully, so there might be some things to try on your side:

- Increase timeout - maybe just for this query ("if (bigJob) cmd.CommandTimeout = 200;")
- Try use memory for temporary structures instead if filespace (PRAGMA temp_store = 2)
- Try "in-memory" DB to start DB fully in RAM (should be no problem for most HTPCs I supppose?)
- Is it possible to inclease the transaction buffers? Because it's a huge query, this might help?

I am no programmer, just did some reading, e.g. h**p://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

Maybe that helps to find a fix?

Thanks for all your work,
Guzzi

PS: THere might also be the possibility of an ugly hack:
If you process a simpler select query (something like select count ...) in advance, this might properly prepare this big query (prepared select). This also can be seen in the GUI, when at first time entering there is only 1 episode, going back and returning shows them all as expected. Not nice, but at least a workaround?

select online_episodes.CompositeID, online_episodes.EpisodeID, online_episodes.SeriesID, online_episodes.EpisodeIndex as online_episodesEpisodeIndex , online_episodes.SeasonIndex, online_episodes.EpisodeName, online_episodes.Watched, online_episodes.Summary, online_episodes.FirstAired, online_episodes.OnlineDataImported, online_episodes.GuestStars, online_episodes.Director, online_episodes.Writer, online_episodes.Hidden, online_episodes.lastupdated, online_episodes.DownloadPending, online_episodes.DownloadExpectedName, online_episodes.ThumbUrl, online_episodes.thumbFilename, online_episodes.Combined_episodenumber, online_episodes.Combined_season, online_episodes.DVD_chapter, online_episodes.DVD_discid, online_episodes.DVD_episodenumber, online_episodes.DVD_season, online_episodes.IMDB_ID, online_episodes.Language, online_episodes.ProductionCode, online_episodes.Rating, online_episodes.absolute_number, online_episodes.airsafter_season, online_episodes.airsbefore_episode, online_episodes.airsbefore_season, online_episodes.seasonid, online_episodes.EpImgFlag, local_episodes.EpisodeFilename, local_episodes.CompositeID, local_episodes.SeriesID, local_episodes.SeasonIndex, local_episodes.EpisodeIndex, local_episodes.LocalEpisodeName, local_episodes.LocalImportProcessed, local_episodes.AvailableSubtitles, local_episodes.CompositeID2, local_episodes.EpisodeIndex2, local_episodes.videoWidth, local_episodes.videoHeight, local_episodes.localPlaytime, local_episodes.VideoCodec, local_episodes.VideoBitrate, local_episodes.VideoFrameRate, local_episodes.VideoAspectRatio, local_episodes.AudioCodec, local_episodes.AudioBitrate, local_episodes.AudioChannels, local_episodes.ext, local_episodes.Removable, local_episodes.StopTime, local_episodes.FileDateAdded, local_episodes.FileDateCreated, local_episodes.AudioTracks, local_episodes.TextCount from online_episodes left join local_episodes on (local_episodes.CompositeID=online_episodes.CompositeID)  where online_episodes.CompositeID not in (select distinct local_episodes.CompositeID2 from local_episodes where local_episodes.CompositeID2 != '') union select online_episodes.CompositeID, online_episodes.EpisodeID, online_episodes.SeriesID, online_episodes.EpisodeIndex as online_episodesEpisodeIndex , online_episodes.SeasonIndex, online_episodes.EpisodeName, online_episodes.Watched, online_episodes.Summary, online_episodes.FirstAired, online_episodes.OnlineDataImported, online_episodes.GuestStars, online_episodes.Director, online_episodes.Writer, online_episodes.Hidden, online_episodes.lastupdated, online_episodes.DownloadPending, online_episodes.DownloadExpectedName, online_episodes.ThumbUrl, online_episodes.thumbFilename, online_episodes.Combined_episodenumber, online_episodes.Combined_season, online_episodes.DVD_chapter, online_episodes.DVD_discid, online_episodes.DVD_episodenumber, online_episodes.DVD_season, online_episodes.IMDB_ID, online_episodes.Language, online_episodes.ProductionCode, online_episodes.Rating, online_episodes.absolute_number, online_episodes.airsafter_season, online_episodes.airsbefore_episode, online_episodes.airsbefore_season, online_episodes.seasonid, online_episodes.EpImgFlag, local_episodes.EpisodeFilename, local_episodes.CompositeID, local_episodes.SeriesID, local_episodes.SeasonIndex, local_episodes.EpisodeIndex, local_episodes.LocalEpisodeName, local_episodes.LocalImportProcessed, local_episodes.AvailableSubtitles, local_episodes.CompositeID2, local_episodes.EpisodeIndex2, local_episodes.videoWidth, local_episodes.videoHeight, local_episodes.localPlaytime, local_episodes.VideoCodec, local_episodes.VideoBitrate, local_episodes.VideoFrameRate, local_episodes.VideoAspectRatio, local_episodes.AudioCodec, local_episodes.AudioBitrate, local_episodes.AudioChannels, local_episodes.ext, local_episodes.Removable, local_episodes.StopTime, local_episodes.FileDateAdded, local_episodes.FileDateCreated, local_episodes.AudioTracks, local_episodes.TextCount from online_episodes left join local_episodes on (local_episodes.CompositeID2=online_episodes.CompositeID)  where local_episodes.CompositeID2 != '' order by online_episodesEpisodeIndex asc

 

[ltfearme]

Thanks for the suggestions Guzzi, I havent had time to look at this issue for a while now and will take a lot of research as Im no SQL guru so I have to make sure it doesnt affect people with smaller databases as well.

I will definitely try your suggestions but im sorry it wont make it in the release I plan to do shortly...I may contact you via pm afterwards with a test dll though.

 

[Guzzi]

AW: Re: Slow to load initially the genres view

Thanks for the suggestions Guzzi, I havent had time to look at this issue for a while now and will take a lot of research as Im no SQL guru so I have to make sure it doesnt affect people with smaller databases as well.

I will definitely try your suggestions but im sorry it wont make it in the release I plan to do shortly...I may contact you via pm afterwards with a test dll though.

Hi Damien,

thanks for your feedback; have sent you a PM.