DB - SQLLite/MySQL - Performance issues

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
RockerC Offline
Fan
Posts: 318
Joined: May 2011
Reputation: 3
Post: #91
(2014-07-26 12:33)m.savazzi Wrote:  I think I will propose a slightly different approach that should be more flexible and future proof. Also I'm looking at one db for everything not just the video part
Since this is now really a different scope than your original post you should probably start over with a new thread instead of continuing here, as I bet you will get more more attention with a such new thread with a better subject topic description that simply doesn't involve small performance optimizations.

Oh, and with RetroPlayer being mainlined sooner or later might as well consider adding stand-alone games and game ROMs launched via emulators into the scope of the database model right away as well.

Would also be nice if music and music videos could be combined, or at least linked and share information. Just like movies and tv shows / episodes could be linked, or how movies and movie soundtracks could be linked. And how actors / actresses and movies / tv shows / episodes are linked today.
(This post was last modified: 2014-07-28 16:10 by RockerC.)
find quote
m.savazzi Offline
Moderator
Posts: 882
Joined: Mar 2008
Reputation: 26
Post: #92
(2014-07-28 15:58)RockerC Wrote:  Since this is now really a different scope than your original post you should probably start over with a new thread instead of continuing here, as I bet you will get more more attention with a such new thread with a better subject topic description that simply doesn't involve small performance optimizations.

Done! new thread: http://forum.xbmc.org/showthread.php?tid=200911

If you think I'm useful please use the +/- button to raise my reputation
find quote
m.savazzi Offline
Moderator
Posts: 882
Joined: Mar 2008
Reputation: 26
Post: #93
some interesting data:
Code:
22:09:30 T:10908   DEBUG: CVideoDatabase::RunQuery took 2137 ms for 13169 items query: select * from episodeview  WHERE ((episodeview.playCount IS NULL OR episodeview.playCount < 1))

22:08:38 T:11600   DEBUG: GetRecentlyAddedAlbumSongs() query: SELECT songview.* FROM (SELECT idAlbum FROM albumview ORDER BY idAlbum DESC LIMIT 10) AS recentalbums JOIN songview ON songview.idAlbum=recentalbums.idAlbum

22:08:38 T:11600   DEBUG: CMusicDatabase::GetRecentlyAddedAlbums query: select * from albumview where strAlbum != '' order by idAlbum desc limit 10

22:08:53 T:10908   DEBUG: CVideoDatabase::RunQuery took 655 ms for 2962 items query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movieview.playCount < 1))

22:08:58 T:11600   DEBUG: CVideoDatabase::RunQuery took 8 ms for 10 items query: select * from movieview  ORDER BY dateAdded desc, idMovie desc LIMIT 10

22:08:58 T:11600   DEBUG: CVideoDatabase::RunQuery took 1 ms for 10 items query: select * from episodeview  ORDER BY dateAdded desc, idEpisode desc LIMIT 10

22:08:58 T:11600   DEBUG: CVideoDatabase::RunQuery took 2 ms for 0 items query: select * from musicvideoview  ORDER BY dateAdded desc, idMVideo desc LIMIT 10

22:09:48 T:10908   DEBUG: CVideoDatabase::RunQuery took 2 ms for 0 items query: select * from musicvideoview

22:09:53 T:10908   DEBUG: CMusicDatabase::GetAlbumsByWhere query: SELECT albumview.* FROM albumview  WHERE albumview.strAlbum <> ''
22:09:53 T:10908   DEBUG: CMusicDatabase::GetAlbumsByWhere - query took 2 ms

22:09:53 T:10908   DEBUG: Skin Widgets: Total time needed to request random queries: 0:01:07.433000
22:09:53 T:10908   DEBUG: Skin Widgets: Total time needed to request recommended queries: 0:00:00
22:09:53 T:10908   DEBUG: Skin Widgets: Total time needed to request recent items queries: 0:00:00
22:09:53 T:10908   DEBUG: Skin Widgets: Total time needed for all queries: 0:01:07.433000
22:09:53 T:10908   DEBUG: Skin Widgets: script version 0.0.29 stopped

If you think I'm useful please use the +/- button to raise my reputation
find quote
Tolriq Offline
Donor
Posts: 2,312
Joined: Jun 2009
Reputation: 68
Location: France
Post: #94
Another one that I forget to post too Wink

11:04:31 T:3580 DEBUG: JSONRPC: Incoming request: {"jsonrpc": "2.0", "id": 1, "method": "VideoLibrary.GetEpisodes", "params": { "properties": ["title", "playcount", "season", "episode", "showtitle", "plot", "file", "rating", "resume", "tvshowid", "art", "streamdetails", "firstaired", "runtime"], "limits": {"end": 20}, "sort": {"method": "random" }, "filter": {"field": "playcount", "operator": "lessthan", "value": "1"}}}
11:04:51 T:3580 DEBUG: CVideoDatabase::RunQuery took 19254 ms for 23367 items query: select * from episodeview WHERE ((episodeview.playCount IS NULL OR episodeview.playCount < 1))

It seems the widget ask for 20 items but the query does not have limits and as such is very very slow since I suppose playcount is not indexed

Yatse 2 : Media Center Remote Control for Touch Screens
Yatse, the Xbmc Remote and Widgets for Android
find quote
Post Reply