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