On Wed, Sep 30, 2009 at 7:13 AM, Pavel Ivanov <paiva...@gmail.com> wrote: >> Well, assuming that your >> SELECT is selecting the rows you mean to UPDATE or INSERT ... > > Also assuming that > - all data necessary for these statements can fit into sqlite's cache;
Generally the desire to UPDATE-if-present-else-INSERT involves a single row and the UPDATE involves a WHERE clause on a primary key. I'm sure other cases could be constructed, but an all-in-one statement to handle them would not be reasonable. > - several instances of your application cannot be executed in parallel > (otherwise you have to do a special treatment for SQLITE_BUSY return > value); In some cases the easy fix to this is "BEGIN IMMEDIATE". > And finally could you say it once more: what is the benefit from doing > select first and then insert/update as opposed to just insert/update > without select? I see that in your case insert/update could be > executed faster, without disk I/O, but if we look at them combined > what's the difference? The advantage is that the code is easier to read. It essentially says "Is there a row there? If so, update it, otherwise insert a new row." I'm not meaning to suggest that there is never a benefit to attempting the UPDATE then following up with an INSERT if the row is missing (and then an UPDATE if someone beats you in). It's just that the benefit to that pattern is often not NEARLY so great as it is in a client/server system, where the communications overhead often dominates the query time. If you prepare your statements ahead of the BEGIN, and then blast through them with no non-SQLite calls interleaved, then your code often isn't going to be much less efficient than SQLite would be doing it all internally. I see this a lot, where coders write a convoluted statement to save time which would probably save time over the alternative on MYSQL, but which doesn't help much on SQLite. I guess measure before making it convoluted :-). -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
