Katastrophos.net Blog

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.