SQLite simple timing profiler patch

Update: SQLite now features an integrated timer which can be activated via the .timer command in the console shell. Details here.

As a follow-up to my previous post “SQLite performance tuning and optimization on embedded systems” here is a very basic patch that introduces support for timing SQL queries in the sqlite3 console shell.

SQLite 3.3.8:
Patch against shell.c shell.c

SQLite 3.3.9 CVS - shell.c rev 1.157:
Patch against shell.c shell.c

Two new shell commands are introduced in sqlite3:

.profile ON|OFF        Turn profiling on or off
.timer start|show      Measure elapsed CPU time

.profile ON will turn off output to stdout. Instead it will display CPU execution time for every SQL command processed.
Here is an example output:

sqlite> .profile on
sqlite> .read test1.sql
Exec time  BEGIN;                                       :  0.000 s.
Exec time  DROP TABLE IF EXISTS playlist_view;          :  0.020 s.
Exec time  DROP TABLE IF EXISTS overview;               :  0.000 s.
Exec time  CREATE TEMPORARY TABLE playlist_view AS   ...  :  0.180 s.
Exec time  CREATE TEMPORARY TABLE overview AS        ...  :  0.140 s.
Exec time  SELECT DISTINCT genre, genre IN ("Progress...  :  0.030 s.
...

Note: You can still override the output setting with the .output command after an .profile ON has been issued.
.profile OFF will turn off time-based profiling and reenable output to stdout.

.timer start will start a timer. You can execute a sequence of queries and then print the required CPU time via .timer show.
Use .timer start again to reset the timer. This .timer patch is based on SQLite ticket #1227 by Bartosz Polednia.

Timing is currently done using clock() which provides only a 10 ms precision for CPU time on most systems. This might be inadequate depending on your requirements. If you know a more precise way, please let me know.

This entry was posted on Thursday, January 4th, 2007 at 12:27 and is filed under Development, Hack, Quasar Media Player . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “SQLite simple timing profiler patch”

  • Andre Says:
    January 4th, 2007 at 12:29

    Please note, that this patch only measures the raw CPU time elapsed while executing a query. This is not equal to the realtime requirements.

  • SQLite performance tuning and optimization on embedded systems at Katastrophos.net Blog Says:
    January 4th, 2007 at 12:31

    [...] Software « “Yet Another Zaurus Media Player”… done differently . (Phase 2: “Development progress, no release yet.”) SQLite simple timing profiler patch » [...]

  • “Yet Another Zaurus Media Player”… done differently . (Phase 2: Development progress, no release yet.) at Katastrophos.net Blog Says:
    January 6th, 2007 at 5:49

    [...] With that being said, different rules in database design apply for embedded systems: In the third approach I already created a pretty decent database schema. Something I naturally would have done on a desktop system. Keeping the layout clean, using relations where applicable, minimizing data storage requirements. On a desktop system dereferencing and joining tables is fast. However, not so on my Zaurus: Simple left-joins over three tables would take up to a few hundred milliseconds. In contrast, these queries are almost unmeasurable on my desktop system, meaning they were faster than 10 ms. Now add a few other equally expensive queries to that and imagine, you’re doing a search on your playlist with 2000 items. Do you want to wait 3 seconds or longer for the result? That’s not what I call interactive. So, I had a nice profiling, optimizing and testing marathon last weekend. To make a long story short, after analyzing the bottlenecks and also having a lengthy discussion with a DB-guru friend, I ended up simplifying the database schema in a direction I wouldn’t normally take on a desktop system. It’s not totally ugly now, but it’s just not as relational as you might expect a SQL database to be. Also, some data is redundantly held in temporary tables, which isn’t nice either, but helps performance A LOT. In order to do the profiling I made some changes to the SQLite codebase, which I will post shortly along with some optimization hints. Update: Hints here, patch here. [...]

  • Mahalakshmi Says:
    April 16th, 2007 at 10:36

    Hi,

    I created a table named MUSIC in sqlite.It consists of 11 fields which is as follows.

    “create table MUSIC(Id integer primary key,
    Album text not null collate nocase default ‘Unknown’ ,
    URL text not null collate nocase default ‘Unknown’ ,
    Duration integer,
    Track integer,
    BitRate integer,
    sampleRate integer,
    Channels integer,
    Genre text not null collate nocase default ‘Unknown’,
    Artist text not null collate nocase default ‘Unknown’,
    Filesize integer);”

    I want to optimise the speed so i performed indexing for Album,Artist and Genre fields alone.
    But i could not find anyoptimization after indexing these 3 fields.
    I tried with indexing all the fields but still i could not achieve optimization.

    I will often use only these following statements

    “select distinct Album from MUSIC;”
    “select distinct Artist from MUSIC;”
    ” select distinct Album from MUSIC where Artist=”A.R.Rehman’ ;”
    ” select URL from MUSIC where Artist=”A.R.Rehman’ and Album=’Vantae Mathram’;”
    ” select * from MUSIC where Artist=”A.R.Rehman’ and Album=’Vantae Mathram’;”

    Can u please help me to make maximum optimization for the previous statements.

    Regards,
    M.Mahalakshmi

Leave a Reply