How to rewrite slow proc with deletes

Posted on

Question :

How should this be re-written/optimized to speed up? On avg deletes 80 million rows. No FK references. Cannot use move data to another table and truncate methods.

  • SQL Server 2012 Stnd
  • CPU 8 Cores
  • Memory 26gb
create PROCEDURE [dbo].[slow_proc]
(
@timestart      DATETIME,
@timeend        DATETIME,
@ID_record      UNIQUEIDENTIFIER
)

AS

SELECT @timeend = DATEADD(d, 1, @timeend)

IF(@ID_record IS NULL)
BEGIN

DELETE FROM t_table1 WITH (NOLOCK)
WHERE     (begintime >= @timestart) AND
(begintime < @timeend)

END
ELSE
BEGIN

DELETE FROM t_table1 WITH (NOLOCK)
WHERE     (begintime >= @timestart) AND (begintime < @timeend) AND (ID = @ID_record)

DELETE FROM t_table1 WITH (NOLOCK)
WHERE     (begintime >= @timestart) AND (begintime < @timeend) AND (ID IS NULL OR ID = '1')

END

GO

—————Info—————-

  • One clustered Index on “t_id” column / PK_t_id
  • No triggers

Answer :

Please see below for what I would do to tweak the performance of this process.

  • You don’t need the NOLOCK hints on the table, you are deleting records, you WILL take a lock.
  • Delete in batches (I usually start at 5K per cycle). This avoids escalation to a table lock.
  • Avoid OR clauses (see where I added an explicit delete for ID=’1′)
  • You really need a non-clustered index on begintime otherwise the @ID_record IS NULL branch will always be slow.
CREATE PROCEDURE [dbo].[slow_proc]
        (
        @timestart      DATETIME,
        @timeend        DATETIME,
        @ID_record      UNIQUEIDENTIFIER
        )
    AS
    BEGIN
    
        DECLARE @BatchSize INT = 5000   /** 5000 is a good number, avoids table lock escalation most of the time **/
        DECLARE @RowCount INT = 1       /** Settting to 1 initially so we always enter the loop at least once. **/
    
        SELECT @timeend = DATEADD(d, 1, @timeend)
    
        WHILE COALESCE(@RowCount, 0) >= 1
        BEGIN
            
            IF(@ID_record IS NULL)
            BEGIN
    
                /** If @ID_record is NULL, then delete all records within timeframe
                    */
    
                DELETE TOP (@BatchSize) FROM t_table1 
                WHERE     (begintime >= @timestart) AND
                (begintime < @timeend)
    
                SET @RowCount = @@ROWCOUNT
    
            END
            ELSE
            BEGIN
    
                /** If @ID_records is not NULL, then delete that specific record within timeframe
                    , make sure to get all records that have ID NULL or '1'
                    **/
                DELETE TOP (@BatchSize) FROM t_table1 
                WHERE     (begintime >= @timestart) AND (begintime < @timeend) AND (ID = @ID_record)
    
                SET @RowCount = @@RowCount 
    
                DELETE TOP (@BatchSize) FROM t_table1 
                WHERE     (begintime >= @timestart) AND (begintime < @timeend) AND (ID IS NULL)
    
                SET @RowCount = COALESCE(@RowCount, 0) + @@ROWCOUNT
    
                DELETE TOP (@BatchSize) FROM t_table1 
                WHERE     (begintime >= @timestart) AND (begintime < @timeend) AND (ID = '1')
    
                SET @RowCount = COALESCE(@RowCount, 0) + @@ROWCOUNT
    
            END
    
            PRINT 'Removed ' + CONVERT(NVARCHAR(20, COALESCE(@RowCount, 0)))
    
        END
    
    END

Leave a Reply

Your email address will not be published. Required fields are marked *