On 17 Aug 2010, at 12:16am, Maciej Kurczewski wrote: > I'm using sqlite as a data storage backend for my log parsing application. > I have around 7 milion - equals to 1GB of binary log (up to 35 mln.) > records to insert at once, I'm using prepared statments, huge > transactions, and optimised (I hope) pragma settings: PRAGMA > journal_mode = OFF; PRAGMA cache_size = 50000; PRAGMA temp_store = > MEMORY.
I don't see anything wrong with those, but i'm not an expert. > But still inserting the whole dataset takes more than 8 > minutes :( . [snip] If you have a record number, or any other integer suitable for using as a unique integer primary key, make sure you define it as one. This will save some overhead. Take a good look at which columns you define. Do you need them all ? Could you combine any ? Does your table have any indexes ? It is sometimes faster to drop all indexes, do the inserts, then create the indexes again. The final step will take a long time, but the total time may be less. > BTW. Inserting smaller dataset, like 250MB =~ 2mln records., takes > only 20 seconds. Why is the difference so huge? I would guess that many aspects of that small dataset would fit in memory or cache. Can you tell whether the slowdown is sudden on some magic number of records, or just a gradual process as the dataset grows bigger ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
