Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Answered: Bulk Delete of records

    I have a large database, 157 million records each week I purge older records. Around 14 million. I have tried several different methods for deleting the records but it seems to be taking an excessive amount of time to complete. I assume my delete is not written correctly. BTW I am recording the deleted records to a stats file for auditing. I then use the file created by that process to get a list of records to delete. everything down to the delete only takes 10 minutes, the delete has been running for 4 hours and still going. Here is what I have.

    Code:
    /* ************************************** */
    /* Update record in Trace Return Stats    */
    /* ************************************** */
    /* Select Trace Returns drop counts       */
    /* ************************************** */
    IF OBJECT_ID('tempdb.dbo.#TempTable1') IS NOT NULL
    	DROP TABLE #TempTable;
    	
    SELECT a.* INTO #TempTable1 
    FROM 
    (
    SELECT FileName, TR_SerNum
    FROM IMBTracing.dbo.Trace_Return_Table  
    WHERE FileType = 'CT1' AND NOT EXISTS 
    	(
    	SELECT 1 
    	FROM IMBTracing.dbo.CT1_Mail_Data 
    	WHERE TR_SerNum = HT_SerNum
    	) 
    ) as a	
    
    IF OBJECT_ID('tempdb.dbo.#ControlTable') IS NOT NULL
    	DROP TABLE #ControlTable;
    
    /* Get file counts                     */
    SELECT FileName, Delete_Cnt
    INTO #ControlTable
    FROM
    	(
    	SELECT FileName, COUNT(TR_SerNum) as Delete_Cnt
    	FROM dbo.#TempTable1
    	GROUP BY FileName
    	) as a
    	
    SET @STRMSG = N'Get Trace Delete Counts ' 
    	+ RTRIM(CAST(GETDATE() AS nvarchar(30)));  
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT	
    
    /* Update record in Trace Return Stats    */
    UPDATE Trace_Return_Stats 
    	SET Aged_Records = Aged_Records + Delete_Cnt
    FROM Trace_Return_Stats t1 INNER JOIN #ControlTable t2 
    ON t1.FileName = t2.FileName
    
    SET @STRMSG = N'Update Trace Stats ' 
    	+ RTRIM(CAST(GETDATE() AS nvarchar(30)))  
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT	
    
    IF OBJECT_ID('tempdb.dbo.#ControlTable') IS NOT NULL
    	DROP TABLE #ControlTable;
    
    IF OBJECT_ID('tempdb.dbo.#ControlTable2') IS NOT NULL
    	DROP TABLE #ControlTable2;
    
    /* ************************************** */
    /* Delete record from Trace Return Table  */
    /* ************************************** */
    SELECT DISTINCT TR_SerNum 
    INTO #ControlTable2
    FROM #TempTable1
    
    SET @r = 1;
    WHILE @r > 0
    	BEGIN
    	  BEGIN TRANSACTION;
    		BEGIN TRY
    			
    			BEGIN TRANSACTION;
    			DELETE TOP (100000)
    				FROM IMBTracing.dbo.Trace_Return_Table  
    			WHERE FileType = 'CT1' 
    			AND NOT EXISTS 
    				(
    				SELECT 1 
    				FROM #ControlTable2 t2
    				WHERE TR_SerNum = t2.TR_SerNum
    				);
    				
    			SET @r = @@ROWCOUNT;	
    			COMMIT;	
    			CHECKPOINT;
    
    			SET @STRMSG =  N'Delete Trace Records 100000' + N' ' 
    				+ RTRIM(CAST(GETDATE() AS nvarchar(30)))  		
    		END TRY
    		BEGIN CATCH
    			SET @STRMSG = (SELECT ERROR_MESSAGE())
    			RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
    			IF @@TRANCOUNT>0 ROLLBACK
    		END CATCH    SET @r = 1;
    	END
    	
    SET @STRMSG = N'Delete Trace Returns Complete' 
    	+ RTRIM(CAST(GETDATE() AS nvarchar(30)))  
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

  2. Best Answer
    Posted by wjburke2

    "Say what you will about NOT IN but this query only took 24 minutes
    USE IMBTracing;
    GO

    DECLARE @r INT;
    DECLARE @STRMSG varchar(80);

    SET @STRMSG = N'Cleanup for CT1 Trace records older than '
    + N' Started ' + RTRIM(CAST(GETDATE() AS nvarchar(30)))
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

    /* ************************************** */
    /* Delete record from Trace Return Table */
    /* ************************************** */
    SET @r = 1;
    WHILE @r > 0
    BEGIN TRY
    BEGIN TRANSACTION;

    DELETE TOP (100000)
    FROM IMBTracing.dbo.Trace_Return_Table
    WHERE FileType = 'CT1' AND TR_SerNum NOT IN
    (SELECT HT_SerNum FROM CT1_Mail_Data);

    SET @r = @@ROWCOUNT;
    COMMIT;
    CHECKPOINT;
    SET @STRMSG = N'Deleted 100000 Rows' + N' '
    + RTRIM(CAST(GETDATE() AS nvarchar(30)));
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

    END TRY
    BEGIN CATCH
    SET @STRMSG = (SELECT ERROR_MESSAGE())
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
    IF @@TRANCOUNT>0 ROLLBACK
    END CATCH

    SET @STRMSG = N'Delete Trace Returns '
    + RTRIM(CAST(GETDATE() AS nvarchar(10)))
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
    "


  3. #2
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    Say what you will about NOT IN but this query only took 24 minutes
    USE IMBTracing;
    GO

    DECLARE @r INT;
    DECLARE @STRMSG varchar(80);

    SET @STRMSG = N'Cleanup for CT1 Trace records older than '
    + N' Started ' + RTRIM(CAST(GETDATE() AS nvarchar(30)))
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

    /* ************************************** */
    /* Delete record from Trace Return Table */
    /* ************************************** */
    SET @r = 1;
    WHILE @r > 0
    BEGIN TRY
    BEGIN TRANSACTION;

    DELETE TOP (100000)
    FROM IMBTracing.dbo.Trace_Return_Table
    WHERE FileType = 'CT1' AND TR_SerNum NOT IN
    (SELECT HT_SerNum FROM CT1_Mail_Data);

    SET @r = @@ROWCOUNT;
    COMMIT;
    CHECKPOINT;
    SET @STRMSG = N'Deleted 100000 Rows' + N' '
    + RTRIM(CAST(GETDATE() AS nvarchar(30)));
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

    END TRY
    BEGIN CATCH
    SET @STRMSG = (SELECT ERROR_MESSAGE())
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT
    IF @@TRANCOUNT>0 ROLLBACK
    END CATCH

    SET @STRMSG = N'Delete Trace Returns '
    + RTRIM(CAST(GETDATE() AS nvarchar(10)))
    RAISERROR (@STRMSG, 0, 1) WITH NOWAIT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •