Re: [sqlite] precompiled SQL and transactions
Michael Roth wrote: > Exists the same error in the sqlite3_prepare call? Anybody validated > this? I just tested this with SQLite V3.0 and it does not display this quirk. I get the same performance using a statement that is prepared inside a transaction and one that is prepared outside a transaction. Both of these execute much faster than the same statement when no explicit transaction is used (i.e. automatic transaction per insert statement). FYI: I did a loop that executed 5,000,000 precompiled inserts into a single table in about 63 seconds with an explicit transaction. Using the automatic transactions for each insert statement I can execute only 500 inserts in about 61 seconds. Using explicit transactions is about 10,000 times faster! Using sqlite_exec to execute the inserts directly, rather than precompiling, with an explicit transaction does about 1,250,000 inserts in 64 seconds. The precompiled statement is about 4 times as fast as direct execution (i.e. compile and execute each statement).
Re: [sqlite] precompiled SQL and transactions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Christian Smith wrote: | 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 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. AFAIK sqlite_compile in sqlite3 is called sqlite3_prepare?!?! And sqlite3_prepare is documented: http://www.sqlite.org/capi3ref.html#sqlite3_prepare Exists the same error in the sqlite3_prepare call? Anybody validated this? -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBFBhQSIrOxc3jOmoRAqVfAJ4x03t8eoQ7Kbwirch2kSsu+pDuXwCfao4c j/yB4secbAug+nhuHXExugs= =dXcg -END PGP SIGNATURE-
Re: [sqlite] precompiled SQL and transactions
Sorry for the duplicate. I thought my orignal message had been rejected because I received a spamfilter message (see attached), so I sent it again. Your email has been blocked by the AntiSpam filter, and redirected to /dev/null (trash). This is the header of the original message: _From: "Dennis Cote" <[EMAIL PROTECTED]> _To: "sql-users" <[EMAIL PROTECTED]> _Date: Wed, 4 Aug 2004 12:25:36 -0600 _Subject: [sqlite] precompiled SQL and transactions NOTE: DO NOT REPLY TO THIS MESSAGE, IT WILL BE AUTOMATICALLY REDIRECTED TO TRASH. (Non rispondere a questo messaggio, verra' automaticamente eliminato dal server) You may have received this message because the anti-spam filter has considered your email a spam (junk) mail. We apologize for the inconvenient, and we ask you to contact the destination person, or try to resend the message. (Avete ricevuto questo messaggio perche' il server ha considerato la vostra email come un possibile spam. Ci scusiamo per l'inconveniente, chiedendovi cortesemente di contattare il destinatario della posta elettronica in oggetto, o di provare a reinviare il messaggio.)
Re: [sqlite] precompiled SQL and transactions
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 / \
[sqlite] precompiled SQL and transactions
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.
[sqlite] precompiled SQL and transactions
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.