幫助調整此更新查詢或可能的重寫

我有以下 SQL 查詢，它在 avg 上執行。40 秒，需要一些指導，我可以在哪裡改進或重寫：

  Declare @status_D INT,
     Declare @StatusIP INT,
     Declare @Status_F INT,
   
   SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
   SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
   SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

UPdate U
       SET 
        U.[id]= @StatusIP,
        U.[EnabledDate]= Getdate()
        OUTPUT deleted.col1,
        deleted.col2,
        deleted.col3,
        deleted.col4,
        Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

 COMMIT TRAN 

我收到來自 blitzcache 的警告，因為強制序列化、昂貴的排序、不可分割和合併加入 “Mant to Many” = True

我們唯一的索引是在 id 上，它是一個以 id 作為前導鍵列的 NC 索引。

如果可以的話，我會嘗試更新我的問題，如果執行計劃。截至目前，SELECT 中的表約為 1 億。在 col1 上也有 PK/CI。

我已經問過使用者為什麼我們在那個拉近 50 多列但在更新中使用較少的 TOP 中做 *，可能 bi 想錯了，但我想到了。

將索引添加到修改時間似乎也無濟於事。

請建議還有什麼可以檢查的。謝謝

計劃-索引搜尋發生在 NC 索引 id 上

警告

• 強制序列化是因為OUTPUT（沒有目標或@table變數會導致這種情況，但不是#temp表）
• 昂貴的排序是因為您在ModifiedTime沒有支持索引和查詢模式的情況下進行排序
• 非 SARGable 警告是因為（如果我記得我是如何正確編寫檢查的）局部變數。

如何修復它

您需要一個良好的支持索引：

CREATE INDEX super_awesome
ON dbo.BatchChangeTable
   (id, ModifiedTime)
INCLUDE
   (EnabledDate);

但是查詢的編寫方式IN將被解釋為範圍謂詞，並且ORDER BY ModifiedDate可能仍會導致排序操作。為了充分利用索引，您需要更改表單以使用兩個相等謂詞和 a 。UNION ALL

您還需要重寫查詢以使用動態 SQL，以免受到局部變數的副作用的影響。

成品將如下所示：

BEGIN TRAN;

/*I'm guessing here, address local factors*/
CREATE TABLE #output(col1 int, col2 int, col3 int, col4 int, id int)

/*Use parameterized dynamic SQL*/
DECLARE @sql nvarchar(MAX) = N''

/*Hello I am your friend*/
SET @sql += N'
UPDATE u
SET
   u.id = @StatusIP,
   u.EnabledDate = GETDATE()
OUTPUT
   deleted.col1,
   deleted.col2,
   deleted.col3,
   deleted.col4,
   Inserted.id
INTO #output 
FROM
(
   SELECT TOP (5000)
       b.*
   FROM 
   (
        SELECT 
            b2.*
        FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
        WHERE b2.id = @status_D
        
        UNION ALL 
        
        SELECT 
            b2.*
        FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
        WHERE b2.id = @Status_F
        ORDER BY b2.ModifiedTime
   ) AS b
   ORDER BY b.ModifiedTime
) AS u;
';

EXEC sp_executesql @sql,
                  N'@status_D int,
                    @StatusIP int,
                    @Status_F int',
                    @status_D,
                    @StatusIP,
                    @Status_F;

SELECT 
   o.*
FROM #output AS o

COMMIT TRAN;         

您需要稍微調整一下以適應您的實際情況，但它應該讓您朝著正確的方向前進。

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