Hullo Puneet, Many thanks for your response.
My understanding of a sqlite3 "transaction" is probably poor. From your response (if you are correct) I see that only UPDATES and INSERTS can be speeded up via bundling many numbers of them in a Being/Commit block? Leading me to ask: Is there no difference in behavior between a SINGLE select and several of them within the context of transaction? And yes, each of the many SELECTS have a different WHERE clause. -rosemary. On Apr 7, 2009, at 12:38 PM, P Kishor wrote: > On Tue, Apr 7, 2009 at 2:36 PM, Rosemary Alles > <al...@ipac.caltech.edu> wrote: >> Hullo Igor, >> >> Many thanks for your response: I believe I didn't phrase my question >> correctly: >> >> 1) If I were to bundle several thousand SELECT statements in a single >> transaction - why would it not run faster? > > as far as I understand, transactions matter only in the context of > UPDATEs and INSERTs for the purpose of speed (keep in mind, I am not > addressing data integrity -- the ACID part here, but only speed). A > transaction speeds this multiple UPDATEs and INSERTs by decreasing the > number of times your program interacts with slowest part of your > computer, the hard disk. > > Multiple SELECTs in a transaction might help with the integrity, but > ensuring that you don't end up getting data changed in mid-stream, but > won't speed up the query. > > Are all your thousands of SELECTs based on different WHERE criterion? > If not, they would really be just one SELECT. > >> 2) This is precisely the problem though - each of those statements >> will yield rows of results to be parsed with >> sqlite3_column - in the context of the user's (my) program. If many >> SELECT statements are issued within the context >> of a single transaction (repeatedly), how does one deal with the >> results without a callback (if using sql_step)? Yes, >> sql_exec is touted to be a wrapper around sql_prepare, bind, step. >> However, is does (also - additionally) offer the >> option of a user supplied calleback routine which sql_prepare etc. do >> not. >> >> Essentially, my question is about context. if many many SELECTS are >> bundled in a single transaction using prepare, >> bind and step. In what context does one parse the results? Do we not >> have synchronizing issue here? >> >> Thanks again, >> rosemary >> >> On Apr 6, 2009, at 8:03 PM, Igor Tandetnik wrote: >> >>> "Rosemary Alles" <al...@ipac.caltech.edu> wrote >>> in message news:20A6B796-613B-4F5D- >>> bfca-359d6b9fa...@ipac.caltech.edu >>>> I want to speed up my app. Can I run SELECT statements within the >>>> context of a transaction. >>> >>> Yes, but it's unlikely to make it run any faster. >>> >>>> If so, how does one handle the query >>>> results? >>> >>> The same way as when running it without an explicit transaction. >>> >>>> I would assume this cannot be done with sql_prepare, >>>> sql_bind, sql_step? >>> >>> Of course it can. See sqlite3_column_* >>> >>>> Would I *have* to use sql_exec >>> >>> No. And if you look at the implementation of sqlite3_exec, it uses >>> sqlite3_prepare and sqlite3_step internally anyway. It's maintained >>> mostly for backward compatibility. >>> >>>> What am I giving up >>>> by using sql_exec vs sql_prepare, sql_bind and sql_step? >>> >>> Off the top of my head: 1) strong typing (you get all data as >>> strings, >>> so that, say, an integer is converted to string and then you'll have >>> to >>> convert it back); 2) streaming (with sqlite3_exec, the whole >>> resultset >>> must be present in memory at the same time; with sqlite3_step, only >>> one >>> row's worth of data needs to be present in memory; makes a huge >>> difference for very large resultsets). >>> >>> Igor Tandetnik >>> >>> >>> > > > -- > Puneet Kishor http://www.punkish.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Carbon Model http://carbonmodel.org/ > Open Source Geospatial Foundation http://www.osgeo.org/ > _______________________________________________ > 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
