Reply to thread

Message

<blockquote data-quote="Bernard" data-source="post: 973430" data-attributes="member: 120430">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.&nbsp;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 &quot;back&quot; to the category list, it then seems like MePo is &quot;hanging&quot; 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 &quot;Unwatched&quot; list. And only once in a while use the custom dynamic lists.&nbsp;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.&nbsp;I have 2 custom &quot;filtered&quot; categories and 1 &quot;dynamic&quot; categories.&nbsp;One is a list of all movies on my file server (filter with: fullpath START WITH &quot;\\fileserver&quot;)One is a list of all movies on my DVD backups (filtered with: fullpath DOES NOT START WITH &quot;\\fileserver&quot;)One is a list of all Media Labels (it has a subgrouping of each media label thus dynamic category)&nbsp;When I open &quot;Moving Pictures&quot; 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).&nbsp;So I went and opened the MovingPictures db with SQL Lite Browser (found on SourceForge website). Then added a &quot;index&quot;, for the &quot;local_media (media_label)&quot; table and column.&nbsp;Then I also went and changed my 2 filters to rather use [Media Label STARTS WITH &quot;Movies&quot;] and [Media Label DOES NOT START WITH &quot;Movies&quot;] Since all my DVDs labels start is named &quot;Movies 1&quot; to &quot;Movies 82&quot;.&nbsp;Now when I open the Moving Pictures plugin, it opens in 9 seconds, and the category opens in less than 1 second.&nbsp;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.&nbsp;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.</blockquote>

[QUOTE="Bernard, post: 973430, member: 120430"] 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. [/QUOTE]

Verification