There is an excellent FAQ [L1 ] for tweaking your use of an SQLite database for optimal performance. Almost all the suggestions in the FAQ are applicable to code using the Tcl binding, but they are not actually expressed in Tcl code snippets. Hence this page, which attempts to do so.
In "Section 5: Optimizing Queries" the suggestion is made to replace GLOB and LIKE where possible. The example given in the FAQ is "The expression (x GLOB 'abc*') can be replaced by (x >= 'abc' AND x < 'abd')." In other words, if you have an INDEX on col_name, and you want to match the first few characters of the values in col_name, replace this:
proc search_partial_match_slowly {str} {
append str %
dbcmd eval {SELECT * FROM table_name WHERE col_name LIKE $str} result {
parray result
}
}with this:
proc search_partial_match_quickly {str} {
set sub_str [string range $str 0 end-1]
set last_char [string index $str end]
set end $sub_str[format %c [expr {1+[scan $last_char %c]}]]
dbcmd eval {SELECT * FROM table_name WHERE (col_name >= $str AND col_name < $end)} result {
parray result
}
}for greatly increased speed. (Time taken was reduced from much longer than one minute to much less than one second, in my case.) Alastair Davies 14 December 2006