> 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

Reply via email to