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