Delete Statement in SQL Is Very Slow

Delete statement in SQL is very slow

Things that can cause a delete to be slow:

• deleting a lot of records
• many indexes
• missing indexes on foreign keys in child tables. (thank you to @CesarAlvaradoDiaz for mentioning this in the comments)
• deadlocks and blocking
• triggers
• cascade delete (those ten parent records you are deleting could mean
  millions of child records getting deleted)
• Transaction log needing to grow
• Many Foreign keys to check

So your choices are to find out what is blocking and fix it or run the deletes in off hours when they won't be interfering with the normal production load. You can run the delete in batches (useful if you have triggers, cascade delete, or a large number of records). You can drop and recreate the indexes (best if you can do that in off hours too).

Very slow DELETE query

Add a Primary key to your table variables and watch them scream

DECLARE @IdList1 TABLE(Id INT primary Key not null)
DECLARE @IdList2 TABLE(Id INT primary Key not null)

because there's no index on these table variables, any joins or subqueries must examine on the order of 10,000 times 10,000 = 100,000,000 pairs of values.

SQL Delete a single row by PK is very slow

I dropped and re-created all Foreign Keys with NO cascade delete.
Now the execution plan is using efficient Index Seeks to check RI on all FKs.

Not sure why having cascade delete enabled caused these Index Scans though...

Why delete rows seems to be slow in SQL Server?

This is an apples to oranges comparison, since a DELETE is writing to the transaction log and SELECT is not.

The DISTINCT works by virtue of sorting the output rows in order to find duplicates. This will be made really slow if you are missing an index (for what it's worth 3 seconds seems slow to me for output). Even slower when performing a NOT IN on the same column. This doesn't take into consideration any constraints you might have on that column, which could further slow it down.

You can likely make this faster using GROUP BY.

DELETE 
FROM      [DBName].[dbo].[index] 
WHERE     family NOT IN (
            SELECT      family 
            FROM        [DBName].[dbo].[student]            
            GROUP BY    family) 

Likely faster still using NOT EXISTS instead of NOT IN:

DELETE 
FROM      [DBName].[dbo].[index] AS outer
WHERE     family NOT EXISTS (
            SELECT      family 
            FROM        [DBName].[dbo].[student] AS inner
            WHERE       inner.family = outer.family
            GROUP BY    family) 

Delete statement was very slow in Oracle

There can be many reasons:

• Server load (unlikely because the SELECT is fast)
• Triggers (see here how to list them for a table).
• Foreign keys (List of foreign keys and the tables they reference)
• A lot of data in each row (LOBs, many columns).
• Someone is locking rows in the table that you'd like to delete (or the whole table). See this blog post how to list locks. This discussion might also help.

If the foreign keys are the problem, the usual solution is to add indexes on the foreign column: For each delete, Oracle needs to check whether this would violate a foreign key relation.

---

Related Topics