Re: [sqlite] Slow inserts with UNIQUE
2011/10/30 Black, Michael (IS)> > #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
Re: [sqlite] Slow inserts with UNIQUE
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?). You should see #3 in particular for keyinfo(). On 3.7.5 with a unique text column I get this for an insert: sqlite> explain insert into a values('String string string2'); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 18000 2 OpenWrite 0 2 0 1 00 3 OpenWrite 1 3 0 keyinfo(1,BINARY) 00 4 NewRowid 0 3 000 5 String80 4 0 String string string2 00 6 SCopy 4 5 000 7 SCopy 3 6 000 8 MakeRecord 5 2 1 ab 00 9 SCopy 3 7 000 10IsUnique 1 127 5 00 11Halt 192 0 column t is not unique 00 12IdxInsert 1 1 010 13MakeRecord 4 1 7 a 00 14Insert 0 7 3 a 1b 15Close 0 0 000 16Close 1 0 000 17Halt 0 0 000 18Transaction0 1 000 19VerifyCookie 0 2 000 20TableLock 0 2 1 a 00 21Goto 0 2 000 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Fabian [fabianpi...@gmail.com] Sent: Saturday, October 29, 2011 12:11 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Slow inserts with UNIQUE 2011/10/29 Simon Slavin <slav...@bigfraud.org> > > When you insert the 10,000 strings are you doing it inside a transaction ? > > BEGIN TRANSACTION; > INSERT ... > INSERT ... > INSERT ... > COMMIT; > > Yes, I use transactions, prepared statements, cache_size, journal_mode and synchronous PRAGMA's, almost everything you can think of to make it faster. But most important: I use the exact same code to fill the initial million rows, so if there was anything wrong, the initial filling would be slow too I suppose? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow inserts with UNIQUE
You need to provide some more info... #1 What version? #2 How long to insert the 1M? #3 What's the average string size? #5 How long to create the index? #6 How long to insert the next 10,000? And your stated problem is simple enough you should be able to create a complete stand-alone example so we can all see and test what you're (presumably) doing wrong. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Fabian [fabianpi...@gmail.com] Sent: Saturday, October 29, 2011 12:11 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Slow inserts with UNIQUE 2011/10/29 Simon Slavin <slav...@bigfraud.org> > > When you insert the 10,000 strings are you doing it inside a transaction ? > > BEGIN TRANSACTION; > INSERT ... > INSERT ... > INSERT ... > COMMIT; > > Yes, I use transactions, prepared statements, cache_size, journal_mode and synchronous PRAGMA's, almost everything you can think of to make it faster. But most important: I use the exact same code to fill the initial million rows, so if there was anything wrong, the initial filling would be slow too I suppose? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow inserts with UNIQUE
2011/10/29 Simon Slavin> > When you insert the 10,000 strings are you doing it inside a transaction ? > > BEGIN TRANSACTION; > INSERT ... > INSERT ... > INSERT ... > COMMIT; > > Yes, I use transactions, prepared statements, cache_size, journal_mode and synchronous PRAGMA's, almost everything you can think of to make it faster. But most important: I use the exact same code to fill the initial million rows, so if there was anything wrong, the initial filling would be slow too I suppose? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow inserts with UNIQUE
On 29 Oct 2011, at 5:57pm, Fabian wrote: > I have a table with one TEXT column. I insert 1 million rows of short > strings, and then create an UNIQUE INDEX. The speed is very acceptable. Then > I insert another 10.000 short strings, and the performance is very bad, it > almosts take longer than inserting the initial million(!) rows to fill the > table. When you insert the 10,000 strings are you doing it inside a transaction ? BEGIN TRANSACTION; INSERT ... INSERT ... INSERT ... COMMIT; Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow inserts with UNIQUE
I have a table with one TEXT column. I insert 1 million rows of short strings, and then create an UNIQUE INDEX. The speed is very acceptable. Then I insert another 10.000 short strings, and the performance is very bad, it almosts take longer than inserting the initial million(!) rows to fill the table. Is this by design, or what can be causing this? I also observed that inserting the additional 10.000 rows goes progressively faster when nearing the end, I suppose because this is because at that time the index is completely in cache. But what is the reason for the bad performance of these inserts? I read things about b-tree page splitting, could it be related? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users