Understanding performance difference between movie and tv base library views
#1
I have always assumed that the thumbnail cache is the biggest bottleneck in library views. In fact I have had several dev conversations just on this topic and assumed I had proven it by testing with all thumbnails removed for big response increases.

However something simple has just occurred to me:

My base movie view has approx 2000 entrys and my base tv view has 1000. Almost all have covers and fanart but the tv view is MASSIVELY slower. The movie viewer takes a fraction of the time to load (in the order of 2 seconds for movie view load and 15+ for TV view).

Does this suggest that there is something else is at play here that slows the tv view down and if so what would it be and how can I improve it or turn it off?
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#2
Compare the query times first and foremost.

After the query, it could be other stuff that slows it down (eg stat'ing the filesystem and other such nasties.)
Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


Image
Reply
#3
OK will do, will rake through some debug logs and see whats going on
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#4
OK as requested some timings.

This is using an older copy of my database/cache running on Beta 1 XP with faster hardware than my HTPC.

However since the actual times are irrelevant but rather the timing differences for the actual count of returned data these things make no difference

Loading Movie Base View

Code:
10:10:25 T:344 M:3993485312   DEBUG: CGUIMediaWindow::GetDirectory (videodb://1/2/)
10:10:25 T:344 M:3993485312   DEBUG:   ParentPath = [videodb://1/2/]
10:10:25 T:344 M:3993378816   DEBUG: CVideoDatabase::GetSetsNav query: select sets.idSet,sets.strSet,count(1),count(files.playCount) from sets join setlinkmovie on sets.idSet=setlinkmovie.idSet join movie on setlinkmovie.idMovie=movie.idMovie join files on files.idFile=movie.idFile  group by sets.idSet
10:10:25 T:344 M:3993251840   DEBUG: CVideoDatabase::GetMoviesByWhere query: select * from movieview where movieview.idmovie NOT in (select idmovie from setlinkmovie)
10:10:25 T:344 M:3976679424   DEBUG: Time for actual SQL query = 150
10:10:26 T:344 M:3957030912   DEBUG: Time to retrieve movies from dataset = 1561
10:10:27 T:344 M:3969323008   DEBUG: Saving fileitems [videodb://1/2/]
10:10:27 T:344 M:3964686336   DEBUG:   -- items: 2161, sort method: 23, ascending: true

Loading TV View

Code:
10:10:32 T:344 M:3972214784   DEBUG: CGUIMediaWindow::GetDirectory (videodb://2/2/)
10:10:32 T:344 M:3972214784   DEBUG:   ParentPath = [videodb://2/2/]
10:10:32 T:344 M:3972214784   DEBUG: CVideoDatabase::GetTvShowsByWhere query: select * from tvshowview
10:10:33 T:344 M:3948441600   DEBUG: Time for actual SQL query = 1089
10:10:41 T:344 M:3948167168   DEBUG: Time to retrieve movies from dataset = 7892
10:10:41 T:344 M:3965681664   DEBUG: Saving fileitems [videodb://2/2/]
10:10:41 T:344 M:3963228160   DEBUG:   -- items: 1017, sort method: 2, ascending: true

So we can see the TV show base view takes over 7 times longer to load less than half the number of entrys the Movie view has. In other words the query performs almost 15.5 times worse per item returned.

Obviously this is huge difference in performance.

Thoughts?
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#5
First off, the query time is due to it having to hit far more items than the movies. In order to get episode counts and so on we effectively need to hit the episodes table. This could, ofcourse, be done better using triggers to update temps in the tvshow table, so the query time could be reduced to something similar to the movies query.

As for the retrieve from dataset timings, this could be due to hitting the disk. I'll take a look at the code and see if there's some obvious bottlenecks that could cause it.

Cheers,
Jonathan
Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


Image
Reply
#6
I will manually time the loads of each view in the morning and post what they are as well just for completeness sake.

Thanks for looking into this
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#7
OK some manual timings.

First i timed the opening of both views:

Movie Base View: Approx 3 seconds
TV Base View: Approx 9 seconds

Now I removed my folder thumbnail cache. I opened XBMC and allowed it to create the default thumbnail folder. Shutdown XBMC and opened again.

Movie Base View with no thumbnails: Approx 2 seconds
TV Base View: Approx 17 seconds

These are very approximate timings but it is a bit odd that the Movies view gets faster when no thumbnails and the TV view gets slower.

Edit:

FYI XBOX 1 load times are off the charts measured in multiple minutes.
I am less concerned about the initial load time as opposed to the perception of usabilty. Specifically when nevigating to the season level in tv show library view returning to the base view has similar high load times. That is the real area of reduced usabilty
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#8
i found the cause yesterday i believe, at least part of it - get a new build Smile
Reply
#9
spiff talked to vdrfan. new build coming for testing. Will report back ASAP. thanks a bunch.
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#10
spiff found your commit 25046 and just tested with 25049.

The difference in performance is nothing short of phenomenal. We looking at least at a 4 times speed up.
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply
#11
cool. more coming
Reply
#12
Some Before and after stats. You can see some serious improvements here.

Loading Movie Base View - Beta 1

Code:
10:10:25 T:344 M:3993485312   DEBUG: CGUIMediaWindow::GetDirectory (videodb://1/2/)
10:10:25 T:344 M:3993485312   DEBUG:   ParentPath = [videodb://1/2/]
10:10:25 T:344 M:3993378816   DEBUG: CVideoDatabase::GetSetsNav query: select sets.idSet,sets.strSet,count(1),count(files.playCount) from sets join setlinkmovie on sets.idSet=setlinkmovie.idSet join movie on setlinkmovie.idMovie=movie.idMovie join files on files.idFile=movie.idFile  group by sets.idSet
10:10:25 T:344 M:3993251840   DEBUG: CVideoDatabase::GetMoviesByWhere query: select * from movieview where movieview.idmovie NOT in (select idmovie from setlinkmovie)
10:10:25 T:344 M:3976679424   DEBUG: Time for actual SQL query = 150
10:10:26 T:344 M:3957030912   DEBUG: Time to retrieve movies from dataset = 1561
10:10:27 T:344 M:3969323008   DEBUG: Saving fileitems [videodb://1/2/]
10:10:27 T:344 M:3964686336   DEBUG:   -- items: 2161, sort method: 23, ascending: true

Loading Movie Base View - Rev25083

Code:
08:47:27 T:3156 M:3315920896   DEBUG: CGUIMediaWindow::GetDirectory (videodb://1/2/)
08:47:27 T:3156 M:3315920896   DEBUG:   ParentPath = [videodb://1/2/]
08:47:27 T:3156 M:3315806208   DEBUG: CVideoDatabase::GetSetsNav query: select sets.idSet,sets.strSet,count(1),count(files.playCount) from sets join setlinkmovie on sets.idSet=setlinkmovie.idSet join movie on setlinkmovie.idMovie=movie.idMovie join files on files.idFile=movie.idFile  group by sets.idSet
08:47:27 T:3156 M:3315687424   DEBUG: CVideoDatabase::GetMoviesByWhere query: select * from movieview where movieview.idmovie NOT in (select idmovie from setlinkmovie)
08:47:27 T:3156 M:3299229696   DEBUG: Time for actual SQL query = 73
08:47:28 T:3156 M:3279396864   DEBUG: Time to retrieve movies from dataset = 985
08:47:29 T:3156 M:3291701248   DEBUG: Saving fileitems [videodb://1/2/]
08:47:29 T:3156 M:3286880256   DEBUG:   -- items: 2161, sort method: 23, ascending: true

Loading TV View - Beta 1

Code:
10:10:32 T:344 M:3972214784   DEBUG: CGUIMediaWindow::GetDirectory (videodb://2/2/)
10:10:32 T:344 M:3972214784   DEBUG:   ParentPath = [videodb://2/2/]
10:10:32 T:344 M:3972214784   DEBUG: CVideoDatabase::GetTvShowsByWhere query: select * from tvshowview
10:10:33 T:344 M:3948441600   DEBUG: Time for actual SQL query = 1089
10:10:41 T:344 M:3948167168   DEBUG: Time to retrieve movies from dataset = 7892
10:10:41 T:344 M:3965681664   DEBUG: Saving fileitems [videodb://2/2/]
10:10:41 T:344 M:3963228160   DEBUG:   -- items: 1017, sort method: 2, ascending: true

Loading TV View - Rev25083

Code:
08:47:33 T:3156 M:3291283456   DEBUG: CGUIMediaWindow::GetDirectory (videodb://2/2/)
08:47:33 T:3156 M:3291283456   DEBUG:   ParentPath = [videodb://2/2/]
08:47:33 T:3156 M:3291484160   DEBUG: CVideoDatabase::GetTvShowsByWhere query: select * from tvshowview
08:47:33 T:3156 M:3267399680   DEBUG: Time for actual SQL query = 550
08:47:33 T:3156 M:3267194880   DEBUG: Time to retrieve tvshows from dataset = 313
08:47:34 T:3156 M:3285377024   DEBUG: Saving fileitems [videodb://2/2/]
08:47:34 T:3156 M:3282980864   DEBUG:   -- items: 1017, sort method: 2, ascending: true
Having problems getting your TV shows recognized?

Try my extra TV show matching REGEX here
Reply

Logout Mark Read Team Forum Stats Members Help
Understanding performance difference between movie and tv base library views0