Re: [sqlite] Slow inserts with UNIQUE

[Fabian]

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

[Black, Michael (IS)]

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

[Black, Michael (IS)]

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

[Fabian]

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

[Simon Slavin]

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