Query executes fast, but keeps the table locked for some time

foreign keysqlite

Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table.

It creates a table with a foreign key constraint and inserts 8000 random values, with the foreign key always NULL.

The 8000 rows are inserted very quickly, and it is possible to query them very quickly.

But it's impossible to drop the table for a few seconds.

PRAGMA foreign_keys = 0;

DROP TABLE IF EXISTS Drawings;

CREATE TABLE Drawings ( 
    PartNumber       TEXT,
    Description      TEXT NOT NULL,
    ParentPartNumber TEXT,
    FOREIGN KEY ( ParentPartNumber ) REFERENCES Drawings ( PartNumber ) 
);

CREATE UNIQUE INDEX PartNumber_Idx ON Drawings ( 
    PartNumber 
);

INSERT INTO Drawings ( 
    PartNumber,
    Description 
) 
  WITH RECURSIVE
    cte(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 8000)
  SELECT x, random() FROM cte;

PRAGMA foreign_keys = 1;

SELECT *
  FROM Drawings;

--DROP TABLE IF EXISTS Drawings;

EDIT

I just found out that disabling the foreign key check while dropping the table speeds up the operation:

PRAGMA foreign_keys = 0;
DROP TABLE IF EXISTS Drawings;
PRAGMA foreign_keys = 1;

The question is still open: why dropping a table (or performing other operations that I haven't identified yet) cannot be done immediately after populating the table above, but it can be done after leaving the database alone for a few seconds or after disabling the foreign key check?

Best Answer

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 [some query]. If this SELECT 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. 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.

You should create an index on Drawings(ParentPartNumber).

Related Question