SQLite: Bad performance with ON DELETE CASCADE

问题:

I am using a SQLite Database in my application for storing measurement data.

It is organized in groups in projects. The tables are interconnected with foreign keys, that use ON DELETE CASCADE to handle the links. The database looks like this:

• Table Projects
• Table Groups (Foreign Key: project_id)
• Table Files (Foreign Key: group_id)
• Table Datapoints (Foreign Key: file_id)

All foreign keys reference columns declared like

project_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT.

The foreign keys are declared like this

FOREIGN KEY(project_id) REFERENCES Projects(project_id) ON DELETE CASCADE

Now I have 1 project, 1 group in the project, 800 files in this group and around 60 datapoints per file.

Deleting the group with DELETE FROM Groups WHERE project_id=1 works well but it takes around 21 seconds, which is way too long for my needs.

I have wrapped the deletion in a Transaction.

I am an extreme novice in SQL and SQLite. Is this duration normal or is there any way to speed it up? I need to delete the group to fill in updated values. In my application I only want to keep one project in memory, so just deleting the whole database and filling it from scratch (though much faster, ~1 sek) is not really an option.

The tables are created like this:

'Create projects table
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Projects ( ProjectID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " & _
        "name TEXT NOT NULL, comment TEXT, date DATETIME2, diameter FLOAT(53), thickness FLOAT(53) );"

'Create Groups table
cmd.CommandText &= vbNewline & "CREATE TABLE IF NOT EXISTS Groups ( GroupID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, " & _
        "name TEXT NOT NULL, text TEXT NOT NULL, fixed TINYINT NOT NULL, " & _
        "FOREIGN KEY(project_id) REFERENCES Projects(ProjectID) ON DELETE CASCADE );"

回答1:

The documentation says:

Indices are not required for child key columns but they are almost always beneficial. […]

Each time an application deletes a row from the ... parent table, it performs [a query] to search for referencing rows in the ... child table.

If this query returns any rows at all, then SQLite concludes that deleting the row from the parent table would violate the foreign key constraint and returns an error. Similar queries may be run if the content of the parent key is modified or a new row is inserted into the parent table. If these queries cannot use an index, they are forced to do a linear scan of the entire child table. In a non-trivial database, this may be prohibitively expensive.

So, in most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index.

So you should create indexes on the child key columns:

CREATE INDEX Groups_project_id_index ON Groups(project_id);