On Wed, Sep 30, 2009 at 10:41 AM, Ken <kennethinbox-sql...@yahoo.com> wrote: > --- On Wed, 9/30/09, Scott Hess <sh...@google.com> wrote: >> Since SQLite is an embedded database, >> it generally does not pay to >> count statements, unless they add additional disk >> I/O. You can code >> like this: >> >> BEGIN >> SELECT ... >> if (select results A) >> INSERT ... >> else >> UPDATE ... >> END >> >> and it will be about as fast as either the INSERT or the >> UPDATE run >> independently. This is because the INSERT or the >> UPDATE will have to >> read in all the pages the SELECT would have read in, so the >> SELECT is >> essentially free (just a small cost in CPU). Well, >> assuming that your >> SELECT is selecting the rows you mean to UPDATE or INSERT >> ... > > Depending upon your system and your data. > Say you have some type of Primary Key or unique index. > > For the case where updates happen infrequently code this way. > Begin > Insert into .. > IF PK failure > Update > Commit > > If you tend to load up the data then have more updates. > > Begin > Update > IF No data Found (0 rows updated) > Insert > Commit
My point is that if you have a primary key, the two variants will have almost identical performance because performance is dominated by disk I/O, so use the one which makes your code clearer. This is different from a client/server database system, where talking to the server four times is materially worse than three times, if you can get away with it. -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
