-1

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
7
  • Please update your post's tags with which database system and version you're using.
    – J.D.
    Oct 1 '21 at 11:18
  • Also why can't you move only the data you need to keep to another Table temporarily and use TRUNCATE?
    – J.D.
    Oct 1 '21 at 11:20
  • 1
    Additionally please post your index definitions on t_table1.
    – J.D.
    Oct 1 '21 at 11:32
  • Please consider reading this advice
    – mustaccio
    Oct 1 '21 at 11:55
  • @J.D. Vendor will not approve temp table/truncate method. Thanks for comment.
    – jay
    Oct 1 '21 at 12:08
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.