SimonH (7/6/2015)
Hi I have a table dbo.CustomerServices with over 10 Million RowsI have none unique Indexes on CustomerID and ServiceID
I've also created a unique Index across both columns.
My delete statement is as follows.
DELETE FROM dbo.CustomerService
WHERE CustomerID =7 and ServiceID =21.
This is currently taking 27 seconds to execute, deleting approx. 75,000 rows.
The main over head being the index seeks on CustomerID and ServiceID.
I can improve performance by performing a soft delete (Updating a bit column(isdeleted)) and then purging the records at a more convenient time. This runs in a second but is not ideal.
It would involve changing a lot of code adding predicates to account for the isdeleted=0.
Does anyone have any other solutions?
Has anyone else noticed that there's a statement here that's contradictory to other statements? The poster indicates he "also created a unique Index across both columns", and I have to assume from context that he/she means CustomerID and ServiceID. IF that is true, then how can there be 75,000 records for any combination of values for those two fields ? A Unique Index would fail on non-compliant data, wouldn't it ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)