2011/10/30 Black, Michael (IS) <michael.bla...@ngc.com> > > #1 What version? >
3.7.8, using System.Data.Sqlite wrapper > #2 How long to insert the 1M? > 10 seconds > > #3 What's the average string size? > 55 characters > > #5 How long to create the index? > 10 seconds > #6 How long to insert the next 10,000? > > 34 seconds. But... only 1 second if I do it immediately after filling the database, so it seems to be related wether the file is in the filesystem cache or not. Server-apps will always have the file cached, but this is a desktop-app, where the db will not be in the filesystem cache most of the times. One more thing...show us the EXPLAIN of your insert. Is sounds like your > insert is not using the index for the insert for some reason (buq in > sqlite?). 0 Trace 0 0 0 00 1 Goto 0 18 0 00 2 OpenWrite 0 2 0 1 00 3 OpenWrite 1 14345 0 keyinfo(1,BINARY) 00 4 NewRowid 0 2 0 00 5 String8 0 3 0 test 00 6 SCopy 3 4 0 00 7 SCopy 2 5 0 00 8 MakeRecord 4 2 1 ab 00 9 SCopy 2 6 0 00 10 IsUnique 1 12 6 4 00 11 Goto 0 15 0 00 12 IdxInsert 1 1 0 10 13 MakeRecord 3 1 6 a 00 14 Insert 0 6 2 table 1b 15 Close 0 0 0 00 16 Close 1 0 0 00 17 Halt 0 0 0 00 18 Transaction 0 1 0 00 19 VerifyCookie 0 2 0 00 20 TableLock 0 2 1 table 00 21 Goto 0 2 0 00 The resulting database is about 125MB large. So 34 seconds seems way too long, even if the whole db-file has to be read from disk, and stored into memory, it shouldn't take that long. Pragma's used: PRAGMA page_size = 4096; PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; PRAGMA temp_store = MEMORY; PRAGMA locking_mode = EXCLUSIVE; PRAGMA cache_size = 72500; If you need any more info, let me know! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
