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
JOIN
to the temp table. Seeing the actual execution plans of both (which you can upload to Paste The Plan) would be helpful.