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.
January 4th, 2007 at 12:29
Please note, that this patch only measures the raw CPU time elapsed while executing a query. This is not equal to the realtime requirements.
January 4th, 2007 at 12:31
[...] Software « “Yet Another Zaurus Media Player”… done differently . (Phase 2: “Development progress, no release yet.”) SQLite simple timing profiler patch » [...]
January 6th, 2007 at 5:49
[...] With that being said, different rules in database design apply for embedded systems: In the third approach I already created a pretty decent database schema. Something I naturally would have done on a desktop system. Keeping the layout clean, using relations where applicable, minimizing data storage requirements. On a desktop system dereferencing and joining tables is fast. However, not so on my Zaurus: Simple left-joins over three tables would take up to a few hundred milliseconds. In contrast, these queries are almost unmeasurable on my desktop system, meaning they were faster than 10 ms. Now add a few other equally expensive queries to that and imagine, you’re doing a search on your playlist with 2000 items. Do you want to wait 3 seconds or longer for the result? That’s not what I call interactive. So, I had a nice profiling, optimizing and testing marathon last weekend. To make a long story short, after analyzing the bottlenecks and also having a lengthy discussion with a DB-guru friend, I ended up simplifying the database schema in a direction I wouldn’t normally take on a desktop system. It’s not totally ugly now, but it’s just not as relational as you might expect a SQL database to be. Also, some data is redundantly held in temporary tables, which isn’t nice either, but helps performance A LOT. In order to do the profiling I made some changes to the SQLite codebase, which I will post shortly along with some optimization hints. Update: Hints here, patch here. [...]
April 16th, 2007 at 10:36
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
February 12th, 2009 at 16:56
I don’ understand what to do with any of the two files?
Coud you do please an step by step guide.
Thank you.
February 3rd, 2010 at 22:00
[...] SQLite simple timing profiler patch [...]
February 3rd, 2010 at 22:05
[...] SQLite simple timing profiler patch [...]