On 31 Dec 2012, at 9:13pm, Roger Binns <rog...@rogerbinns.com> wrote:
> You should create the indexes after inserting the data. If you don't you > end up with index data and row data intermingled in the database which > makes things slower. > > Journaling off definitely worked for me when I benchmarked it. I was > working with a 15GB dump from postgres on Linux. To elucidate, theoretically the fastest combination of operations is ... Turn journalling off Delete all indexes If you need to create tables, do it here BEGIN ... do all your inserts COMMIT Create indexes Turn journalling on Note that if you're using FOREIGN KEYS that changes this a little since SQLite won't let you do an insert without an index which lets it do the necessary lookups. However, unless you're doing this sort of thing as part of a regular working day this really doesn't matter. It takes 5 minutes to load your rows instead of 4 ? Who cares. For anything that takes more than 3 minutes I'm off getting coffee anyway. For normal operation the amount of time you save from BEGIN DELETE FROM myTable; ... do all your inserts COMMIT isn't worth the difference, and saves a lot of programming and error-handling. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users