Scott Hess wrote: > You should reduce your demonstration case to something you'd be > willing to post the code for. Probably using synthetic data > (preferably generated data). There's something missing in the thread > right now, and it's unlikely to be exposed by random shots in the > dark.
As I was putting together the answer to this request, I decided to double-check the result by #ifdef'ing out the sqlite calls. Turns out I *was* being stupid. An old app-level error check ran after a hunk of data was inserted. The check was supported by the PK definition, which I had just removed. So sqlite was doing a table scan every batch. Measurements were better with user-level indices because one of the indices was usable in the error check. *sigh* kill me. Sorry for wasting your time there. :/ So the summary of this thread for those who follow is: 1. Primary keys cause implicit indices to be defined. 2. If you insert data out of order (according to the index) then you have to read/write more pages per insertion. This, among other things, means the journal file can grow with the starting db size, not just with the size of the transaction. 3. Consider reducing churn against OS-level caches (or the disk) by increasing sqlite's cache_size. Thanks again, everyone, for your help! Eric -- Eric A. Smith You made the fatal mistake of assuming that your friends are genuine. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
