Database query speedups

Posted By: Team XBMC on Aug 24, 2008 in Site News

I was testing some smartplaylists out from some bug reports, and found that the same query was performed significantly faster in SQLiteSpy than in XBMC on the same machine.  The performance was an order of magnitude slower in XBMC than it was in SQLiteSpy, and we actually use a newer version of SQLite than is in the version of SQLiteSpy I was testing.

Turns out that we’re using a “wrapper” around the lib, which uses sqlite3_exec() and a callback routine to grab the per-row data.  This isn’t too much of a problem (see below) and is certainly an easy way to go about things.  The slow downs were caused by the way the callback was implemented.

The main issue was due to allocation + deep copying. For each row of data, the callback gets called, and we copied the data into a row_type = std::map<int, field_value>, where field_value was basically a string.  The row is then pushed into a std::map<int, row_type>.  This, ofcourse, results in numerous allocation + deallocation as the maps are copied + moved around in memory, in order to optimize the map lookup.  The use of map here is the first obvious problem: It’s indexed by the row number, so why not use a vector?  Furthermore, if we use a vector<row_type *> then it’ll save constantly copying data whenever we have to resize the vector.

The second problem isn’t quite as bad, but is inherent to the way sqlite3_exec() is handled.  Each field in each row is converted to a string to be passed to the callback, and we must then go and convert it back to the appropriate type for actual use.  We can skip this step by breaking the sqlite3_exec() down into what it does behind the scenes (sqlite_prepare/step/column/finalize) and just copy the appropriate datatype straight away, ensuring that no conversion needs to be performed.

Result: The old version took 1760+19862 ms (query + retrieval), whereas the new version takes just 324+1370 ms.  Not bad for an hours effort.

Share on reddit


Discussion - 5 Comments

  • Jezz_X Aug 25, 2008 

    Well since no one else will say it NICE!!!!

  • randomcomment Aug 25, 2008 

    Really great optimization

  • Nickman Aug 28, 2008 

    Every bit counts. Great work.. As always ;)

  • Johny007 Sep 10, 2008 

    Great work, nice to see that there is still a way how to speed up things.

  • Sean Keeney Sep 13, 2008 

    We had the same problem with a project at my last job, with the same fix. After doing this and changing some indexes there was an order of magnitude in performance.

    It also had the effect of making it faster than using mysql, which shut up a fairly vocal mysql loving colleague :D

    Good work by the way :)

About XBMC

XBMC is a free and open source media player application developed by the XBMC Foundation, a non-profit technology consortium. XBMC is available for multiple operating-systems and hardware platforms, featuring a 10-foot user interface for use with televisions and remote controls. It allows users to play and view most videos, music, podcasts, and other digital media files from local and network storage media and the internet.