We have a table,
ProductHierarchy, which is set. The # of rows will not change. It has simple parent/child Product Hierarchy data in it.
We also have a Table-valued function which takes a
ProductHierarchyId and then returns all rows where IsDescendantOf is true for that Id. Or in other words, it returns that row, plus all of its ancestors.
The problem is that with this Table-valued function, we have to use it with
CROSS APPLY, and this is seriously slowing down the query.
My thought is to create either a second permanent table (or a temp table/table variable in the query in question) that has all possible results from the Table-valued function already in it. And then
JOIN to that table instead of using the
CROSS APPLY. In this table, we would add a column called something like
QueryID. So instead of
CROSS APPLY dbo.GetProductLevels(P.ProductHierarchyId)
We could use
LEFT JOIN FutureTable ft ON ft.QueryId = P.ProductHierarchyId
I’m struggling with the query to create that table. Here’s what I have so far…
SELECT * , 1 AS QueryId FROM dbo.ProductHierarchy WHERE (SELECT ProductHierarchyNode FROM dbo.ProductHierarchy WHERE ProductHierarchyId = 1).IsDescendantOf(ProductHierarchyNode) = 1
Ok, so that works great for the record where ProductHierarchyId = 1. But then I’d need to repeat that for ProductHierarchyId = 2:
SELECT * , 2 AS QueryId FROM dbo.ProductHierarchy WHERE (SELECT ProductHierarchyNode FROM dbo.ProductHierarchy WHERE ProductHierarchyId = 2).IsDescendantOf(ProductHierarchyNode) = 1
And then for 3, and then for 4, all the way to the last Id, doing a
UNION each time, inside a loop — which is hideous.
I KNOW there is a way to do this all in one query. Something like a recursive CTE. But my brain isn’t getting there.
Wouldn’t you just do this?
SELECT . . . -- the columns you want INTO . . . -- where you want them FROM dbo.ProductHierarchy ph CROSS APPLY dbo.GetProductLevels(P.ProductHierarchyId);