On 31 Dec 2012, at 9:13pm, Roger Binns <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
