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.

  4. Its hard to come by educated people about this subject, however, you sound like you know what you’re talking
    about! Thanks

Leave a Reply

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


This site uses Akismet to reduce spam. Learn how your comment data is processed.