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 <jo...@viacognis.com> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > 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 <joannekp...@yahoo.com> 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
