Can dropping and recreating an index speed up a SQL delete?

Question

I'm performing some large date-based delete queries on tables with indexes on fields other than date. I've been told that dropping those indexes, performing the delete, and then re-building the index could be faster than just running the delete against the indexed table. Is this the case?

I can see why the actual removing of records would be faster without an index, but I feel like the act of having to rebuild it later would negate any speed advantage earned.

Answer 1

When making the decision to remove the indexes first, you have to compare the cost (or time) to maintain the index during a delete with the cost to recreate the index after the delete. Two of the most influential factors are number of rows in the table and number or rows being deleted.

When the row count is small or the the row count to delete is small, you won't benefit by removing the indexes first. But, if you have a large table and many rows to delete, the cost of updating the index increases until you reach a point where it's more costly to maintain the index than to delete and recreate the index.

Since you indicate that you're doing multiple large deletes, my hunch is that you have a situation where removing and recreating indexes will help.