Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

2013-03-20 Thread Igor Tandetnik 

On 3/20/2013 9:21 AM, Michael Black wrote:

I can tell you the explain plan is notably different between those two
inserts.  This is with 3.7.14.1
If only I knew more about the details of what the plan meansone thing I
noted is that keyinfo is opened twice for write on Test#2


That's because there are two (redundant) indexes on the same column that 
need to be updated - one explicitly created, and another implicit in 
UNIQUE constraint.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

2013-03-20 Thread Michael Black 
I can tell you the explain plan is notably different between those two
inserts.  This is with 3.7.14.1
If only I knew more about the details of what the plan meansone thing I
noted is that keyinfo is opened twice for write on Test#2
Test 1:
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 45000
2 OpenWrite  1 2 0 5  00
3 OpenWrite  2 3 0 keyinfo(1,BINARY)  00
4 NewRowid   1 2 000
5 String80 3 0 Braintree Road  00
6 Null   0 4 000
7 Null   0 5 000
8 Once   0 25000
9 Null   0 8 000
10Integer1 9 000
11OpenRead   0 2 0 4  00
12OpenRead   3 3 0 keyinfo(1,BINARY)  00
13String80 100 Braintree Road  00
14SeekGe 3 23101  00
15IdxGE  3 23101  01
16IdxRowid   3 11000
17Seek   0 11000
18Column 0 3 11   00
19Integer1 13000
20Add131112   00
21Move   128 100
22IfZero 9 23-1   00
23Close  0 0 000
24Close  3 0 000
25SCopy  8 6 000
26NotNull6 28000
27Integer1 6 000
28Integer147 000
29SCopy  3 14000
30SCopy  2 15000
31MakeRecord 142 1 ad 00
32SCopy  2 11000
33IsUnique   2 391114 00
34NotExists  1 3911   00
35Rowid  1 15000
36Column 1 0 14   00
37IdxDelete  2 14200
38Delete 1 0 000
39IdxInsert  2 1 000
40MakeRecord 3 5 11aeadd  00
41Insert 1 112 FREQMARY   0b
42Close  1 0 000
43Close  2 0 000
44Halt   0 0 000
45Transaction0 1 000
46VerifyCookie   0 4 000
47TableLock  0 2 1 FREQMARY   00
48Goto   0 2 000
Test 2:
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 64000
2 OpenWrite  1 4 0 5  00
3 OpenWrite  2 6 0 keyinfo(1,BINARY)  00
4 OpenWrite  3 5 0 keyinfo(1,BINARY)  00
5 NewRowid   1 3 000
6 String80 4 0 Braintree Road  00
7 Null   0 5 000
8 Null   0 6 000
9 Once   0 26000
10Null   0 9 000
11Integer1 10000
12OpenRead   0 4 0 4  00
13OpenRead   4 6 0 keyinfo(1,BINARY)  00
14String80 110 Braintree Road  00
15SeekGe 4 24111  00
16IdxGE  4 24111  01
17IdxRowid   4 12000
18Seek   0 12000
19Column 0 3 12   00
20Integer1 14000
21Add141213   00
22Move   139 100
23IfZero 1024-1   00
24Close  0 0 000
25Close  4 0 000
26SCopy  9 7 000
27NotNull7 29000
28Integer1 7 000

[sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

2013-03-20 Thread Frank Chang 
Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?

In *Test #1*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

In *Test #1*, we use the following UPSERT:

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

In *Test #2*, we have a

CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)

and a

CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).

--

In *Test #2*, we use the same UPSERT as *Test#1*,

INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)

--

With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.

May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?

Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users