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 means....one thing I noted is that keyinfo is opened twice for write on Test#2 Test 1: addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 45 0 00 2 OpenWrite 1 2 0 5 00 3 OpenWrite 2 3 0 keyinfo(1,BINARY) 00 4 NewRowid 1 2 0 00 5 String8 0 3 0 Braintree Road 00 6 Null 0 4 0 00 7 Null 0 5 0 00 8 Once 0 25 0 00 9 Null 0 8 0 00 10 Integer 1 9 0 00 11 OpenRead 0 2 0 4 00 12 OpenRead 3 3 0 keyinfo(1,BINARY) 00 13 String8 0 10 0 Braintree Road 00 14 SeekGe 3 23 10 1 00 15 IdxGE 3 23 10 1 01 16 IdxRowid 3 11 0 00 17 Seek 0 11 0 00 18 Column 0 3 11 00 19 Integer 1 13 0 00 20 Add 13 11 12 00 21 Move 12 8 1 00 22 IfZero 9 23 -1 00 23 Close 0 0 0 00 24 Close 3 0 0 00 25 SCopy 8 6 0 00 26 NotNull 6 28 0 00 27 Integer 1 6 0 00 28 Integer 14 7 0 00 29 SCopy 3 14 0 00 30 SCopy 2 15 0 00 31 MakeRecord 14 2 1 ad 00 32 SCopy 2 11 0 00 33 IsUnique 2 39 11 14 00 34 NotExists 1 39 11 00 35 Rowid 1 15 0 00 36 Column 1 0 14 00 37 IdxDelete 2 14 2 00 38 Delete 1 0 0 00 39 IdxInsert 2 1 0 00 40 MakeRecord 3 5 11 aeadd 00 41 Insert 1 11 2 FREQMARY 0b 42 Close 1 0 0 00 43 Close 2 0 0 00 44 Halt 0 0 0 00 45 Transaction 0 1 0 00 46 VerifyCookie 0 4 0 00 47 TableLock 0 2 1 FREQMARY 00 48 Goto 0 2 0 00 Test 2: addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 64 0 00 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 0 00 6 String8 0 4 0 Braintree Road 00 7 Null 0 5 0 00 8 Null 0 6 0 00 9 Once 0 26 0 00 10 Null 0 9 0 00 11 Integer 1 10 0 00 12 OpenRead 0 4 0 4 00 13 OpenRead 4 6 0 keyinfo(1,BINARY) 00 14 String8 0 11 0 Braintree Road 00 15 SeekGe 4 24 11 1 00 16 IdxGE 4 24 11 1 01 17 IdxRowid 4 12 0 00 18 Seek 0 12 0 00 19 Column 0 3 12 00 20 Integer 1 14 0 00 21 Add 14 12 13 00 22 Move 13 9 1 00 23 IfZero 10 24 -1 00 24 Close 0 0 0 00 25 Close 4 0 0 00 26 SCopy 9 7 0 00 27 NotNull 7 29 0 00 28 Integer 1 7 0 00 29 Integer 14 8 0 00 30 SCopy 4 15 0 00 31 SCopy 3 16 0 00 32 MakeRecord 15 2 1 ad 00 33 SCopy 3 12 0 00 34 IsUnique 2 43 12 15 00 35 NotExists 1 43 12 00 36 Rowid 1 16 0 00 37 Column 1 0 15 00 38 IdxDelete 2 15 2 00 39 Rowid 1 16 0 00 40 Column 1 0 15 00 41 IdxDelete 3 15 2 00 42 Delete 1 0 0 00 43 SCopy 4 15 0 00 44 SCopy 3 16 0 00 45 MakeRecord 15 2 2 ad 00 46 SCopy 3 12 0 00 47 IsUnique 3 56 12 15 00 48 NotExists 1 56 12 00 49 Rowid 1 16 0 00 50 Column 1 0 15 00 51 IdxDelete 2 15 2 00 52 Rowid 1 16 0 00 53 Column 1 0 15 00 54 IdxDelete 3 15 2 00 55 Delete 1 0 0 00 56 IdxInsert 3 2 0 00 57 IdxInsert 2 1 0 00 58 MakeRecord 4 5 12 aeadd 00 59 Insert 1 12 3 FREQMARY2 0b 60 Close 1 0 0 00 61 Close 2 0 0 00 62 Close 3 0 0 00 63 Halt 0 0 0 00 64 Transaction 0 1 0 00 65 VerifyCookie 0 4 0 00 66 TableLock 0 4 1 FREQMARY2 00 67 Goto 0 2 0 00
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang Sent: Wednesday, March 20, 2013 8:01 AM To: sqlite-users@sqlite.org Cc: m...@melissadata.com Subject: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE? 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 100000 UPSERTS, Test #1 takes 10 hours to complete. With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION every 100000 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
