There is an excellent FAQ [http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html] 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

----
SQLite does the above optimization automatically.
See http://www.sqlite.org/optoverview.html#like_opt
But the optimization is not valid for LIKE since LIKE is case insensitive
by default.  But if you turn on case sensitivity for LIKE, or if you make
the collating sequence of the column case insensitive, or if you use GLOB
instead of LIKE (since GLOB is case sensitive) then the optimization should
work for you.

[AMG]: Regarding LIKE optimization: Recently I needed to do many, many case insensitive lookups on a symbol table.  LIKE worked but was uncomfortably slow, so I just added a new table mapping between commons and their lowercased names, and I created an index on the lowercase column.  This proved to be a gigantic performance win.  (As you may have guessed, this symbol table includes a lot of FORTRAN symbols grouped into common blocks.)

LIKE gotcha: I also used LIKE to find all commons whose names didn't start with @, but when I changed the leading character to _, the search broke.  This is because LIKE interprets _ as a metacharacter (any one character), same as how % means any number of characters.  Switching to "glob _*" was the solution.

----

[pcam] Has anyone experienced the cache method and managed to have some prepared query ? Does it improve performance ?
If so please include a snippet.

<<categories>> Database | Example | Performance