慢 CTE 更新查詢

我有一個查詢執行了三個多小時而沒有完成。

我意識到不好的部分是過濾器運算符，它的成本為 89%。

WITH 
   FASES_NUMERADAS_CTE AS 
   (
       SELECT 
           NUM_PROCES, 
           DES_SISTEM_PROCES, 
           DAT_PRIMEIRA_FASE, 
           CLASSE_SISTEMA_ATUAL, 
           CLASSE_SISTEMA_ANTERIOR, 
           DAT_CLASSE_ATUAL, 
           ROW_NUMBER() OVER (
               PARTITION BY NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE 
               ORDER BY DAT_CLASSE_ATUAL) AS RN
       FROM #TEMP_COMPLE_10966_GERAL
   ), 
   FASES_RN_1_CTE AS 
   (
       SELECT *
       FROM FASES_NUMERADAS_CTE
       WHERE RN = 1
   )
UPDATE TC 
SET TC.CLASSE_SISTEMA_ATUAL = T2.CLASSE_SISTEMA_ATUAL 
FROM FASES_RN_1_CTE T1 
CROSS APPLY 
(
   SELECT TOP 1 T.CLASSE_SISTEMA_ATUAL 
   FROM FASES_NUMERADAS_CTE T
   WHERE T.NUM_PROCES = T1.NUM_PROCES
   AND T.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES
   AND T.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE
   AND T.RN > 1
   ORDER BY T.RN DESC
) AS T2 
INNER JOIN #TEMP_COMPLE_10966_GERAL TC 
   ON TC.NUM_PROCES = T1.NUM_PROCES 
   AND TC.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES 
   AND TC.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE 
   AND TC.DAT_CLASSE_ATUAL = T1.DAT_CLASSE_ATUAL 
   AND TC.CLASSE_SISTEMA_ANTERIOR = T1.CLASSE_SISTEMA_ANTERIOR;

由於它沒有結束，我沒有真正的執行計劃。

https://www.brentozar.com/pastetheplan/?id=B1x_w-QeK

有沒有人有任何改善執行時的提示？

我不知道這會解決您所有的性能問題，但它至少可以讓您更好地確定查詢的哪個部分實際上很慢，並且可能使更完整的解決方案顯而易見。

從性能的角度來看， CTE 通常是無用的構造。

CREATE CLUSTERED INDEX c ON 
   #TEMP_COMPLE_10966_GERAL
(
   NUM_PROCES, 
   DES_SISTEM_PROCES, 
   DAT_PRIMEIRA_FASE,
   DAT_CLASSE_ATUAL
); /*This index supports the row number function*/


SELECT 
   NUM_PROCES, 
   DES_SISTEM_PROCES, 
   DAT_PRIMEIRA_FASE, 
   CLASSE_SISTEMA_ATUAL, 
   CLASSE_SISTEMA_ANTERIOR, 
   DAT_CLASSE_ATUAL, 
   RN = 
       ROW_NUMBER() OVER 
       (
           PARTITION BY 
               NUM_PROCES, 
               DES_SISTEM_PROCES, 
               DAT_PRIMEIRA_FASE 
           ORDER BY 
               DAT_CLASSE_ATUAL
       )
INTO #FASES_NUMERADAS_CTE
FROM #TEMP_COMPLE_10966_GERAL;
/*Step one*/


SELECT 
   *
INTO #FASES_RN_1_CTE
FROM #FASES_NUMERADAS_CTE
WHERE RN = 1
/*Step two*/


UPDATE TC
   SET TC.CLASSE_SISTEMA_ATUAL = 
           T2.CLASSE_SISTEMA_ATUAL
FROM #FASES_RN_1_CTE T1
CROSS APPLY 
(
   SELECT TOP (1) 
       T.CLASSE_SISTEMA_ATUAL
   FROM #FASES_NUMERADAS_CTE T
   WHERE T.NUM_PROCES = T1.NUM_PROCES
   AND   T.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES
   AND   T.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE
   AND   T.RN > 1
   ORDER BY T.RN DESC
) AS T2
INNER JOIN #TEMP_COMPLE_10966_GERAL TC
   ON  TC.NUM_PROCES = T1.NUM_PROCES
   AND TC.DES_SISTEM_PROCES = T1.DES_SISTEM_PROCES
   AND TC.DAT_PRIMEIRA_FASE = T1.DAT_PRIMEIRA_FASE
   AND TC.DAT_CLASSE_ATUAL = T1.DAT_CLASSE_ATUAL
   AND TC.CLASSE_SISTEMA_ANTERIOR = T1.CLASSE_SISTEMA_ANTERIOR;
   /*Last, update*/

另一種選擇使用聚集索引，最終鍵按降序排序：

CLUSTERED (NUM_PROCES, DES_SISTEM_PROCES, DAT_PRIMEIRA_FASE, DAT_CLASSE_ATUAL DESC)

和一個虛擬的非聚集列儲存索引：

CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON #TEMP_COMPLE_10966_GERAL (NUM_PROCES) 
WHERE NUM_PROCES = 0 
AND NUM_PROCES = 1;

由於 where 子句中的矛盾，該列儲存索引不需要時間來創建並且從不包含任何行。它的重點是在 SQL Server 2016 上啟用批處理。

有了這些索引，以下內容可以滿足您的需要：

UPDATE Q2
SET Q2.CLASSE_SISTEMA_ATUAL = Q2.LAST_CLASSE_SISTEMA_ATUAL
FROM
(
   SELECT
       Q1.*,
       LAST_CLASSE_SISTEMA_ATUAL =
           MAX(IIF(Q1.PrevRow IS NULL, Q1.CLASSE_SISTEMA_ATUAL, NULL)) OVER (
               PARTITION BY Q1.NUM_PROCES, Q1.DES_SISTEM_PROCES, Q1.DAT_PRIMEIRA_FASE 
               ORDER BY Q1.DAT_CLASSE_ATUAL DESC 
               ROWS UNBOUNDED PRECEDING),
       NextRow = 
           LEAD(Q1.NUM_PROCES) OVER (
               PARTITION BY Q1.NUM_PROCES, Q1.DES_SISTEM_PROCES, Q1.DAT_PRIMEIRA_FASE 
               ORDER BY Q1.DAT_CLASSE_ATUAL DESC)
   FROM 
   (
       SELECT
           TCG.*,
           PrevRow = 
               LAG(TCG.NUM_PROCES) OVER (
                   PARTITION BY TCG.NUM_PROCES, TCG.DES_SISTEM_PROCES, TCG.DAT_PRIMEIRA_FASE 
                   ORDER BY TCG.DAT_CLASSE_ATUAL DESC)
       FROM #TEMP_COMPLE_10966_GERAL AS TCG
   ) AS Q1
) AS Q2
WHERE
   Q2.NextRow IS NULL;

LAG這裡的想法是通過或LEAD將NULL針對該情況返回的事實來辨識每個組中的第一行和最後一行。視窗化MAX將目標第一行值轉發到視窗中的所有行，因此我們可以在最後執行直接值更新。

更自然FIRST_VALUE並且LAST_VALUE不使用，因為它們目前與批處理模式執行不兼容（除非在LAST_VALUE邏輯上等同於LAGor LEAD）。

執行計劃是：

除掃描和更新之外的所有操作符都以批處理模式執行。這應該是一個非常有效的計劃。

遺憾的是，並行視窗聚合還不能很好地支持來自 b-tree 索引的順序保留，因此如果您要求並行計劃，您可能會獲得併行行模式視窗假離線而不是批處理模式視窗聚合。與舊式的行模式線軸相比，這些仍然非常有效，但與視窗聚合不同。

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