0

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

3
  • 1
    Little confused on your verbiage / example. You mentioned the first example is the query for the View, but it uses a temp table, which is not permitted in Views. Also is the temp table the only difference between the two queries?...if so, then I don't think your issue is the fact you're using a View, rather it's the different execution plan you're getting from that JOIN to the temp table. Seeing the actual execution plans of both (which you can upload to Paste The Plan) would be helpful.
    – J.D.
    Jul 15, 2022 at 12:18
  • @J.D. yes here its not a view, i just wanted to provide the simplest possible example. In my use case someone else must access the database and he for god knows why can only use select on tables or views and hes the one whos doing the join in the end. The real view is a join of 10 tables and takes 10seconds in the bad case and less than 1 seconds in the fast case. The solution of Charliface is the way to go. Got the needed performance with the simple solution of creating a view using a table valued function
    – Findnix
    Jul 16, 2022 at 15:59
  • 1
    For future reference, if your real query is slow and involves 10 joins, that's a completely different query than one that involves 1 join to a temp table. The reasons for slowness will (most times) be different and hard to debug without the actual query. Please provide the actual query next time (and ideally the actual execution plan when possible) as this will help us help you. Glad to hear Charlieface's answer helped you, but I think that's more so by providing an alternative way to accomplish your goal rather than solving the original root performance bottleneck you had. Hard to say though.
    – J.D.
    Jul 16, 2022 at 20:18

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.