On 3/20/2013 8:29 AM, Frank Chang wrote:
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)
What's the relationship between TESTMARY and FREQMARY? Why do you show the schema of one, when you are concerned with inserts into the other?
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.
Check whether OR REPLACE part actually works in the second case - whether Count gets incremented as expected. I suspect that, with ON CONFLICT IGNORE clause, conflicts may in fact be ignored. Then it works much faster simply because it makes much fewer writes.
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
