為什麼這個帶有參數的遞歸 CTE 在使用文字時不使用索引？

我在樹結構上使用遞歸 CTE 來列出樹中特定節點的所有後代。如果我在我的WHERE子句中寫一個文字節點值，SQL Server 似乎實際上只將 CTE 應用於該值，從而給出一個實際行數較低的查詢計劃，等等：

但是，如果我將該值作為參數傳遞，它似乎會實現（假離線）CTE，然後在事後對其進行過濾：

我可能讀錯了計劃。我沒有註意到性能問題，但我擔心 CTE 的實現可能會導致更大的數據集出現問題，尤其是在更繁忙的系統中。此外，我通常會在自身上複合這種遍歷：向上遍歷祖先並返回後代（以確保收集所有相關節點）。由於我的數據是這樣的，每組“相關”節點都相當小，因此實現 CTE 沒有意義。當 SQL Server 似乎實現了 CTE 時，它的“實際”計數給了我一些相當大的數字。

有沒有辦法讓查詢的參數化版本像文字版本一樣工作？我想將 CTE 放在可重用的視圖中。

用文字查詢：

CREATE PROCEDURE #c AS BEGIN;
   WITH descendants AS (SELECT
        t.ParentId Id
       ,t.Id DescendantId
   FROM #tree t
   WHERE t.ParentId IS NOT NULL
   UNION ALL SELECT
        d.Id
       ,t.Id DescendantId
   FROM descendants d
   JOIN #tree t ON d.DescendantId = t.ParentId)
   SELECT d.*
   FROM descendants d
   WHERE d.Id = 24
   ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

帶參數查詢：

CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
   WITH descendants AS (SELECT
        t.ParentId Id
       ,t.Id DescendantId
   FROM #tree t
   WHERE t.ParentId IS NOT NULL
   UNION ALL SELECT
        d.Id
       ,t.Id DescendantId
   FROM descendants d
   JOIN #tree t ON d.DescendantId = t.ParentId)
   SELECT d.*
   FROM descendants d
   WHERE d.Id = @Id
   ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

設置程式碼：

DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
    Id BIGINT NOT NULL PRIMARY KEY
   ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
        CAST(1 AS BIGINT) Value
   UNION ALL SELECT
        n.Value * 2 + 1
   FROM number n
   WHERE n.Value * 2 + 1 <= @count
   UNION ALL SELECT
        n.Value * 2
   FROM number n
   WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;

Randi Vertongen 的回答正確地解決瞭如何使用查詢的參數化版本獲得所需的計劃。如果您對細節感興趣，該答案通過解決問題的標題來補充這一點。

SQL Server 將尾遞歸公用表表達式 (CTE) 重寫為迭代。從惰性索引假離線向下的所有內容都是迭代翻譯的執行時實現。我詳細說明了執行計劃的這一部分是如何工作的，以回答Using EXCEPT in a recursive common table expression。

您想在 CTE之外指定一個謂詞（過濾器） ，並讓查詢優化器將此過濾器下推到遞歸內部（重寫為迭代）並將其應用於錨成員。這將意味著遞歸僅從匹配的那些記錄開始ParentId = @Id。

這是一個相當合理的期望，無論是使用文字值、變數還是參數；但是，優化器只能執行已為其編寫規則的事情。規則指定如何修改邏輯查詢樹以實現特定轉換。它們包括確保最終結果安全的邏輯 - 即它在所有可能的情況下返回與原始查詢規範完全相同的數據。

負責在遞歸 CTE 上推送謂詞的規則稱為SelOnIterator- 實現遞歸的迭代器上的關係選擇（= 謂詞）。更準確地說，這個規則可以將選擇複製到遞歸迭代的錨部分：

Sel(Iter(A,R)) -> Sel(Iter(Sel(A),R))

可以使用未記錄的提示禁用此規則OPTION(QUERYRULEOFF SelOnIterator)。使用此選項時，優化器不能再將具有文字值的謂詞下推到遞歸 CTE 的錨點。你不希望這樣，但它說明了這一點。

最初，此規則僅限於處理具有文字值的謂詞。它也可以通過指定來處理變數或參數OPTION (RECOMPILE)，因為該提示啟用了參數嵌入優化，從而在編譯計劃時使用變數（或參數）的執行時文字值。該計劃沒有被記憶體，因此它的缺點是每次執行時都要重新編譯。

在某些時候，該SelOnIterator規則得到了改進，也適用於變數和參數。為了避免意外的計劃更改，這受到 4199 跟踪標誌、數據庫兼容性級別和查詢優化器修補程序兼容性級別的保護。這是優化器改進的正常模式，並不總是記錄在案。改進通常對大多數人都有好處，但任何改變都有可能給某人帶來倒退。

我想將 CTE 放在可重複使用的視圖中

您可以使用內聯表值函式而不是視圖。提供要下推的值作為參數，並將謂詞放在遞歸錨成員中。

如果您願意，也可以選擇全域啟用跟踪標誌 4199。此標誌涵蓋了許多優化器更改，因此您需要在啟用它的情況下仔細測試您的工作負載，並準備好處理回歸。

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