2

I know this has been asked few times in here, but my case is bit different. I would like to purge the data in from my SQL Server database of 700 GB and almost 1000 tables.

I spent fair amount of time preparing delete scripts which delete all the data for a subscriber. My problem is, this takes a huge amount of time to delete and lots of application timeouts when delete statements run. Time is not an issue here but only the concern is an application timeout.

What would be the best approach/technique to purge the SQL tables?

declare @subid int = 72
IF  EXISTS (SELECT 1 FROM Table1
    WHERE  subId =  @subid and UnsubscribeDate IS NOT NULL)
                                                                          
                   CREATE TABLE #ClientTable 
                   (
                                   ClientGuid uniqueidentifier
                                   ,BusinessGuid uniqueidentifier
                   )

                   INSERT INTO #ClientTable
                   SELECT top 10 c.ClientGuid, cb.ClientBusinessGuid from table2 c 
                   RIGHT JOIN table3 cb on c.clientBusinessGuid = cb.clientBusinessGuid 
                    WHERE  cb.subId = @subId
                   AND IsSelf = 'N' 
                   
                   
                   INSERT INTO DBA_CONFIG.DBO.Datapurge_Clientguid_ClientBusinessGuid(CLIENTGUID,BUSINESSGUID)SELECT * FROM #ClientTable
                   
                  CREATE TABLE #OpportunityIdTable 
                  (
                     OpportunityId int
                  )

                  INSERT INTO #OpportunityIdTable
                  SELECT OpportunityId FROM table4 WHERE  ClientGuid IN (SELECT ClientGuid FROM #ClientTable)

                  CREATE TABLE #BroadcastContactIdTable  
                  (
                  BroadcastContactId int
                  )

                  INSERT INTO #BroadcastContactIdTable
                  SELECT BroadcastContactId FROM table5 WHERE  ClientGuid IN (SELECT ClientGuid FROM #ClientTable)

                 
                   
                  DELETE FROM Table6  WHERE  ClientGuid IN (SELECT ClientGuid FROM #ClientTable)
                   DELETE FROM Table7  WHERE  ClientGuid IN (SELECT ClientGuid FROM #ClientTable)
                   DELETE FROM Table8  WHERE  BroadcastContactId IN (SELECT BroadcastContactId FROM #BroadcastContactIdTable)
                   DELETE FROM Table9  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                   DELETE FROM Table10  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                   --DELETE FROM Table11  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                   DELETE FROM Table12  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                   --DELETE FROM Table13  WHERE  CopiedFromOpportunityId in (SELECT OpportunityId FROM #OpportunityIdTable)
                   DELETE Table14  WHERE  DocumentId IN (SELECT DocumentId FROM MD_Documents WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable))
                  DELETE Table15  WHERE  DocumentId IN (SELECT DocumentId FROM MD_Documents  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable))
                  DELETE Table16  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE Table17  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE Table18  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE Table19  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE Table20  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE Table21  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE Table22  WHERE  OpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  UPDATE Table23 SET MasterOpportunityId=NULL WHERE  MasterOpportunityId IN (SELECT OpportunityId FROM #OpportunityIdTable)
                  DELETE FROM Table24  WHERE  ClientGuid IN (SELECT ClientGuid FROM #ClientTable)

This is a sample query. The actual one has 1000 lines of delete commands. This is actually a stored procedure I call inside a cursor that passes adviserbusinessid as a parameter.

I am deleting just a subset of the data based on the subscriber ID from subscriber table.

8
  • Are there any triggers or on cascade delete for foreign keys?
    – vonPryz
    Commented Mar 6, 2023 at 8:12
  • You’re doing it all in one big transaction. That’s bound to cause problems.
    – RonJohn
    Commented Mar 6, 2023 at 11:44
  • @RonJohn, How can we split it into multiple transaction? Create multiple procedure with small chunk of tables? Commented Mar 6, 2023 at 23:24
  • @vonPryz There are triggers and cascade delete but very few, less than 10.. Commented Mar 6, 2023 at 23:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.