Kodi Community Forum

KODI Leia nightly 08.12.2018
Central SQL database (MariaDB)

When movie sets are loaded into the skin it seems to take an inordinately long time. Other queries to the movie database seem to be reasonably fast. This is how it looks in the log:

xml:


21:12:40.316 T:5840   DEBUG: CGUIMediaWindow::GetDirectory (library://video/movies/sets.xml/)

21:12:40.316 T:5840   DEBUG:   ParentPath = [library://video/movies/sets.xml/]

21:12:42.478 T:8756   DEBUG: CVideoDatabase::RunQuery took 638 ms for 19 items query: SELECT genre.genre_id, genre.name, count(1), count(files.playCount) FROM genre JOIN genre_link ON genre.genre_id = genre_link.genre_id JOIN movie_view ON genre_link.media_id = movie_view.idMovie AND genre_link.media_type='movie' JOIN files ON files.idFile = movie_view.idFile GROUP BY genre.genre_id

21:12:44.824 T:8756   ERROR: XFILE::CDirectory::GetDirectory - Error getting

21:12:45.909 T:11996   ERROR: Previous line repeats 6 times.

21:12:45.909 T:11996   DEBUG: CVideoDatabase::RunQuery took 8877 ms for 1689 items query: select * from movie_view

21:13:04.577 T:8980   DEBUG: Thread JobWorker 8980 terminating (autodelete)

21:13:14.824 T:8756   DEBUG: Thread JobWorker 8756 terminating (autodelete)

21:13:16.449 T:11996   DEBUG: Thread JobWorker 11996 terminating (autodelete)

21:13:59.008 T:5840   DEBUG: CVideoDatabase::RunQuery took 78342 ms for 622 items query: select * from movie_view  JOIN sets ON movie_view.idSet = sets.idSet ORDER BY sets.idSet

or when navigating the sets item

xml:
21:39:37.331 T:2964   DEBUG: CVideoDatabase::RunQuery took 69836 ms for 622 items query: select * from movie_view  JOIN sets ON movie_view.idSet = sets.idSet ORDER BY sets.idSet

21:39:37.481 T:2964   DEBUG: Saving fileitems [videodb://movies/sets/]

I have already dropped the videodb from the SQL server and completely built it anew from scratch but the issue remains. I'm tentatively labeling this as a bug.


EDIT: Sorry, this should have been posted under ESTUARY. Please move it, admins.

Not sure if the skin has anything to to do with a 'generic'? database query, but your thread has been moved.
Do you see differences in query execution times when using a different skin?

(2018-12-09, 22:44)HeresJohnny Wrote: [ -> ]RunQuery took 78342 ms for 622 items query

78 seconds for a 622-items view is ludicrous. Are you sure that MariaDB isn't having a fit? :-\

I've tried with vanilla Estuary and pkscout's Estuary mod... both present the same query times.

From the same log:

xml:
21:14:22.083 T:9496   DEBUG: CVideoDatabase::RunQuery took 3667 ms for 5754 items query: select * from episode_view

This seems to be within the expected margin, given that I'm connecting from a Laptop via Wifi. What I find strange are the CDirectory errors, but that goes beyond my skills.

Following up on this. I've tried with Confluence and it takes just as long as with Estuary, too long. What still catches my eye in the log are the following lines:


Does anybody have an idea why those would fail initially but eventually return a result anyway?

Ok, I've identified the source of the problem. It looks like all movies have 2 entries in the "movie" table, like so:

That's pretty weird because I had dropped the whole database when I scanned my library two weeks ago. Removing the source only gets rid of the high ID entry, the one with "1" stays in the database. I am guessing the duplicated entries throw off the movie_view and in turn the set query. Removing the duplicates, set queries are fast again.

I've also modified the sets.xml node from its default

xml:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<node order="80" type="filter" visible="Library.HasContent(MovieSets)">

	<label>20434</label>

	<icon>DefaultSets.png</icon>

	<content>movies</content>

	<group>sets</group>

</node>

to

xml:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<node order="80" type="folder">

	<label>20434</label>

	<icon>DefaultSets.png</icon>

	<path>videodb://movies/sets/</path>

</node>

which doesn't throw the error I've mentioned above.

Thread marked solved.