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.
Please note, that this patch only measures the raw CPU time elapsed while executing a query. This is not equal to the realtime requirements.
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
I don’ understand what to do with any of the two files?
Coud you do please an step by step guide.
Thank you.
Its hard to come by educated people about this subject, however, you sound like you know what you’re talking
about! Thanks