如何重写此查询(包括应用于过滤的UNION、WITH和self-join )？

Question

我是在最后期限到来时写下这篇文章的。

WITH   AllCategories
AS     (SELECT CaseTable.CaseID,
               CT.Category,
               CT.CategoryType,
               Q.Note AS CategoryCaseNote,
               Q.CategoryID,
               Q.CategoryIsDefaultValue
        FROM   CaseTable
               INNER JOIN
               ((SELECT CaseID, -- Filled categories in table
                        CategoryCaseNote AS Note,
                        CategoryID,
                        -1 AS QuestionID,
                        0 AS CategoryIsDefaultValue
                 FROM   CaseCategory)
                UNION ALL
                (SELECT -1 AS CaseID, -- possible categories
                        NULL AS Note,
                        CategoryID AS CategoryID,
                        QuestionID,
                        1 AS CategoryIsDefaultValue
                 FROM   SHOW_QuestionCategory)) AS Q
               ON (Q.QuestionID = -1
                   OR Q.QuestionID = CaseTransactionTable.QuestionID)
                  AND (Q.CaseID = -1
                       OR Q.CaseID = CaseTable.CaseTransactionID)
               LEFT OUTER JOIN
               CategoryTable AS CT
               ON Q.CategoryID = CT.CategoryID)
SELECT A.*
FROM   AllCategories AS A
       INNER JOIN
       (SELECT   CaseID,
                 CategoryID,
                 MIN(CategoryIsDefaultValue) AS CategoryIsDefaultValue
        FROM     AllCategories
        GROUP BY CaseID, CategoryID) AS B
       ON A.CaseID = B.CaseID
          AND A.CategoryID = B.CategoryID
          AND A.CategoryIsDefaultValue = B.CategoryIsDefaultValue

现在它正在成为瓶颈，因为CaseTable和子查询与UNION之间的连接非常昂贵(导致频繁使用的过程的成本超过30%；在执行计划中，它的嵌套循环节点的select成本约为70%)。

我曾多次尝试重写它，但这些尝试只会导致更糟糕的性能。

表CaseCategory在元组(CaseID, CategoryID)上有唯一索引。

Answer 1

这可能是基数估计错误和CTE使用的问题的组合。根据您告诉我们的内容，我将尝试给出一些一般性的指导。在不知道数据的基数和分布的情况下，您在索引上提供的信息毫无意义。顺便说一句，不确定它是否有资格作为一个答案，但它对于评论来说太长了。请随意投下反对票:)

有一个从视图中选择的存储过程，我说的对吗？我还假设你在某个地方有一些WHERE子句，对吧？

在这种情况下，一起删除视图，并将代码移动到过程中。这将允许摆脱CTE (很可能执行两次)，并将来自现在的CTE的中间结果保存到#temp表中。对UNION ALL子查询应用相同的#temp-table策略可能是有益的。请确保尽快应用WHERE谓词(SQL Server通常适合用于推送，但proc-view-CTE的组合可能会使其混淆)。