如何重写此查询（包括UNION，WITH和自联接应用于过滤）？

I have written this view when deadline was coming.

截止日期即将来临,我写了这个观点。

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

Now it's becoming bottleneck because of very expensive join between CaseTable and subquery with UNION (resulting in over 30% cost of frequently used procedure; in execution plan it's nested loops node with ~70% cost of select).

现在它变成了瓶颈,因为CaseTable和子查询与UNION之间非常昂贵的连接(导致经常使用的过程成本超过30%;在执行计划中,它是嵌套循环节点,选择成本约为70%)。

I have tried to rewrite it multiple times, but these attempts resulted only in worser perfomance.

我试图多次重写它,但这些尝试只会导致性能恶化。

Table CaseCategory have unique index on tuple (CaseID, CategoryID).

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

1 个解决方案

#1

It's probably a combination of problems with bad cardinality estimates and use of CTE. With what you've told us, I'd try to give some general guidance. Info you provided on the index means nothing without knowing the cardinality and distribution od the data. BTW, not sure if it would qualify as an answer, but it's too long for a comment. Feel free to downvote :)

这可能是基数估计不良和使用CTE的问题的组合。根据你告诉我们的内容,我会尝试给出一些一般指导。您在索引上提供的信息在没有了解数据的基数和分布情况下意味着什么。顺便说一句,不确定它是否有资格作为答案,但是评论太长了。随意downvote :)

There is a stored procedure selecting from the view, am I correct? I also presume you have some WHERE clause somewhere, right?

从视图中选择存储过程,我是否正确?我还假设你在某处有一些WHERE子句,对吧?

In that case, get rid of the view alltogether, and move the code into the procedure. This will allow to get rid of the CTE (which is most likely executed twice), and to save the intermediate results from what is now the CTE into a #temp table. Could be benefitial to apply the same #temp-table strategy with the UNION ALL subquery. Make sure to apply the WHERE predicates as soon as possible (SQL Server is usually good with pushing, but this combination of proc-view-CTE might confuse it).

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