SQLite simple timing profiler patch

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.

4 Responses to “SQLite simple timing profiler patch”


  1. 1 Andre

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

  2. 2 Mahalakshmi

    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

  1. 1 SQLite performance tuning and optimization on embedded systems at Katastrophos.net Blog
  2. 2 “Yet Another Zaurus Media Player”… done differently . (Phase 2: Development progress, no release yet.) at Katastrophos.net Blog
    Pingback on Jan 6th, 2007 at 5:49

Leave a Reply