Filtering a recursive CTE as view

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply