sql - 递归 SQL (CTE) 性能低下

Question

很抱歉，如果这个问题有点长，但没有简单的表达方式。

我有以下查询

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) 

在 GetParentCategoriesByCategoryId() 中，我有以下公用表表达式 (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

我的查询执行计划如下所示。

不幸的是，对于 2000 行，我的查询时间超过 7 秒。我相信我已经添加了正确的索引(并且它似乎表明查询正在使用它们)。

我已经能够将问题缩小到 CTE 中的 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 blah blah blah....

因为当我删除它时，性能会大大提高，但到目前为止我只能推断出这些。

我不期待一个解决方案，因为我知道它基于许多因素，但我远不是 SQL 专家，我希望有人能就我可能需要寻找的内容提供任何指导？

表的架构可以在这里找到:https://www.dropbox.com/s/tpetq6fky58fhti/schemas.sql?dl=0

Answer 1

因此对于任何好奇的人来说，最终解决方案包括重做我的索引，利用上面评论中的一些建议，重要的是删除临时表。

最后，我设法将查询时间缩短到 1 秒以内，这是我的目标。 但我不太确定 Group By，想知道是否有更好的方法来做到这一点？还有其他人有任何进一步的改进吗？

    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(*)