Building dynamic lists when entering them directly rather than on plugin start? (2 Viewers)

[TheBatfink]

I think I may of already asked this but is there any way to do this, or as I'm sure there isn't, get it added or some other way of handling these? When you build lists for directors and actors it makes the plugin open soooo slow (and this is even on a 6 x 3ghz Core CPU with 2 x Raid 0 SSD's).

Is it not possible to build the menus when you enter them rather than triggering them when entering the plugin? I appreciate when we are looking at 1300+ movie entries, the list can be quite large, but it still seems a long time for a simple query? What makes it take so long.. SQLite general performance?

Where are the dynamic list entries put once its been queried? is it stored in a table somewhere?

Batfink

 

[RoChess]

The real fix would simply be for SQL indexes to be created, this can reduce queries from taking minutes down to 0.1 second.

These indexes are created for the dynamic categories such as genres/year/etc, but not for directors/actors. Not sure if you can fix this by adding indexes manually with an SQLite tool, or if adjustments in the MovPic code are needed. There is an existing ticket on this.

 

[TheBatfink]

Is there a list of actors / directors other than the one in the movie_info table?

 

[Bernard]

It is hard to think that a list of 1300 entries should really take longer than a second to execute (even without indexing). I'm used to running queries on 100k entries, within a second and no indexing. But that is with MsSql Server. I wish MePo can enable the use of MsSql because then db corruptions and multi-client access problems will be something of the past.

Anycase, I have the same problem with 1003 entries. But my dynamic list is actually my Removable media labels. I have backup a lot of movies to DVDs and now like to have a list of my labels to work from. It also is bothersome if you are in the list, and you use "back" to the category list, it then seems like MePo is "hanging" while it is building that lists again. Also an upgrade to the code to rebuild that list only when you access them is a lot more reasonable, since I mostly use the "Unwatched" list. And only once in a while use the custom dynamic lists.

I like RoChess's suggestion, maybe we can add custom indexes and it will stay since MePo upgrades does not revert changes to the DBs. I'm going to to try it sometime, and will post if I could get it to work.[DOUBLEPOST=1362943621][/DOUBLEPOST]Ok, I have cracked it! It was quite easy.

I have 2 custom "filtered" categories and 1 "dynamic" categories.

One is a list of all movies on my file server (filter with: fullpath START WITH "\\fileserver")
One is a list of all movies on my DVD backups (filtered with: fullpath DOES NOT START WITH "\\fileserver")
One is a list of all Media Labels (it has a subgrouping of each media label thus dynamic category)

When I open "Moving Pictures" plugin, it took 32 seconds to display the list of categories (top level!!!). Then when you open any of the 3 categories it took 12 seconds).

So I went and opened the MovingPictures db with SQL Lite Browser (found on SourceForge website). Then added a "index", for the "local_media (media_label)" table and column.

Then I also went and changed my 2 filters to rather use [Media Label STARTS WITH "Movies"] and [Media Label DOES NOT START WITH "Movies"] Since all my DVDs labels start is named "Movies 1" to "Movies 82".

Now when I open the Moving Pictures plugin, it opens in 9 seconds, and the category opens in less than 1 second.

It was interesting to note that NO additional indexes exist on any field other than primary keys (default) and foreign keys. So if anyone has dynamic or filtered custom categories, even the default ones, they can benefit from adding indexes manually.

Also for each movie all the actors is saved in 1 cell, seperated by a pipe symbol (|). So even if this field was indexed the system will still have to split each cells value to get a list of unique actors. So I'm not sure that an index will speed this process up.[DOUBLEPOST=1362944377][/DOUBLEPOST]Last post, on a sidenote, I just added indexes on: date_added, genres, certificates, year. But this did not increase the loading speed of the plugin to less than 8 seconds. So it seems to have no effect, doing this.

 

[RoChess]

It is hard to think that a list of 1300 entries should really take longer than a second to execute (even without indexing).

I've seen slow-queries go from almost an hour to a few milliseconds after creating indexes. I'm sure the query in this case is a lot of table joins and other aspects. Adding indexes manually doesn't always work then to increase performance.

Last I looked into this myself, before aborting dynamic ones and going with a bunch of static ones instead; I had a few tables that contained a bunch of rows relating to the dynamic categogies added. The column names you mention do not sound right for those tables.

Easy to run a test on this box, and it gives me the following:

[node] table gets an entry added for "Actors" which is the dynamic filter entry added. Below that on this test box I get 32 actor node entries from the small selection of movies this test setup holds (15 movies if memory serves me, which would lead to 75 actors, but I'm testing a lot of movie series for IMDb+ which obviously results in a lot of reduced entries then).

The [parent] column of each added actor refers back to the ID that belongs to the "Actors" node entry, but they all get a unique [filter] ID.
The [filter] ID from that table gets linked to the [filter] ID used for dynamic actors inside the [node__node] table.

Just speculating now, but the delay is either from verifying that all those static entries added to the [node] table and their reference in [node__node] are still valid each time, or causes a giant delay in some crazy query that lacks the indexes to do it fast. Guess you can try to add indexex on the respective columns in those tables, but would help to find out first what query is actually used that causes the delay (easier to spot best place to add indexes then).

I can do some trial & error later this week on a large scale test database copy, by enabling dynamic actors on it and see what indexes are needed. But you will probably beat me to it.

 

[TheBatfink]

Also for each movie all the actors is saved in 1 cell, seperated by a pipe symbol (|). So even if this field was indexed the system will still have to split each cells value to get a list of unique actors.

Whats the reasoning for this anyway. Surely that breaks normal form?

So what indexes do I need to add for actors and directors dynamic lists? I don't want to just add them willy nilly as from what I remember, indexes can speed up searches but slow down updates.

 

[Bernard]

So what indexes do I need to add for actors and directors dynamic lists? I don't want to just add them willy nilly as from what I remember, indexes can speed up searches but slow down updates.

I don't know the answer to this. As you can derive from RoChess's post, he is taking about a table called [node] and [node__node]. It would seem to me that the plugin actually go and create a "node" for each actor for example. Now this should be a one time thing then, because the only time this will need to be done again, is when info is updated or new movies are added.

But from what I found, indexing the actual value field where this nodes is being build from, causes an increase in speed. This gives me the idea that the nodes are repeatedly build every time you access the plugin "category" page. Because each movie has a field that list all the actors, it would mean that the plugin has to scan and work through each cell. So I don't think any indexing will help with this.

I might be wrong, and if we actually investigate the code, we will be able to not speculate and give an exact answer. And I don't think anyone is up for that yet.

 

[TheBatfink]

It definitely creates it each time. Regarding investigating the code, I looked before, its not a small / simple plugin

I suspect whilst there is no actor specific table and many-many relationships defined through a joining table back to the movie entities, speeding this up may not even be possible and will certainly go beyond simply creating faster queries. I would guess the bottleneck is parsing the actor names out of the single fields like you say. This is why I wondered if it would be possible to trigger the list generation when you enter that specific entry in the catagories menu, rather than when you enter it at the highest level.

Maybe @fforde will tell us to stop talking silly and put us straight

 

[Bernard]

This is why I wondered if it would be possible to trigger the list generation when you enter that specific entry in the catagories menu, rather than when you enter it at the highest level.

+1

I would agree, since we don't always use the dynamic lists every day. I only use mine if for instance I have already inserted a DVD and want to see what else is on it. And I also like the idea of the dynamic actor list, because I can then view movies by certain actors that I like (for instance: Will Smith) But to think that it will slow down (again) is not worth it, I then rather just go to IMDB and check the list of movies he plays in, and see which ones I do have to watch.

 

[TheBatfink]

I added this on the tracker.. maybe it will garner more responses this way, who knows. Please star it

https://code.google.com/p/moving-pictures/issues/detail?id=1122&thanks=1122&ts=1363103260