Re: [sqlite] DELETE extremely slow

[Jean-Luc Hainaut]

Four observations that may (or may not) explain the problem:
- dataset.id declared "unique": useless since the primary key is unique 
by definition but it may create an index (not checked). A PK is not 
"more unique" if an additional "unique" constraint is declared..
- no "on delete <...>" on trace.datasetid; so, default "no action". Is 
it what you want?
- no index on this foreign key, hence potential full scan to identify 
children rows in "trace" (or absence thereof).
- index trace_idx_01 declared twice. Most often once is sufficient, even 
for large tables.

Jean-Luc Hainaut

Yes, please apologize, I indeed forgot to attach the table definitions:

CREATE TABLE dataset (
     id         INTEGER  PRIMARY KEY AUTOINCREMENT
                         UNIQUE
                         NOT NULL,
     name       STRING   DEFAULT NULL
                         COLLATE NOCASE,
     is_latest  BOOLEAN  NOT NULL
                         DEFAULT 1,
     created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE trace (
     id         INTEGER  PRIMARY KEY AUTOINCREMENT
                         UNIQUE
                         NOT NULL,
     name       STRING   DEFAULT NULL
                         COLLATE NOCASE,
     datasetid  INTEGER  REFERENCES dataset (id)
                         NOT NULL,
     quantityid INTEGER  REFERENCES quantity (id)
                         NOT NULL,
     stored     DATETIME DEFAULT NULL,
     created_at DATETIME NOT NULL
                         DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX trace_idx_01 ON trace (
     quantityid
);

CREATE INDEX trace_idx_01 ON trace (
     quantityid
);

CREATE TABLE item (
     id        INTEGER PRIMARY KEY AUTOINCREMENT
                       UNIQUE
                       NOT NULL,
     traceid   INTEGER REFERENCES trace (id)
                       NOT NULL,
     freq      BIGINT  NOT NULL,
     value     REAL    NOT NULL,
     noiseflag BOOLEAN DEFAULT NULL
);

CREATE INDEX item_idx_01 ON item (
     traceid
);

CREATE TABLE metadata (
     id        INTEGER PRIMARY KEY AUTOINCREMENT
                       UNIQUE
                       NOT NULL,
     parameter STRING  NOT NULL
                       COLLATE NOCASE,
     value     STRING  NOT NULL
                       COLLATE NOCASE,
     datasetid INTEGER DEFAULT NULL
                       REFERENCES dataset (id),
     traceid   INTEGER DEFAULT NULL
                       REFERENCES trace (id),
     itemid    INTEGER DEFAULT NULL
                       REFERENCES item (id)
);

CREATE INDEX metadata_idx_01 ON metadata (
     parameter,
     value,
     datasetid,
     traceid,
     itemid
);

CREATE INDEX metadata_idx_02 ON metadata (
     datasetid,
     traceid
);

CREATE INDEX metadata_idx_03 ON metadata (
     traceid
);

CREATE INDEX metadata_idx_04 ON metadata (
     datasetid,
     itemid
);

CREATE INDEX metadata_idx_05 ON metadata (
     traceid,
     itemid
);

CREATE INDEX metadata_idx_06 ON metadata (
     itemid
);

CREATE INDEX metadata_idx_07 ON metadata (
     datasetid,
     parameter
);

CREATE INDEX metadata_idx_08 ON metadata (
     traceid,
     parameter
);

CREATE INDEX metadata_idx_09 ON metadata (
     parameter,
     traceid
);

CREATE INDEX metadata_idx_10 ON metadata (
     parameter,
     datasetid,
     traceid,
     itemid
);

CREATE TABLE quantity (
     id         INTEGER             PRIMARY KEY AUTOINCREMENT
                                    UNIQUE
                                    NOT NULL,
     name       STRING              NOT NULL,
     unit       STRING              NOT NULL,
     sumrule    [SMALLINT UNSIGNED] DEFAULT NULL,
     created_at DATETIME            DEFAULT CURRENT_TIMESTAMP,
     UNIQUE (
         name,
         unit,
         sumrule
     )
     ON CONFLICT IGNORE
);





----- Original Message -----
From: Dominique Devienne <ddevie...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Sent: Thursday, October 31, 2019, 11:06:07
Subject: [sqlite] DELETE extremely slow

On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:

I'm using a database with 5 hierarchically strcutured tables using foreign
keys. The largest table contains about 230'000 entries. My problem is that
deleting in this database is extremely slow:
pragma foreign_keys=on;
pragma journal_mode=wal;
.timer on
delete from dataset;
--> Run Time: real 197993.218 user 53015.593750 sys 54056.546875
I experimentally imported the same data into a MariaDB database and tried
the same operation there (without paying attention to creating any indexes,
etc.). It takes only a few seconds there.
Is there something I can check or do to improve deletion speed?

You're not describing the schema enough IMHO.
Is dataset the "top-most" table, containing the "parent" rows all other
tables references (directly or indirectly),
with all FKs having ON DELETE CASCADE?

If that's the case, without some kind of optimization in SQLite, when the
first parent row is deleted,
it triggers a cascade of deletes in "child" tables, looking for rows using
the parent row. So if your FKs
are not indexed for those column(s), that's a full table scan each time...
That's "depth first".

By analyzing the graph of FKs and their ON DELETE CASCADE state, and in the
specific case of
fully deleting the "main parent table", SQLite could decide switch to a
smarter "breadth first" delete,
but I suspect it's not a compelling enough use-case for Richard to invest
time on this.

Try indexing your FKs, and see what happens. --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users