xbmc/xbmc — pull request #1085 by Montellese (2012-06-17 14:32:25+00:00)
Title: A few database/query optimizations

vdrfan and myself took the time to log all the SQL queries we run in XBMC (especially during startup etc) and we were surprised by the huge number of queries that are run when XBMC starts (without doing a single action as a user). We have taken a look at the queries that appeared most in the log and tracked them down. We have done the following optimizations (in the order of the commits):

1. On every CDatabase::Open we check the version of the database we are opening to see if we need to update the database. These queries make up about 20% of all queries that are run in XBMC. Once a database has been updated, we will never have to update it again while XBMC runs so there's no need to check for it every time. Using a map we remember which databases we have already opened (and updated) and don't run the version query anymore if the db is already in the map.
2. Adding bookmark.timeInSeconds and bookmark.totalTimeInSeconds to the video views saves us from having to make those calls to retrieve a video's resume point manually. This saves at least one query for every video when listed in the GUI.
3. In the recently added job for recently added album songs we iterate over all retrieved songs and retrieve their album id and album thumb (two extra queries per song). The album id is already available in CMusicInfoTag (just not exposed) so we can save that query completely. Furthermore as we retreive multiple songs from the same album they can all use the same album cover so we can just remember the cover of the current album until the album changes and only then retrieve the album's cover. These two changes saved 190 (of 195) queries for the recently added album songs widget in my setup.
4. see 5.
5. jmarshall added a new seasons table in his recent work and that seasonid is needed to retrieve season thumbs. By storing the idSeason value in the video views and in CVideoInfoTag we can save a query for every episode. Furthermore we can save some queries in recently added episodes by also remembering the season and show thumb for every season/show and only querying the database if we haven't come across that season/show yet. This saves about half of the queries for the recently added episodes widget.
6. the implementation for recently added albums retrieves the values for rating, year, artist and album title manually with 1 query per attribute (i.e. 4 queries per album) even though that data is already available in the CAlbum object's we get from the database.
7. When retrieving sets, we retrieve all movies that are part of a set and put the set together based on those movies (to get additional metadata like rating, year etc). In that case we don't need to retrieve the stream details because we don't use them and they'll be retrieved when browsing into the set in the GUI. So we can save those queries.
8. When a user adds addon shortcuts to the confluence home screen, the addon title and icon are constantly retrieved from CGUIInfoManager to be able to show them in the GUI. During that query we also check against the addon database, if the addon is disabled even though we don't use that information, so that check is unnecessary. This saves a lot of queries because they are repeated periodically as long as the user is on the home screen.
This is even worse for the global search addon which has been integrated into the confluence home screen by default. For every frame (i.e. 60 times per second) we query the enabled/disabled state of the addon from the database (SYSTEM_HAS_ADDON). We haven't looked into that yet, but maybe someone can shed some light on it.
9. When changing a view/window in the GUI we have to retrieve the next view/viewstate which includes queries to the ViewState database. Instead of doing one query to retrieve the view state we run the same query 3-4 times in different places. Especially on embedded devices these queries are rather costly and slow down the changing of views/windows. This commit is highly experimental because neither vdrfan nor myself have any real knowledge about this process.

All in all with these changes the number of queries on startup (before doing a single user interaction) goes down to 20-30%. On fast systems there's no real noteable speed-up because some of those queries are not expensive at all but on embedded devices (where I/O (i.e. accessing the sqlite database file) is more time intensive and computation power more scarce) it could speed up the startup process and some loading times of item lists.