Re: [sqlite] Prepared statements must be generated inside your transaction
Joanne Pham wrote: > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > > So I have to do this: > begin transaction > prepared statement >.. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > I believe that used to be the case with early versions of sqlite 3. It is no longer true. You can see that sqlite 3.6.14 generates exactly the same opcodes when it prepares a statement either inside or outside a transaction using the explain command. SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a,b); sqlite> .explain on sqlite> explain insert into t values(1,2); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 10000 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 000 4 Integer1 3 000 5 Integer2 4 000 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 000 9 Halt 0 0 000 10Transaction0 1 000 11VerifyCookie 0 1 000 12TableLock 0 2 1 t 00 13Goto 0 2 000 sqlite> begin; sqlite> explain insert into t values(1,2); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 10000 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 000 4 Integer1 3 000 5 Integer2 4 000 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 000 9 Halt 0 0 000 10Transaction0 1 000 11VerifyCookie 0 1 000 12TableLock 0 2 1 t 00 13Goto 0 2 000 sqlite> The older versions of sqlite generated different code in these two cases. If a statement was to be executed inside a transaction it was necessary to compile (i.e. prepare) it inside a transaction (thought not necessarily the same transaction that it was to be executed in) in order for sqlite to generate the correct code. If my memory serves me correctly, I seem to recall it added some kind of a COMMIT opcode to the end of a statement when it was compiled outside a transaction. This opcode would incorrectly close the transaction when executed inside a transaction. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
Joanne Pham wrote: > Hi, > Thanks for information! > Regarding "batch" insert why we need to put the column binding > (sqlite3_bind...) before running sqlite3_step. For example: > sqlite_prepare_v2 > begin transaction > loop thru all the changes > sqlite3_bind > sqlite3_step. > end loop > end transaction > > For other database like Microsoft Sql server I only bind the column once(bind > statement outside the loop to the data structure) in the loop I don't need to > bind column again but just copy the new inserted row to the data structure > that already binded outside of the loop. In this case we don't need to bind > the columns in the loop. Why this way didn't work for SQLite3 database. > Thanks, > JP > > > > > > From: John Stanton > To: General Discussion of SQLite Database > Sent: Tuesday, May 12, 2009 12:09:09 PM > Subject: Re: [sqlite] Prepared statements must be generated inside your > transaction > > The confusion is in the names. When you "PREPARE" a statement you > actually compile the SQL. Compiling a program each time you run it is a > waste of resources, and so is compiling the SQL each time you use it. > > Prepare your statements once and use them many times, binding data to > the compiled code at execution time. > > The design of Sqlite is such that it is possible to store compiled SQL > permanently and use it when you run your application. I beleive there > is, or was a version of Sqlite tailored for embedded use which does just > that. > > In our Sqlite programs we like toprepare all SQL in an initialization > phase and have two wins. First we get faster execution and secondly we > detect database errors or mismatches before entering the main functions > of the program and avoid having to backtrack in error recovery. > > Pavel Ivanov wrote: > >> I believe, these matters are very specific for each database server >> (though I can't recall server for which it's true what you say). What >> specific server is talked about in this book? What's the name of this >> book? >> >> As long as SQLite is a concern, I prepare statements outside of >> transaction and then use them across different transactions without >> any problems but with huge performance improvement compared to when >> I've prepared statements before each transaction. >> >> Pavel >> >> On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: >> >> >>> Hi All, >>> I have read one of the performance document and it stated that "prepared >>> statements must be generated inside transaction". Is that correct. >>> The document also stated that " While trying to improve the performance of >>> bulk imports in our C++ project, we found that creating the prepared >>> statements was a large hit. Creating them once at the >>> construction of our class, though, made the problem worse! It turns >>> out that prepared statements that are generated before the transaction >>> start do not work with the transaction. The fix was simply to >>> create new prepared statements once per transaction." >>> >>> So I have to do this: >>> begin transaction >>> prepared statement >>> .. >>> end transaction. >>> >>> I though the prepare statement must be outside of the transaction. Can any >>> one confirm this? >>> Thanks, >>> JP >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > If you do not need to bind more than one data address, do not use the "?" (or > alternative) in your SQL. You use the bind capability to bind different data > addresses to the compiled SQL statement > The Sqlite binding method gives great flexibility in the use of compiled/prepared statements. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
Hi, Thanks for information! Regarding "batch" insert why we need to put the column binding (sqlite3_bind...) before running sqlite3_step. For example: sqlite_prepare_v2 begin transaction loop thru all the changes sqlite3_bind sqlite3_step. end loop end transaction For other database like Microsoft Sql server I only bind the column once(bind statement outside the loop to the data structure) in the loop I don't need to bind column again but just copy the new inserted row to the data structure that already binded outside of the loop. In this case we don't need to bind the columns in the loop. Why this way didn't work for SQLite3 database. Thanks, JP From: John Stanton To: General Discussion of SQLite Database Sent: Tuesday, May 12, 2009 12:09:09 PM Subject: Re: [sqlite] Prepared statements must be generated inside your transaction The confusion is in the names. When you "PREPARE" a statement you actually compile the SQL. Compiling a program each time you run it is a waste of resources, and so is compiling the SQL each time you use it. Prepare your statements once and use them many times, binding data to the compiled code at execution time. The design of Sqlite is such that it is possible to store compiled SQL permanently and use it when you run your application. I beleive there is, or was a version of Sqlite tailored for embedded use which does just that. In our Sqlite programs we like toprepare all SQL in an initialization phase and have two wins. First we get faster execution and secondly we detect database errors or mismatches before entering the main functions of the program and avoid having to backtrack in error recovery. Pavel Ivanov wrote: > I believe, these matters are very specific for each database server > (though I can't recall server for which it's true what you say). What > specific server is talked about in this book? What's the name of this > book? > > As long as SQLite is a concern, I prepare statements outside of > transaction and then use them across different transactions without > any problems but with huge performance improvement compared to when > I've prepared statements before each transaction. > > Pavel > > On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: > >> Hi All, >> I have read one of the performance document and it stated that "prepared >> statements must be generated inside transaction". Is that correct. >> The document also stated that " While trying to improve the performance of >> bulk imports in our C++ project, we found that creating the prepared >> statements was a large hit. Creating them once at the >> construction of our class, though, made the problem worse! It turns >>out that prepared statements that are generated before the transaction start >>do not work with the transaction. The fix was simply to >> create new prepared statements once per transaction." >> >> So I have to do this: >> begin transaction >> prepared statement >> .. >> end transaction. >> >> I though the prepare statement must be outside of the transaction. Can any >> one confirm this? >> Thanks, >> JP >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
The confusion is in the names. When you "PREPARE" a statement you actually compile the SQL. Compiling a program each time you run it is a waste of resources, and so is compiling the SQL each time you use it. Prepare your statements once and use them many times, binding data to the compiled code at execution time. The design of Sqlite is such that it is possible to store compiled SQL permanently and use it when you run your application. I beleive there is, or was a version of Sqlite tailored for embedded use which does just that. In our Sqlite programs we like toprepare all SQL in an initialization phase and have two wins. First we get faster execution and secondly we detect database errors or mismatches before entering the main functions of the program and avoid having to backtrack in error recovery. Pavel Ivanov wrote: > I believe, these matters are very specific for each database server > (though I can't recall server for which it's true what you say). What > specific server is talked about in this book? What's the name of this > book? > > As long as SQLite is a concern, I prepare statements outside of > transaction and then use them across different transactions without > any problems but with huge performance improvement compared to when > I've prepared statements before each transaction. > > Pavel > > On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: > >> Hi All, >> I have read one of the performance document and it stated that "prepared >> statements must be generated inside transaction". Is that correct. >> The document also stated that " While trying to improve the performance of >> bulk imports in our C++ project, we found that creating the prepared >> statements was a large hit. Creating them once at the >> construction of our class, though, made the problem worse! It turns >> out that prepared statements that are generated before the transaction start >> do not work with the transaction. The fix was simply to >> create new prepared statements once per transaction." >> >> So I have to do this: >> begin transaction >> prepared statement >>.. >> end transaction. >> >> I though the prepare statement must be outside of the transaction. Can any >> one confirm this? >> Thanks, >> JP >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
I think last-last paragraph answers your question and proves incorrectness of previous paragraph: "I've used this technique and it seems to work just fine. I had to remember to reset() the prepared statements immediately after I used them, however." :) Pavel On Tue, May 12, 2009 at 12:48 PM, Joanne Pham wrote: > Thanks for quick responde my email > This is sqlite documentation. Below is the link and last paragraph in this > document has stated that. > > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning > > > > From: Pavel Ivanov > To: General Discussion of SQLite Database > Sent: Tuesday, May 12, 2009 9:43:01 AM > Subject: Re: [sqlite] Prepared statements must be generated inside your > transaction > > I believe, these matters are very specific for each database server > (though I can't recall server for which it's true what you say). What > specific server is talked about in this book? What's the name of this > book? > > As long as SQLite is a concern, I prepare statements outside of > transaction and then use them across different transactions without > any problems but with huge performance improvement compared to when > I've prepared statements before each transaction. > > Pavel > > On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: >> Hi All, >> I have read one of the performance document and it stated that "prepared >> statements must be generated inside transaction". Is that correct. >> The document also stated that " While trying to improve the performance of >> bulk imports in our C++ project, we found that creating the prepared >> statements was a large hit. Creating them once at the >> construction of our class, though, made the problem worse! It turns >> out that prepared statements that are generated before the transaction start >> do not work with the transaction. The fix was simply to >> create new prepared statements once per transaction." >> >> So I have to do this: >> begin transaction >> prepared statement >> .. >> end transaction. >> >> I though the prepare statement must be outside of the transaction. Can any >> one confirm this? >> Thanks, >> JP >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
Thanks for quick responde my email This is sqlite documentation. Below is the link and last paragraph in this document has stated that. http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning From: Pavel Ivanov To: General Discussion of SQLite Database Sent: Tuesday, May 12, 2009 9:43:01 AM Subject: Re: [sqlite] Prepared statements must be generated inside your transaction I believe, these matters are very specific for each database server (though I can't recall server for which it's true what you say). What specific server is talked about in this book? What's the name of this book? As long as SQLite is a concern, I prepare statements outside of transaction and then use them across different transactions without any problems but with huge performance improvement compared to when I've prepared statements before each transaction. Pavel On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: > Hi All, > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > The document also stated that " While trying to improve the performance of > bulk imports in our C++ project, we found that creating the prepared > statements was a large hit. Creating them once at the > construction of our class, though, made the problem worse! It turns > out that prepared statements that are generated before the transaction start > do not work with the transaction. The fix was simply to > create new prepared statements once per transaction." > > So I have to do this: > begin transaction > prepared statement > .. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > Thanks, > JP > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
I believe, these matters are very specific for each database server (though I can't recall server for which it's true what you say). What specific server is talked about in this book? What's the name of this book? As long as SQLite is a concern, I prepare statements outside of transaction and then use them across different transactions without any problems but with huge performance improvement compared to when I've prepared statements before each transaction. Pavel On Tue, May 12, 2009 at 12:32 PM, Joanne Pham wrote: > Hi All, > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > The document also stated that " While trying to improve the performance of > bulk imports in our C++ project, we found that creating the prepared > statements was a large hit. Creating them once at the > construction of our class, though, made the problem worse! It turns > out that prepared statements that are generated before the transaction start > do not work with the transaction. The fix was simply to > create new prepared statements once per transaction." > > So I have to do this: > begin transaction > prepared statement > .. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > Thanks, > JP > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prepared statements must be generated inside your transaction
Hi All, I have read one of the performance document and it stated that "prepared statements must be generated inside transaction". Is that correct. The document also stated that " While trying to improve the performance of bulk imports in our C++ project, we found that creating the prepared statements was a large hit. Creating them once at the construction of our class, though, made the problem worse! It turns out that prepared statements that are generated before the transaction start do not work with the transaction. The fix was simply to create new prepared statements once per transaction." So I have to do this: begin transaction prepared statement .. end transaction. I though the prepare statement must be outside of the transaction. Can any one confirm this? Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users