Delete Takes a Long Time

Question

I've got a table which has about 5.5 million records. I need to delete some records from it based on date. My query looks like this:

DELETE FROM Table WHERE [Date] between '2011-10-31 04:30:23' and '2011-11-01 04:30:42'

It's about 9000 rows, but this operation last very long time. How can I speed it up? Date is type of datetime2, table has int primary key clustered. Update and delete triggers are disabled.

Answer 1

It's very possible that [Date] is being cast to a string on every row resulting in a sequential scan of the entire table.

You should try casting your parameters to a date instead:

DELETE FROM Table WHERE [Date] between convert(datetime, '2011-10-31 04:30:23') and convert(datetime, '2011-11-01 04:30:42')

Also, make sure there's an index on [Date]