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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
