Issue:
Single row delete should not take 2 minutes to complete
Cause:
Looking at execution plan, a lot of processing on a different table with foreign key link to updating table.
Foreign keys are constraints in the database to ensure that data values used in child table entry are present in a parent, reference table. This helps enforce the integrity we desire, and generally assume to be, in the data. The presence of foreign keys also has an often overlooked impact to deletions made against the parent table. When a row is deleted from the parent table, the database needs to do a check against the child table to ensure that the row being deleted is not referenced by the child table. When the foreign key column(s) in the child table are indexed then this check against the child table is quick and generally not noticeable. If the foreign key column(s) are not indexed then the database has no choice but to resort to a full table scan of the child table… and if the child table is fairly large then that check may take a while to complete, holding up the delete.
It contained an unindexed foreign key to the table that the delete was being performed on. The child table contained tens of millions of rows so a table scan would certainly have explained the delay in deleting from the parent table.
Solution:
Drop the foreign key on the child table. No, no, just kidding! The solution was to index the foreign key column in the child table, making sure to specify the ONLINE clause of the CREATE INDEX statement so that the index could be added without blocking activity on the child the table.
Comments