A TRANSACTION only has a meaning when the database is being altered. There are no journalling and commits on a read. You get the results of the SELECT as soon as the database read occurs. You cannot be faster than that.
Where you can get improved SELECT performance is by using caching. Explore shared caches JS. Rosemary Alles wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
