SQL performance on many deletes

Question

I have a temp table full of GUIDs ##TempCleanseTheseCustomers.

I want to delete several related tables to a customer table. Around 30 related tables. Here is some examples:

Delete from AddressHistory
Where CustomerId in (select CustomerId From ##TempCleanseTheseCustomers)

Delete from Address
Where CustomerId in (select CustomerId From ##TempCleanseTheseCustomers)


DECLARE @MessageMessageId uniqueidentifier
set @MessageMessageId = 
(
SELECT        TOP (1)  Message.MessageId
FROM            Customer INNER JOIN
                     CustomerMessage ON Customer.CustomerId = 
CustomerMessage.CustomerId INNER JOIN
                     Message ON CustomerMessage.MessageId = 
Message.MessageId
Where Customer.CustomerId in (select CustomerId From 
##TempCleanseTheseCustomers)
)


Delete from MessageAttachment
Where MessageId = @MessageMessageId

Delete from Message
Where MessageId = @MessageMessageId

How can make this run as fast as possible? (##TempCleanseTheseCustomers contains over 2 million rows)

I have tried moving the table to memory. Running it via C# code etc.

Make sure your deletes aren't being blocked by other statements and the deleting tables have index on CustomerId (clustered preferably). — EzLo, Apr 12 '18 at 12:02
not for optimization tips, but you do not have `Order by` with `Top`? just a random value? — LONG, Apr 12 '18 at 12:05
Do it in reasonable sized bulks. I.e. not millions of rows per delete, nor just one. — jarlh, Apr 12 '18 at 12:25
Here are some ways to do that: https://stackoverflow.com/questions/24213299/how-to-delete-large-data-of-table-in-sql-without-log — StephaneM, Apr 12 '18 at 12:25
@Per G please listen to StephaneM https://stackoverflow.com/questions/24213299/how-to-delete-large-data-of-table-in-sql-without-log — MichaelEvanchik, Apr 12 '18 at 14:57

score 0 · Answer 1 · answered Apr 12 '18 at 14:25

0

May be you can check that there are not duplicated records on ##TempCleanseTheseCustomers and check the existence of indexes and keys on the other several tables

answered Apr 12 '18 at 14:25

Eduardo Chávez

48
7

SQL performance on many deletes

1 Answers1