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.

9 thoughts on “SQLite simple timing profiler patch

  1. 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. 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.


  3. I don’ understand what to do with any of the two files?
    Coud you do please an step by step guide.
    Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *