Slow performance with recursive SQL (CTE)

Question

Sorry if this is a bit of a long question but there is no simple way to express it.

I have the following query

SELECT 
    S.*
FROM 
    Stock S
LEFT JOIN 
    Stock_Category SC ON SC.StockId = S.Id
WHERE 
    S.Published = 1 
    AND (@CategoryId IS NULL OR 
         (SELECT COUNT(*) 
          FROM GetParentCategoriesByCategoryId(SC.CategoryId) 
          WHERE Id = @CategoryId) > 0) 

Inside the GetParentCategoriesByCategoryId(), I have the following common table expression (CTE):

DECLARE @TableOutput TABLE(Id UNIQUEIDENTIFIER, 
                            PosDissectionId INT,
                            PosFamilyClassId INT,
                            ParentId UNIQUEIDENTIFIER,
                            Code NVARCHAR(25),
                            [Name] NVARCHAR(100),
                            Description NVARCHAR(1000),
                            AzureId UNIQUEIDENTIFIER,
                            Extension NVARCHAR(10),
                            Visible BIT,
                            OrderIndex INT,
                            StockCount INT,
                            Depth INT)
BEGIN
    DECLARE @TotalVisible INT,
            @TotalRows INT

    ;WITH CategoryStructure (Id, ParentId, ParentName, Name, Depth, Visible)
    As 
    ( 
        SELECT 
            C.Id, 
            C.ParentId, 
            CAST('' AS NVARCHAR(500)) AS ParentName, 
            C.Name, 
            0 AS Depth, 
            C.Visible
        FROM 
            Category C
        WHERE 
            Id = @LocalCategoryId

        UNION ALL

        SELECT 
            ParentCategory.Id, 
            ParentCategory.ParentId, 
            CategoryStructure.Name AS ParentName, 
            ParentCategory.Name, 
            CategoryStructure.Depth + 1,
            ParentCategory.Visible
        FROM 
            Category ParentCategory
        INNER JOIN 
            CategoryStructure ON ParentCategory.Id = CategoryStructure.ParentId
    )
    INSERT INTO @TableOutput
        SELECT          
            C.*,
            SC.StockCount,
            CS.Depth
        FROM 
            CategoryStructure CS 
        INNER JOIN 
            Category C ON  C.Id = CS.Id
        LEFT JOIN 
            (SELECT CategoryId, COUNT(*) AS StockCount 
             FROM Stock_Category SC
             INNER JOIN Stock S ON S.Id = SC.StockId
             WHERE S.Published = 1 AND 
                 ((S.WidthMM IS NOT NULL AND 
                   S.HeightMM IS NOT NULL AND 
                   S.DepthMM IS NOT NULL AND
                    S.WeightG IS NOT NULL)) AND
                CategoryId IN(SELECT CategoryId FROM CategoryStructure)
        GROUP BY CategoryId

    ) SC ON SC.CategoryId = CS.Id

    WHERE (@IncludeSelf = 1 OR CS.Id != @CategoryId) 

    SELECT 
        @TotalVisible = SUM(CONVERT(INT, Visible)),
        @TotalRows = COUNT(*) 
    FROM @TableOutput

    IF @TotalVisible <> @TotalRows
        DELETE FROM @TableOutput    

    RETURN
END

My query execution plan looks like this.

Unfortunately I am getting over 7s long query times for 2000 rows. I believe I have added the correct indexes (and it appears to show that the query is using them).

I have been able to narrow the problem down to the LEFT JOIN in the CTE

   SELECT CategoryId, COUNT(*) AS StockCount 
   FROM Stock_Category SC
   INNER JOIN Stock S ON S.Id = SC.StockId
   WHERE S.Published = 1 AND blah blah blah....

Because when I remove it performance drastically increases but that's all I can deduce so far.

I am not expecting a solution because I understand that its based on many factors but I am far from an SQL expert and am hoping someone could provide any guidance on what I might need to look for?

Schemas for tables can be found here: https://www.dropbox.com/s/tpetq6fky58fhti/schemas.sql?dl=0

Answer 1

So for anyone curious the final solution involved redoing my indexes, utilising some of the suggestions from the comments above and importantly removing the temporary table.

In the end, I managed to get the query down to less than 1 second which was the goal. But I am not really sure about the Group By, wondering if there is a better way to do it? Does anyone else have any further improvements?

    WITH categorystructure (id, parentid, parentname, NAME, depth, visible) 
     AS (SELECT C.id, 
                C.parentid, 
                Cast('' AS NVARCHAR(500)) AS ParentName, 
                C.NAME, 
                0                         AS Depth, 
                C.visible 
         FROM   category C 
         WHERE  id = @CategoryId 
         UNION ALL 
         SELECT ParentCategory.id, 
                ParentCategory.parentid, 
                categorystructure.NAME      AS ParentName, 
                ParentCategory.NAME, 
                categorystructure.depth + 1 AS Depth, 
                ParentCategory.visible 
         FROM   category ParentCategory 
                INNER JOIN categorystructure 
                        ON ParentCategory.id = categorystructure.parentid) 
SELECT C.*, 
       Isnull(SC.stockcount, 0) AS StockCount, 
       CS.depth 
FROM   categorystructure CS 
       INNER JOIN category C 
               ON C.id = CS.id 
       LEFT JOIN (SELECT categoryid, 
                         Count(*) AS StockCount 
                  FROM   stock_category SC 
                         INNER JOIN stock S 
                                 ON S.id = SC.stockid 
                  WHERE  S.published = 1 
                         AND ( @AustPostShippingEnabled = 0 
                                OR ( S.widthmm IS NOT NULL 
                                     AND S.heightmm IS NOT NULL 
                                     AND S.depthmm IS NOT NULL 
                                     AND S.weightg IS NOT NULL ) ) 
                  GROUP  BY categoryid) SC 
              ON SC.categoryid = CS.id 
WHERE  ( @IncludeSelf = 1 
          OR CS.id != @CategoryId ) 
GROUP  BY C.id, 
          C.posdissectionid, 
          C.posfamilyclassid, 
          C.parentid, 
          C.code, 
          C.NAME, 
          C.description, 
          C.azureid, 
          C.extension, 
          C.visible, 
          C.orderindex, 
          SC.stockcount, 
          CS.depth 
HAVING Sum(CONVERT(INT, CS.visible)) = Count(*)