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 other hand there is a pretty simple workaround if you are
aware of the problem.
