Filtering a recursive CTE as view

Question

i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.

If the CTE is used directly and the base elements are allready filtered the CTE is very fast. The main problem is the performance when used as view because there it is not possible to filter directly in the base part of the CTE.

Example:

Create tmp table with 1000 entries:

SELECT TOP 1000 ID INTO #tElements from Elements

This is the CTE equivalent of the view which is slow:

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

This is the CTE directly filtered which is fast

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

Maybe someone has a hint how to tell the server to filter the base elements first for the join and do the recursive part afterwards?

Thanks Mike

Answer 1

You can create it as an inline Table Valued Function

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