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
