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