Re: sqlite-users Digest, Vol 126, Issue 28

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 126, Issue 28

Allen
> If you give the parent column a proper affinity (ie, integer) do you get "happiness making" results?

nope, made no difference


> Date: Wed, 27 Jun 2018 17:47:01 -0600
> From: "Keith Medcalf" <[hidden email]>
> To: "SQLite mailing list" <[hidden email]>
> Subject: Re: [sqlite] insert or replace performance with self
>         "references"    column
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset="utf-8"
>
>
> If you give the parent column a proper affinity (ie, integer) do you get "happiness making" results?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Allen
>>Sent: Wednesday, 27 June, 2018 16:20
>>To: [hidden email]
>>Subject: [sqlite] insert or replace performance with self
>>"references" column
>>
>>I have a table with an additional index and a query:
>>
>>"create table Transactions (Id integer primary key not null, Parent
>>references Transactions(id), Body varchar);"
>>
>>"create index Parent_Index on Transactions (Parent);"
>>
>>EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
>>Body) values (?1, ?2, ?3);
>>23 0 0 SCAN TABLE Transactions
>>43 0 0 SCAN TABLE Transactions
>>
>>The double SCAN TABLE seems to have something to do with both the
>>"references" column and the "or replace" statement.  If I remove
>>either, then the SCAN goes away.
>>
>>Questions:
>>
>>- Is my syntax for the " insert or replace" statement correct, and
>>will it do what I expect (insert a new row with a new auto-generated
>>Id if Id is NULL, insert a new row with the given Id if Id is not
>>NULL
>>and no row exists with that Id, or update the existing row with the
>>given Id if Id is not NULL and a row exists with that Id)?
>>
>>- Is sqlite really doing one or two table scans to perform the
>>"insert
>>or replace" with a "references" self column?
>>
>>- If so, is there a way to eliminate this (other than removing either
>>the "references" or the "or replace")?
>>
>>Thanks much.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 126, Issue 28

Keith Medcalf

Hmmm.

SQLite version 3.25.0 2018-06-21 23:53:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .eqp on
sqlite> create table t(id integer primary key not null, pid references t(id), body varchar);
sqlite> create index t_pid on t (pid);
sqlite> insert into t values (1,null,'body');
sqlite> insert or replace into t values (2,1,'body');
QUERY PLAN
|--SCAN TABLE t USING COVERING INDEX t_pid
`--SCAN TABLE t USING COVERING INDEX t_pid
sqlite> insert or replace into t values (2,1,'body');
QUERY PLAN
|--SCAN TABLE t USING COVERING INDEX t_pid
`--SCAN TABLE t USING COVERING INDEX t_pid

With or without correct affinity and even if you (most uselessly) specify NOT NULL on an INTEGER PRIMARY KEY ...

Different versions of SQLite probably behave differently ... so what version are you using?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Allen
>Sent: Thursday, 28 June, 2018 10:00
>To: [hidden email]
>Subject: Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28
>
>> If you give the parent column a proper affinity (ie, integer) do
>you get "happiness making" results?
>
>nope, made no difference
>
>
>> Date: Wed, 27 Jun 2018 17:47:01 -0600
>> From: "Keith Medcalf" <[hidden email]>
>> To: "SQLite mailing list" <[hidden email]>
>> Subject: Re: [sqlite] insert or replace performance with self
>>         "references"    column
>> Message-ID: <[hidden email]>
>> Content-Type: text/plain; charset="utf-8"
>>
>>
>> If you give the parent column a proper affinity (ie, integer) do
>you get "happiness making" results?
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>>-----Original Message-----
>>>From: sqlite-users [mailto:sqlite-users-
>>>[hidden email]] On Behalf Of Allen
>>>Sent: Wednesday, 27 June, 2018 16:20
>>>To: [hidden email]
>>>Subject: [sqlite] insert or replace performance with self
>>>"references" column
>>>
>>>I have a table with an additional index and a query:
>>>
>>>"create table Transactions (Id integer primary key not null, Parent
>>>references Transactions(id), Body varchar);"
>>>
>>>"create index Parent_Index on Transactions (Parent);"
>>>
>>>EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent,
>>>Body) values (?1, ?2, ?3);
>>>23 0 0 SCAN TABLE Transactions
>>>43 0 0 SCAN TABLE Transactions
>>>
>>>The double SCAN TABLE seems to have something to do with both the
>>>"references" column and the "or replace" statement.  If I remove
>>>either, then the SCAN goes away.
>>>
>>>Questions:
>>>
>>>- Is my syntax for the " insert or replace" statement correct, and
>>>will it do what I expect (insert a new row with a new auto-
>generated
>>>Id if Id is NULL, insert a new row with the given Id if Id is not
>>>NULL
>>>and no row exists with that Id, or update the existing row with the
>>>given Id if Id is not NULL and a row exists with that Id)?
>>>
>>>- Is sqlite really doing one or two table scans to perform the
>>>"insert
>>>or replace" with a "references" self column?
>>>
>>>- If so, is there a way to eliminate this (other than removing
>either
>>>the "references" or the "or replace")?
>>>
>>>Thanks much.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users