Re: [sqlite] DELETE extremely slow

[Thomas Kurz]

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