On 01/11/2011 03:00 PM, Oliver Peters wrote: > Hello, > > I'm on WinXP and using sqlite 3.7.4 with the CLI. I try to insert ~ 10,100,000 > records into a schema with different tables (http://pastebin.com/cbsPHNEj). > The > db file has already 1.9 GB when I start the INSERTs via > > sqlite3 -bail extra.db3< inserts.sql > > The statements in inserts.sql look like > > PRAGMA foreign_keys = ON; > ... > PRAGMA foreign_keys = OFF; > > Most INSERTS are done into the table Verteilerdaten (>10,000,000). I think the > time depends heavily on the activated FOREIGN KEYs - is my assumption correct > and is this a behaviour I only can avoid by not switching this PRAGMA on?
It could be correct. Once a database gets large enough, the speed of INSERT statements tends to be limited by seeking around the file to read data. Particularly if your transactions are also large. If you insert a row into a table that has a foreign key constraint, it has to search for the corresponding key in the parent table. So if the parent table is also large (too large for the cache), those searches might be slowing you down significantly. So if you can get away with doing the inserts with foreign keys turned off, it is worth trying. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
