將遞歸 CTE 過濾為視圖

我正在使用 MSSQL Server 2017 並在視圖中使用 CTE 來獲取連結到一個特定元素的層次樹的根元素。

如果直接使用 CTE 並且基本元素都已過濾，則 CTE 非常快。主要問題是用作視圖時的性能，因為無法直接在 CTE 的基礎部分進行過濾。

例子：

創建包含 1000 個條目的 tmp 表：

SELECT TOP 1000 ID INTO #tElements from Elements

這是慢速視圖的 CTE 等效項：

WITH 
Tree AS
(SELECT        Element AS Node, Element, Parent
 FROM            Elements AS E
 UNION ALL
 SELECT        T.Node, E.Element, E.Parent, E.ProductID
 FROM         Elements AS E INNER JOIN
              Tree AS T ON T.Parent = E.Element)
SELECT DISTINCT Tree.Node, Tree.Element, Tree.Parent
FROM            Tree 
INNER JOIN      #tElements tmp on Tree.Node = tmp.ID
WHERE Tree.Parent IS NULL

這是直接過濾的CTE，速度很快

WITH 
Tree AS
(SELECT        Element AS Node, Element, Parent
 FROM            Elements AS E
 INNER JOIN      #tElements tmp on E.Element = tmp.ID
 UNION ALL
 SELECT        T.Node, E.Element, E.Parent, E.ProductID
 FROM         Elements AS E INNER JOIN
              Tree AS T ON T.Parent = E.Element)
SELECT DISTINCT Tree.Node, Tree.Element, Tree.Parent
FROM            Tree    
WHERE Parent IS NULL

也許有人暗示如何告訴伺服器首先過濾基本元素以進行連接，然後再執行遞歸部分？

謝謝邁克

您可以將其創建為內聯表值函式

CREATE OR ALTER FUNCTION GetElementsTree (
 @ID int
)
RETURNS TABLE AS RETURN

WITH Tree AS (
   SELECT       Element AS Node, Element, Parent
   FROM         Elements AS E
   WHERE        E.Element = @ID

   UNION ALL

   SELECT       T.Node, E.Element, E.Parent, E.ProductID
   FROM         Elements AS E INNER JOIN
                Tree AS T ON T.Parent = E.Element
)
SELECT
 Tree.Node,
 Tree.Element,
 Tree.Parent
FROM            Tree
WHERE Tree.Parent IS NULL;

go

SELECT DISTINCT
 Tree.Node,
 Tree.Element,
 Tree.Parent
FROM            #tElements tmp
CROSS APPLY     getElementsTree (tmp.ID) Tree

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