如何用刪除重寫慢速過程

這應該如何重寫/優化以加快速度？On avg 刪除 8000 萬行。沒有 FK 參考。不能使用將數據移動到另一個表和截斷方法。

• SQL Server 2012 標準
• CPU 8 核
• 記憶體 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

• • • • • • • -資訊 - - - - - - - -

• “t_id”列/PK_t_id 上的一個聚集索引
• 沒有觸發器

請參閱下文，了解我將如何調整此過程的性能。

• 您不需要表上的 NOLOCK 提示，您正在刪除記錄，您將獲得鎖。
• 批量刪除（我通常從每個週期 5K 開始）。這避免了升級到表鎖。
• 避免使用 OR 子句（請參閱我為 ID=‘1’ 添加顯式刪除的位置）
• 您確實需要在 begintime 上使用非聚集索引，否則 @ID_record IS NULL 分支將總是很慢。

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

引用自：https://dba.stackexchange.com/questions/300444