This has come up on the list before, and should probably be documented on
the Wiki in http://www.sqlite.org/cvstrac/wiki?p=PrecompiledSql.
As to whether it's a problem, I think probably not as the 'fix' is so
trivial. If you want to fix, then simply wrap the begin/compile/commit in
a new function called, say sqlite3_compile_transaction, which will do the
compile in a transaction as per your code, and create a ticket with a
patch for DRH to possibly intergrate into the next release.
As the sqlite3_compile API is still experimental (the SQLite V3 API
reference doesn't list it) this is probably a good time to augment the
API, especially if done in a backward compatible way. I don't think anyone
would argue with that.
Christian
On Wed, 4 Aug 2004, Dennis Cote wrote:
>Hi All,
>
>I have discovered a quirk in the SQLite API (both 2.8 and 3.0) when using
>precompiled SQL statements with transactions.
>
>My goal was to perform a series of inserts into a database as quickly as
>possible. To this end I first used a single explicit transaction around the
>series of inserts. Secondly, to eliminate the overhead of compiling the SQL
>code for each insert I precompiled the SQL statement using sqlite_compile.
>Furthermore since this loop is repeated many times, the SQL is compiled only
>once at the beginning of the program and reused when needed.
>
>In pseudocode my loop looks like this;
>
>vm = sqlite_compile ("insert into t values (?,?)")
>
>sqlite_exec ("begin")
>loop {
> sqlite_reset(vm)
> sqlite_bind(vm, 1, ...)
> sqlite_bind(vm, 2, ...)
> sqlite_step(vm)
>}
>sqlite_exec("commit")
>
>The problem is that the loop executed very slowly. The reason it ran slowly
>is that the virtual machine was compiled outside of the transaction, so it
>included the instructions to open and commit a default transaction. As a
>result my explicit transaction was ended by the first sqlite_step call in
>the loop and every other insert ran in its own automatic transaction (which
>everyone knows is quite slow).
>
>The workaround is quite straightforward; simply begin a transaction before
>compiling the statement, and end it afterwards. Since there is an explicit
>transaction active when the VM is compiled, it does not contain the
>instructions to open and commit a default transaction.
>
>My pseudocode now looks like this;
>
>sqlite_exec ("begin")
>vm = sqlite_compile ("insert into t values (?,?)")
>sqlite_exec("commit")
>
>sqlite_exec ("begin")
>loop {
> sqlite_reset(vm)
> sqlite_bind(vm, 1, ...)
> sqlite_bind(vm, 2, ...)
> sqlite_step(vm)
>}
>sqlite_exec("commit")
>
>The loop now runs much much faster (on the order of 100 times faster).
>
>My concern is that opening and closing a real transaction, which does no
>I/O, in order to compile an SQL statement is "strange". It seems to me that
>I should be able to pass a flag to the sqlite_compile function that
>explicitly tells it to include the automatic transaction instructions or
>not.
>
>What do you think? Is this quirk worth an API change? I know the version 3.0
>API is supposed to be frozen, but maybe it's better to change it now rather
>than later. On the otherhand there is a pretty simple workaround if you are
>aware of the problem.
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
