了解查詢速度慢的原因

我在 SQL Server 2016 中有一個新列，位於位類型的表上。（我希望它是一個持久的計算列，但被否決並要求使它有點由程式碼維護）。它的預設值為 0。

我使用以下 SQL 摘錄嘗試為開發環境中的現有行設置初始值，但必須在 5 分鐘後終止它。

update dbo.tableName set newColumn = 

cast(
   (
       case when
           (
           isNull(colA,'') <> ''
           or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
           or colC is not null
           or isNull(colD,'') <> ''
           )
       then 1 
       else 0 
       end
   ) 
as bit);

該表有大約 92,000 行，大約 3,200 行應該設置值 1。（我知道上面也會將剩餘的 88,800 行值設置為 0，即使預設約束已經將它們設置為 0，但是在 WHERE 子句中使用上述邏輯的 SELECT 會在 1 秒內執行）。

我的問題是為什麼上述需要這麼長時間，我將如何確定根本原因？（我應該獲得一個查詢計劃並從那里工作嗎？我會尋找什麼？）

我重寫了更新如下，它在 3 秒內完成。

with cteCommonTableExpression (cteIdColumn)
as
(
   select dbo.tableName.idColumn from dbo.tableName
       where
           case when
               (
               isNull(colA,'') <> ''
               or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
               or colC is not null
               or isNull(colD,'') <> ''
               )
           then 1 
           else 0 
           end
       = 1
)

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in (select cteIdColumn from cteCommonTableExpression);

以下 - 我希望在邏輯上與最後一段程式碼相同 - 也可以在 3 秒內完成。

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in 
(select tn2.idColumn from dbo.tableName tn2 where case when
               (
               isNull(tn2.colA,'') <> ''
               or isNull(tn2.colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
               or tn2.colC is not null
               or isNull(tn2.colD,'') <> ''
               )
           then 1 
           else 0 
           end
       = 1)

在這裡發布後不久，我獲得了估計的查詢計劃，該計劃報告 89% 的成本用於主鍵上的聚集索引更新。新列不涉及主鍵。問題變成了：為什麼這個更新需要聚集索引更新（尤其是替代更新顯然不需要）？

沒看計劃…

更新所有 92k 行

• 從頭到尾掃描表
• 更新所有行（值是否相同）
• 聚集索引葉 = 實際數據

= 你實際上是在做聚集索引更新

當您更新 3,200 行時，意味著您減少了**96.5%**的數據更改工作（3200 是 92000 的 3.5%），所以它當然會執行得更快

事務日誌中的事務大小（用於回滾）也會大得多。這需要分配並且可能需要增加日誌文件大小

基本上，只更新你需要的行……

鑑於：

在開發環境中為現有行設置初始值，但必須在 5 分鐘後終止它。

和

該表大約有 92,000 行

應該是阻塞了。否則行太少了，不能花那麼長時間。

和

為什麼此更新需要聚集索引更新（尤其是替代更新顯然不需要）？

新列位於聚集索引上。所以這就是它必須更新的地方。

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