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 p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Goto 0 18 0 00
2 OpenWrite 0 2 0 1 00
3 OpenWrite 1 3 0 keyinfo(1,BINARY) 00
4 NewRowid 0 3 0 00
5 String8 0 4 0 String string string2 00
6 SCopy 4 5 0 00
7 SCopy 3 6 0 00
8 MakeRecord 5 2 1 ab 00
9 SCopy 3 7 0 00
10 IsUnique 1 12 7 5 00
11 Halt 19 2 0 column t is not unique 00
12 IdxInsert 1 1 0 10
13 MakeRecord 4 1 7 a 00
14 Insert 0 7 3 a 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 a 00
21 Goto 0 2 0 00
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: [email protected] [[email protected]] on
behalf of Fabian [[email protected]]
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 <[email protected]>
>
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
