将递归CTE过滤为视图

Question

我正在使用MSSQL 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

也许有人有一个提示，告诉服务器首先过滤连接的基本元素，然后再执行递归部分？

谢谢迈克

Answer 1

可以将其创建为内联表值函数。

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