2016-07-01, 09:38
Hi,
I've been talking to Marantz/Sopparus about this and he told me that he had already went thru this with the Music Library a year ago (223731 (thread)).
When opening a tvshow in the library it queries which seasons are available for that show ID (which is as it should).
Problem is how the view query is constructed.
This is most likely not a problem with smaller databases (i myself haven't noticed it until recently, which is probably because it's been getting slower over a long period of time).
We did abit of tinkering with the queries and figured out that the issue is that season_view is requesting it's content from several other views.
Only thing different really from the original view query is that this one doesn't retreive its content from the views, but from the base tables.
The query is almost instant when run manually, it does have a slightly slower response time when made into a view.
The result after the view was changed for kodi is still (for me 6 seconds) way faster.
I know that this is probably not in line with the Kodi way of doing this, but I wanted to make you aware of the issue and open a discussion on if/how to resolve it
This probably happens to the other views aswell, but i haven't found a good way of speeding those up.
I've been talking to Marantz/Sopparus about this and he told me that he had already went thru this with the Music Library a year ago (223731 (thread)).
When opening a tvshow in the library it queries which seasons are available for that show ID (which is as it should).
Problem is how the view query is constructed.
Code:
DEBUG: RunQuery took 7435 ms for 2 items query: SELECT * FROM season_view WHERE season_view.idShow = 539This is most likely not a problem with smaller databases (i myself haven't noticed it until recently, which is probably because it's been getting slower over a long period of time).
We did abit of tinkering with the queries and figured out that the issue is that season_view is requesting it's content from several other views.
Code:
SELECT s.idSeason AS idSeason,
s.idShow AS idShow,
s.season AS season,
s.name AS name,
p.strPath AS strPath,
t.c00 AS showTitle,
t.c01 AS plot,
t.c05 AS premiered,
t.c08 AS genre,
t.c14 AS studio,
t.c13 AS mpaa,
COUNT(distinct e.idEpisode) AS episodes,
COUNT(f.playCount) AS playCount,
MIN(e.c05) AS aired
FROM seasons AS s
INNER JOIN tvshowlinkpath AS lp ON (s.idShow = lp.idShow)
JOIN path AS p ON (p.idPath = lp.idPath)
JOIN tvshow AS t ON (s.idShow = t.idShow)
JOIN episode AS e ON (s.idShow = e.idShow) AND (e.c12=s.season)
JOIN files AS f ON (e.idFile = f.idFile)
WHERE t.idShow=526
GROUP BY idSeason;Only thing different really from the original view query is that this one doesn't retreive its content from the views, but from the base tables.
The query is almost instant when run manually, it does have a slightly slower response time when made into a view.
The result after the view was changed for kodi is still (for me 6 seconds) way faster.
Code:
DEBUG: RunQuery took 1468 ms for 1 items query: SELECT * FROM season_view WHERE season_view.idShow = 844I know that this is probably not in line with the Kodi way of doing this, but I wanted to make you aware of the issue and open a discussion on if/how to resolve it
This probably happens to the other views aswell, but i haven't found a good way of speeding those up.
