Speed up deletions on SQL Server 2005?

Question

Most questions I've seen on similar subjects relate to how to speed up inserts into a table.

I want to know what can I do to speed up deletes?

I can't truncate nor drop a table, I have to do something similar to

DELETE FROM table WHERE id IN (SELECT id FROM other_table)

Answer 1

Does "table" have an index on the "id" column?
If not, SQL Server will probably do a table scan to find the records to delete. You can speed this up by creating an index on the "id" column.

Same with "other_table" - if the selection on that table has a WHERE clause as well, check if the columns used there are indexed.

Answer 2

There is no specfic help for your case, because it depends.

General rules (if applicable):

1. Have a nonclustered index for table.id
2. But remove all other keys/indexies never or seldom used
3. Remove all other keys/indexies before delete operation and rebuild them afterwards
4. Avoid a custered index for table.id
5. Speed up "SELECT id FROM other_table" by using an index (assuming this sub-select has any kind of where clause in real world)

Answer 3

Delete in smaller batches, like 1000 records at a time. The actual delete will take a little longer (make sure to have the needed indexes in place) but the users won't be locked out of the table while the long running delete is running.

Answer 4

Some general ideas to follow.

• Make sure you aren't deleting from a column that has a clustered index.
• Make sure that other columns in the row have keys or an index.
• Make sure that you aren't selecting a large amount of records in your select portion.

Other than that it's just a subjective guess as to what is causing your slow down. The best thing to do is to try things and see what helps and what makes it slower, to understand how to items in the row are being found when the delete is called and to see what is taking the longest there.

Answer 5

• Batch it
• Use EXISTS
• Ensure you have correct indexes on id in both tables

Example:

SELECT 'Starting'
WHILE ROWCOUNT <> 0
    DELETE TOP (1000000) T
    FROM  table T
    WHERE EXISTS (
        SELECT * from otherTable OT WHERE T.id = OT.id
    )

Some inspiration perhaps: Bulk DELETE on SQL Server 2008 and a 7 billion row delete