{"id":57,"date":"2007-01-04T12:27:10","date_gmt":"2007-01-04T10:27:10","guid":{"rendered":"http:\/\/katastrophos.net\/andre\/blog\/2007\/01\/04\/sqlite-simple-timing-profiler-patch\/"},"modified":"2008-12-28T23:15:19","modified_gmt":"2008-12-28T22:15:19","slug":"sqlite-simple-timing-profiler-patch","status":"publish","type":"post","link":"https:\/\/katastrophos.net\/andre\/blog\/2007\/01\/04\/sqlite-simple-timing-profiler-patch\/","title":{"rendered":"SQLite simple timing profiler patch"},"content":{"rendered":"

Update: SQLite now features an integrated timer which can be activated via the .timer command in the console shell. Details here.<\/a><\/strong><\/p>\n

As a follow-up to my previous post “SQLite performance tuning and optimization on embedded systems”<\/a> here is a very basic patch that introduces support for timing SQL queries in the sqlite3 console shell.<\/p>\n

SQLite 3.3.8<\/strong>:
\nPatch against shell.c<\/a> shell.c<\/a><\/p>\n

SQLite 3.3.9 CVS<\/strong> – shell.c rev 1.157:
\nPatch against shell.c<\/a> shell.c<\/a><\/p>\n

Two new shell commands are introduced in sqlite3:<\/p>\n

\r\n.profile ON|OFF        Turn profiling on or off\r\n.timer start|show      Measure elapsed CPU time\r\n<\/pre>\n
.profile ON<\/strong> will turn off output to stdout. Instead it will display CPU execution time for every SQL command processed.
\nHere is an example output:<\/p>\n
\r\nsqlite><\/strong> .profile on\r\nsqlite><\/strong> .read test1.sql\r\nExec time  BEGIN;                                       :  0.000 s.\r\nExec time  DROP TABLE IF EXISTS playlist_view;          :  0.020 s.\r\nExec time  DROP TABLE IF EXISTS overview;               :  0.000 s.\r\nExec time  CREATE TEMPORARY TABLE playlist_view AS   ...  :  0.180 s.\r\nExec time  CREATE TEMPORARY TABLE overview AS        ...  :  0.140 s.\r\nExec time  SELECT DISTINCT genre, genre IN (\"Progress...  :  0.030 s.\r\n...\r\n<\/pre>\n
Note: You can still override the output setting with the .output<\/strong> command after an .profile ON<\/strong> has been issued.
\n.profile OFF<\/strong> will turn off time-based profiling and reenable output to stdout.<\/p>\n
.timer start<\/strong> will start a timer. You can execute a sequence of queries and then print the required CPU time via .timer show<\/strong>.
\nUse .timer start<\/strong> again to reset the timer. This .timer patch is based on SQLite ticket #1227<\/a> by Bartosz Polednia.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[20,19,24],"class_list":["post-57","post","type-post","status-publish","format-standard","hentry","tag-development","tag-hack","tag-quasar"],"_links":{"self":[{"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/posts\/57"}],"collection":[{"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/comments?post=57"}],"version-history":[{"count":2,"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/posts\/57\/revisions"}],"predecessor-version":[{"id":82,"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/posts\/57\/revisions\/82"}],"wp:attachment":[{"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/media?parent=57"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/categories?post=57"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/katastrophos.net\/andre\/blog\/wp-json\/wp\/v2\/tags?post=57"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}