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