在 CTE 中從 Seek 轉換為 Scan 的索引

SQL Server 版本：

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) Nov 2 2020 18:35:09 版權所有 (C) 2019 Microsoft Corporation Enterprise Edition：Windows 上基於核心的許可（64 位）伺服器 2019 標準 10.0（內部版本 17763：）（管理程序）

我有一個查詢在不到一秒的時間內完成，兩個表中都使用了 500 萬條記錄。實際執行計劃顯示兩個表的索引查找，並且兩個表都讀取 1 或 2 行。

但是，將相同的查詢轉換為 CTE 時，大約需要 2 分鐘。實際執行計劃顯示索引掃描並讀取所有 500 萬行。

到目前為止，我的印像是，對於普通查詢，查詢是否在 CTE 內並不重要。即使是相同的嵌套查詢也很慢。

什麼可能會影響它？

實際查詢是：

select ca.inst_bs as Base,
   dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
   i2.id,
   ca.id as idcse,
   i2.dtdue 
from tbl_cse ca with(nolock) 
join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
where i2.idcse = 3169

轉換為 CTE 的查詢是：

with tt as 
( 
   select ca.inst_bs as Base, 
       dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
       i2.id,
       ca.id as idcse,
       i2.dtdue 
   from tbl_cse ca with(nolock) 
   join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse = 3169

高性能查詢的實際執行計劃：Query.sqlplan

粘貼計劃時視覺化的快速計劃

使用 CTE 進行慢查詢的實際執行計劃：CTE.sqlplan

粘貼計劃時視覺化的慢計劃

如果我將普通查詢轉換為視圖並idcse = 3169在視圖中使用條件，它的工作速度很慢，與 CTE 相同。

---

作為一個臨時解決方案，我創建了一個接受標準的表值函式，它按預期工作以提供索引搜尋。我需要繼續檢查完整的解決方案，因為我的許多查詢和視圖都使用帶有row_number.

問題

允許將謂詞推過視窗函式的優化器邏輯非常有限。謂詞必須在執行時引用一個常量，並且視窗函式的分區子句必須包含與謂詞相同的列。

您的數據庫正在使用強制參數化，因此您在查詢中提供的常量值將替換為參數標記@0。這可以防止上面連結的問答中描述的下推。

您還需要確保要過濾的列與ROW_NUMBER. 列具有相同的名稱或別名是不夠的，它們必須是相同的引用。

在您的查詢中：

with tt as 
( 
   select ca.inst_bs as Base, 
       dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
       i2.id,
       ca.id as idcse,
       i2.dtdue 
   from tbl_cse ca with(nolock) 
   join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse = 3169

• 被ROW_NUMBER劃分為i2.idcse
• 外部測試idcse指的是ca.id（別名為idcse）

這些不是相同的引用，它們只是具有相同的公開名稱。

由於 join on i2.idcse = ca.id，它們確實具有相同的值，但是額外的推理步驟足以阻止優化器應用轉換。

解決方案

您可以通過多種方式解決此問題。最簡單的可能是從 CTE 返回i2.idcse，而不是ca.id：

與 tt 作為
(
選擇 ca.inst_bs 作為基礎，
dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
i2.id,
       **i2.idcse** , -- 從 ca.id 更改為 idcse
i2.dtdue
來自 tbl_cse ca with(nolock)
在 i2.idcse = ca.id 上加入 tbl_Inst i2 with(nolock)
)
從 tt 中選擇 *，其中 idcse = 3169

要解決強制參數化問題，請添加OPTION (RECOMPILE)到查詢中。

正如我在 Stack Overflow 的回答中所說，我可能會堅持使用表值函式替換。這使您可以更好地控制謂詞出現的位置。

相關閱讀：

• 查詢速度很快，但在創建為視圖時變得遲緩
• SQL Server 視圖 | Stack Overflow 上的內聯視圖擴展指南

使用者charlieface提供了一個您可能喜歡玩的db<>fiddle 展示。

這是兩個非常不同的查詢，我懷疑您犯了一個錯誤。

你的第一個可以where i2.idcse = 3169在鍛煉之前應用過濾器 ROW_NUMBER()（因為它是這樣寫的）。您的第二個不能，因為某些與您的過濾器不匹配的行可能會影響該表達式的結果 - 您需要全部訪問它們併計算它，然後才能過濾。

您在第二個查詢中的過濾器idcse = 3169實際上來自ca.id as idcse. 如果您的子句中涉及此表達式，partition by那麼在row_number計算之前推到的謂詞是合法的。

也許您打算在與原始過濾器相同的列上進行過濾：

with tt as 
( 
   select ca.inst_bs as Base, 
       dateadd(mi,ROW_NUMBER() over (partition by i2.idcse,i2.idpln order by i2.dtdue,i2.id),i2.dtdue) as d1,
       i2.id,
       ca.id as idcse,
       i2.dtdue ,
       i2.idcse as idcse_filter
   from tbl_cse ca with(nolock) 
   join tbl_Inst i2 with(nolock) on i2.idcse = ca.id
)
select * from tt where idcse_filter = 3169

這在法律上將允許推送過濾器，是否在您的 SQL Server 版本中推送它是另一個問題。

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