On Tue, Apr 7, 2009 at 3:45 PM, Rosemary Alles <al...@ipac.caltech.edu> 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?
Not that it is any standard, but search for the word "transaction" at http://developer.postgresql.org/pgdocs/postgres/sql-select.html You will see, Pg recommends using SELECTs inside a TRANSACTION for just the reason I mentioned in my email... ensuring that you retrieve something dependable that is not changed on you midstream, not for speed. > Leading me to > ask: > Is there no difference in behavior between a SINGLE select and several > of them within the context of transaction? What do you mean by "behavior"? Do you mean what you will get back? No, it shouldn't be different. Do you mean how fast you will get it back? Dunno, but you can tell for sure by writing a trivial benchmarking script on your data. > > And yes, each of the many SELECTS have a different WHERE clause. Don't mean to preempt your application, but bunching SELECTs with different WHERE clause makes little sense. I mean, if you are doing SELECT .. FROM .. WHERE color = 'blue' SELECT .. FROM .. WHERE color = 'red' SELECT .. FROM .. WHERE color = 'green' you can just as well do SELECT .. FROM .. WHERE color IN ('blue','red','green') On the other hand, if you are doing SELECT .. FROM .. WHERE color = 'blue' SELECT .. FROM .. WHERE taste = 'bitter' SELECT .. FROM .. WHERE type = 'pill' That doesn't make much sense, but can also be accomplished with a single SELECT and a bunch of ORs Maybe you should explain your actual problem a bit more. What exactly are you trying to accomplish? What does your db look like? Provide some sample data, and perhaps example of your multiple but different SELECT queries that you want to wrap in a transaction. Once again, if only speed is your aim, benchmark it. > > -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
