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 / \