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

2013-03-20 Thread Igor Tandetnik 

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 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.


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

[sqlite] 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 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 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