insert or replace performance with self "references" column

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

insert or replace performance with self "references" column

Allen
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: insert or replace performance with self "references" column

Keith Medcalf

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
Reply | Threaded
Open this post in threaded view
|

Re: insert or replace performance with self "references" column

Donald Griggs
On Wed, Jun 27, 2018, 7:47 PM Keith Medcalf <[hidden email]> wrote:

>
> 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
>
_______________________________________________
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: insert or replace performance with self "references" column

Clemens Ladisch
In reply to this post by Allen
Allen wrote:
> 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
>
> - Is sqlite really doing one or two table scans to perform the "insert
> or replace" with a "references" self column?

It does the two scans for the foreign key constraint processing.
(The rowid processing is not mentioned in the EQP output.)

> - If so, is there a way to eliminate this (other than removing either
> the "references" or the "or replace")?

As mentioned by Keith, the index is not used because of the wrong
affinity of the Parent column.  With "Parent integer", both scans
are efficient.


Regars,
Clemens
_______________________________________________
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: insert or replace performance with self "references" column

Simon Slavin-3
In reply to this post by Allen
On 27 Jun 2018, at 11:20pm, Allen <[hidden email]> wrote:

> 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

SQLite version 3.22.0 2017-12-05 15:00:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY,
   ...> parent REFERENCES Transactions(id),
   ...> Body TEXT);
sqlite> create index Parent_Index on Transactions (Parent);
sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values (3, 4, 'Hello');          
sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, Body) values (3, 4, 'Hello');
0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index
0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index

If you replace "not null' from your CREATE TABLE command, do you get the result I do ?
What, specifically, INSERT OR REPLACE command did you use ?
Which version of the command-line shell tool did you use ?

Simon.
_______________________________________________
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: insert or replace performance with self "references" column

Allen
In reply to this post by Allen
Thank you for your assistance. I got the same results as you using the
shell, and was only getting the problem in my C program.

My C program was creating a "Parent_Index" on two different tables, so
the second Parent_Index on the Transactions table was not being
created, and this was causing the "replace" to do a table scan.  I
solved the problem by making the index names globally unique.

Thanks again.


> Date: Thu, 28 Jun 2018 18:09:09 +0100
> From: Simon Slavin <[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=us-ascii
>
> On 27 Jun 2018, at 11:20pm, Allen <[hidden email]> wrote:
>
>> 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
>
> SQLite version 3.22.0 2017-12-05 15:00:17
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> PRAGMA foreign_keys = ON;
> sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY,
>    ...> parent REFERENCES Transactions(id),
>    ...> Body TEXT);
> sqlite> create index Parent_Index on Transactions (Parent);
> sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values (3, 4, 'Hello');
> sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, Body) values (3, 4, 'Hello');
> 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index
> 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index
>
> If you replace "not null' from your CREATE TABLE command, do you get the result I do ?
> What, specifically, INSERT OR REPLACE command did you use ?
> Which version of the command-line shell tool did you use ?
>
> Simon.
_______________________________________________
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: insert or replace performance with self "references" column

Simon Slavin-3
On 2 Jul 2018, at 2:45pm, Allen <[hidden email]> wrote:

> My C program was creating a "Parent_Index" on two different tables, so
> the second Parent_Index on the Transactions table was not being
> created, and this was causing the "replace" to do a table scan.  I
> solved the problem by making the index names globally unique.

Easy mistake to make.  Glad you figured it out.

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