> Actually my understanding would suggest that INSERT OR REPLACE should > execute slower than UPDATE + INSERT (or INSERT + UPDATE).
After some experimentation, I think that they turn out to be a wash in my case. INSERT OR REPLACE is implemented in sqlite as INSERT OR DELETE THEN INSERT, which due to my access patterns puts the really common (k1, k2) pairs together at the end of the database (or at least with the highest row ids, which tend to group together), which increases the chances to share pages for those common pairs. > And about journal size: imagine that you've got "lucky" and > all those 94k rows are each in it's own page in the counters table. > SQLite will have to save each of that pages in the journal which will > give journal size of about 94k * 4096 ~ 400M. Maybe it makes sense to drop my page size then all the way down to the disc's sector size. That reduces the changes that a given set of pairs shares pages but clearly that's already very low. It would at least reduce the number of bytes needing to be written, even if the number of seeks stays essentially the same. But since it's seeks that are probably dominating the I/O, maybe it makes sense to increase the transaction batch size so that we're overwriting most of the DB on every commit rather than overwriting (say) 10%, 10% as often. Does that make sense? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
