Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?
In *Test #1*, we have a
CREATE TABLE TESTMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON TESTMARY(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 TESTMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON TESTMARY(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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
