The TCL interface to SQLite does not currently support precompiled
statements. Each SQL statement executed is compiled for that one
execution. If the same statement is executed multiple times, it is
compiled multiple times. This note considers ways of addressing
that deficiency.
The most obvious approach is to add to the database object a
"compile" method that returns a new object that is the precompiled
statement. For example:
set stmt [db compile $sql]
$stmt eval
$stmt finalize
An extension of this idea is to allow the precompiled statement to
also be used in place of the SQL argument on the "eval" method of
the main database object. Like this:
set stmt [db compile $sql]
db eval $stmt
The two techniques above can both be implemented at the same time,
allowing the programmer to use whichever seems more convenient.
The third approach (and the one that I currently prefer) uses no
API change at all and is completely transparent to the programmer.
In the third approach, the TCL interface caches precompiled statements
internally and simply reuses them when the same SQL strings are
presented for execution. Legacy programs would require no changes
to take advantage of precompiled statements - they just magically
run faster.
The issue with the third approach is deciding when to clear the
precompiled statement cache. Precompiled statements use memory
and we do not want them to hang around forever. Ideas for how
to deal with this problem include:
(1) Clear the cache at each COMMIT. The primary use of
precompiled statements is to do multiple INSERTs without
having to recompile the INSERT statement multiple times.
But multiple INSERTs are also usually done inside a
transaction. So when the transaction commits, it usually
means we are done with the INSERTs.
(2) Store the N most recent statements where N is a constant
in the range of 5 to 10. Here again, the assumption is
that precompiled statements are most helpful inside tight
loops.
(3) Only cache statements that have named parameters. The idea
here is that statements without parameters are rarely executed
more than once (why would you want to do the same thing twice?)
so do not bother filling the cache with statements that will
never be reused.
(4) Create a new method on the database object that enables and
clears the precompiled statement cache. This would require
minimal changes to legacy code. Perhaps the enabling statement
could also set the value for N in (2) above.
An actually implementation would probably implement some combination
of the above ideas. The question is, which combination.
The third approach might also be combined with the first two.
An implementation of all three would provide automatic caching
in the common case so that applications could take advantage of the
cache with no programmer intervention. But an explicit precompile
of the first two techniques would be available to programmers
who want more control.
Thoughts?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
