Christian Smith <[EMAIL PROTECTED]> wrote:
>
>
> Run time compilation is not that expensive if the generated vm is cached.
> Just have a per-connection hash, use the SQL as the hash key, and the
> resulltingvm as the value. Upon first use, the SQL will not be in the
> hash, and will be compiled and inserted. Subsequent uses of the same SQL
> will be quickly retrieved from the hash and used as is.
>
> This is how the TCL wrappers work, I believe.
>
Yes, this is how the TCL wrapper works. But there are
some complications.
(1) It is important to limit the cache size. Otherwise
the size of the cache might grow without bound. The
TCL extension caches the last 10 statements by
default (the cache size can be adjusted at runtime).
The cache employs LRU replacement.
(2) For any precompiled and cached statement, your code
needs to be ready to deal with an SQLITE_SCHEMA error
by deleting the old statement and rebuilding it using
sqlite3_prepare(). An SQLITE_SCHEMA error occurs
when the precompiled statement detects that the current
database schema is different from the schema at the
time the statement was first compiled. The TCL
wrapper handles SQLITE_SCHEMA errors automatically so
that a TCL programmer never has to know about them.
The second point is the primary reason why Daniel's
original idea of precompiling statements in C++ programs
might not be as helpful as he imagines. If statements
were precompiled into the C++ program, then the C++
program would have to be recompiled whenever the database
schema changed, which could quickly get to be bothersome.
As Joe Wilson pointed out, I sell a proprietary version
of SQLite that does something like what Daniel suggests.
The proprietary version omits the SQL parser and code
generator and is thus much smaller than the standard
SQLite - as small 65KiB. There is currently just one
user of this extension - a manufacturer who puts the
code on smart-cards with extremely tight code memory
constraints. Another consumer device manufacturer is
also looking into this technology.
Using the proprietary extension, statements are prepared
on a workstation and then the compiled forms of the
statements are stored in a special table in the database.
The database is then transfered to the device. The device
has just enough code to read the special table, extract
the prepared statements, and run the prepared statements.
You can also bind values the prepared statements so that
INSERT, and UPDATE statements are useful. Note, however,
that the device is unable to modify the database schema in
any way, because if it were to do so, all of the precompiled
statements would have to be compiled again, and the device
lacks the SQL parser and code generator needed to do that.
So this extension, while useful in certain niche applications,
is not particularly helpful to most people.
--
D. Richard Hipp <[EMAIL PROTECTED]>
