SQL recursive hierarchy very slow

Question

So I just got a task to optimize an SQL query that builds a hierarchy tree of products, inner products, manufactory operations, operation details, and items (needed to make the product). The query returns 218k rows, works correctly but takes about 15-18 seconds to build the tree. Is it possible to optimize that query so it would take about 2-3 seconds? If I add @ProductID as a parameter for this procedure and do

WHERE ProductID = @ProductID

after selecting from Product table, then it works very fast, but tree is not complete, because if the product that I build the tree for is an inner product, it's parent product does not show up in the tree. Would it be faster to load all these tables to the C# application and do all hierarchy building there?

I'm open to all ideas. Below is the query in question

  ALTER PROCEDURE [dbo].[ProductProduct_selectHierarchy_v2]
WITH EXECUTE AS CALLER
AS
BEGIN
WITH ProductProductHierarchy (
    ProductID
    ,ItemProductID
    ,LEVEL
    ,UniqueID
    ,ParentID
    ,RowType
    ,ProductDetailID
    ,ProductProductDetailBindID
    ,ManufactoryOperationID
    ,ItemID
    )
AS (
    -- Anchor member definition
    SELECT dbo.Product.ProductID AS ProductID
        ,Product.ProductID AS ItemProductID
        ,0 AS LEVEL
        ,Cast(cast(Product.ProductID AS NVARCHAR(40)) + '' AS NVARCHAR(50)) AS UniqueID
        ,cast('' AS NVARCHAR(50)) AS ParentID
        ,1 AS RowType
        ,NULL AS ProductDetailID
        ,NULL AS ProductProductDetailBindID
        ,NULL AS ManufactoryOperationID
        ,NULL AS ItemID
    FROM dbo.Product

    UNION ALL

    -- Recursive member definition
    SELECT e.ProductID
        ,e.ItemProductID
        ,LEVEL + 1
        ,Cast(Cast(d.UniqueID AS NVARCHAR(40)) + '_' + cast((e.ItemProductID) AS NVARCHAR(10)) AS NVARCHAR(50)) AS UniqueID
        ,Cast(d.UniqueID AS NVARCHAR(50)) AS ParentID
        ,1 AS RowType
        ,NULL AS ProductDetailID
        ,NULL AS ProductProductDetailBindID
        ,NULL AS ManufactoryOperationID
        ,NULL AS ItemID
    FROM dbo.ProductProduct AS e
    INNER JOIN ProductProductHierarchy AS d ON e.ProductID = d.ItemProductID

    UNION ALL

    SELECT NULL AS ProductID
        ,NULL
        ,LEVEL + 1
        ,Cast(Cast(d.UniqueID AS NVARCHAR(40)) + '_' + cast((e.ProductDetailID) AS NVARCHAR(10)) AS NVARCHAR(50)) AS UniqueID
        ,Cast(d.UniqueID AS NVARCHAR(50)) AS ParentID
        ,2 AS RowType
        ,e.ProductDetailID AS ProductDetailID
        ,e.ProductProductDetailBindID AS ProductProductDetailBindID
        ,NULL AS ManufactoryOperationID
        ,NULL AS ItemID
    FROM dbo.ProductProductDetailBind AS e
    INNER JOIN ProductProductHierarchy AS d ON e.ProductID = d.ItemProductID

    UNION ALL

    SELECT NULL AS ProductID
        ,NULL
        ,LEVEL + 1
        ,Cast(Cast(d.UniqueID AS NVARCHAR(40)) + '_' + cast((e.ManufactoryOperationID) AS NVARCHAR(10)) AS NVARCHAR(50)) AS UniqueID
        ,Cast(d.UniqueID AS NVARCHAR(50)) AS ParentID
        ,3 AS RowType
        ,NULL AS ProductDetailID
        ,NULL AS ProductProductDetailBindID
        ,e.ManufactoryOperationID AS ManufactoryOperationID
        ,NULL AS ItemID
    FROM dbo.ProductDetailOperation AS e
    INNER JOIN ProductProductHierarchy AS d ON e.ProductProductDetailBindID = d.ProductProductDetailBindID

    UNION ALL

    SELECT NULL AS ProductID
        ,NULL
        ,LEVEL + 1
        ,Cast(Cast(d.UniqueID AS NVARCHAR(40)) + '_' + cast((e.ItemID) AS NVARCHAR(10)) AS NVARCHAR(50)) AS UniqueID
        ,Cast(d.UniqueID AS NVARCHAR(50)) AS ParentID
        ,4 AS RowType
        ,NULL AS ProductDetailID
        ,NULL AS ProductProductDetailBindID
        ,NULL AS ManufactoryOperationID
        ,e.ItemID AS ItemID
    FROM dbo.ProductItem AS e
    INNER JOIN ProductProductHierarchy AS d ON e.ProductID = d.ItemProductID
    )
-- Statement that executes the CTE
SELECT CASE 
        WHEN RowType = 1
            THEN ProductName + '<' + ProductCode + '>'
        WHEN RowType = 2
            THEN ProductDetail.ProductDetailName
        WHEN RowType = 3
            THEN ManufactoryOperation.ManufactoryOperationName
        ELSE Item.ItemName
        END AS ProductName
    ,UniqueID
    ,ProductProductHierarchy.ParentID
    ,Product.ProductID
    ,RowType
FROM ProductProductHierarchy
LEFT OUTER JOIN Product ON Product.ProductID = ProductProductHierarchy.ItemProductID
    AND RowType = 1
LEFT OUTER JOIN ProductDetail ON ProductDetail.ProductDetailID = ProductProductHierarchy.ProductDetailID
    AND RowType = 2
LEFT OUTER JOIN ManufactoryOperation ON ManufactoryOperation.ManufactoryOperationID = ProductProductHierarchy.ManufactoryOperationID
    AND RowType = 3
LEFT OUTER JOIN Item ON Item.ItemID = ProductProductHierarchy.ItemID
    AND RowType = 4
OPTION (MAXRECURSION 0)

END

Answer 1

It looks like you're effectively building a set of heterogeneous rows, which I would try to stay away from in general. Get your product tree using recursion if you need to, but then pull in the operations, items, etc. as separate result sets. Mixing row types as you have is very likely to cause headaches (not just performance ones) down the road.

Without knowing the full functionality of your application I can't say the best way that you should retrieve those items from the DB, but I would definitely start there as a possible issue.