间歇性性能问题-使用多个CTE和块执行计划的查询

Question

我正在尝试优化一个显示变量性能(小于2到超过15s)的SQL查询。

概述

• 这是一个ColdFusion网站，带有Server 2008 R2后端
• 该查询是网站搜索引擎的一部分，因此使用了相当多的。

这是查询的简化视图(一些联接和遗漏的位置)

with AllEligibleEntries AS (
    SELECT DISTINCT b.*,
        c.*,
        DENSE_RANK() OVER (ORDER BY b.CreationDate DESC) AS RowNum  
    From Ticket b
    LEFT JOIN Customer c ON b.CustomerID = c.CustomerID         -- CustomerID is a string, and NOT the primary key
    Where b.TicketID LIKE @P1                                   -- TicketID is a string, and NOT the primary key
        -- various other conditions here, some always present, other added as required by coldfusion through <cfif ...> AND ... </cfif>
), 
NumberOfEligibleEntries AS (
    select MAX(AllEligibleEntries.RowNum) AS NbRows
    from AllEligibleEntries
),
SelectedEntries AS (
    Select AllEligibleEntries.*
    From AllEligibleEntries
    WHERE AllEligibleEntries.RowNum BETWEEN @P2 AND @P3         -- used to set the number of results per page and the page to display
                                                                -- in all testcases, @P2 is 1 and @P3 is 50, but up to 1000 in production
),
MostRecentAction AS (                                           -- "Greatest-N-Per-Group" - [0]
    Select b1.Ticket, b1.StateID
    FROM TicketEvents b1
    LEFT OUTER JOIN TicketEvents b2
      ON b1.Ticket = b2.Ticket
         AND b1.TicketEventID < b2.TicketEventID
    WHERE b2.Ticket IS NULL
      AND b1.Ticket IN (Select Ticket from SelectedEntries)
),
CompletedTickets AS (
    Select TicketEventID, Ticket, TimeStamp
    From TicketEvents
    Where StateID = 5                                           -- this is the "completed" state
      AND Ticket IN (Select Ticket from SelectedEntries)
),
CompletionDate AS (     
    Select b1.Ticket, b1.TimeStamp
    FROM CompletedTickets b1
    LEFT OUTER JOIN CompletedTickets b2
      ON b1.Ticket = b2.Ticket
         AND b1.TicketEventID < b2.TicketEventID
    WHERE b2.Ticket IS NULL
)
Select SelectedEntries.*, NumberOfEligibleEntries.NbRows,
    d.TimeStamp AS CompletionDate
From SelectedEntries
left join NumberOfEligibleEntries on 1 = 1                      -- ugly hack, so the total number of hits can be displayed in the search result page
left join MostRecentAction a on SelectedEntries.Ticket = a.Ticket
left join CompletionDate d on SelectedEntries.Ticket = d.Ticket
order by SelectedEntries.RowNum

我知道写这个查询的方式有两个潜在的性能问题：

1. 垃圾执行计划

使用与ColdFusion相同的方法(即sp_prepexec，然后是sp_execute)，我使用一个@P1值对查询进行了修改，该值将导致AllEligibleEntries不包含任何结果，然后使用选择的另一个@P1进行sp_executed，以便AllEligibleEntries将包含49行。第二次运行的执行计划将在我发现为什么要上载它时出现错误时立即发布到sqlperformance.com，但这是完全错误的(超过2000次执行索引查找> 6000行时，预期在两行上运行两次，而且还有许多其他的)。

我已经确认使用option(recompile) (由1暗示)可以修复这个问题，但是这看起来更像是补丁而不是补丁。

问:在这种情况下，选项(重新编译)的替代方案是什么？

1. 多次执行查询

在研究这个问题时，我读到了2，CTE只是编写查询的一种很好的方式，但是最终，当使用时它们会被替换，因此可以多次执行。如果是这样的话(我还没有确认)，那么原始的AllEligibleEntries CTE必须执行大约4次。

问:如果是这样的话，是否有办法确保CTE只执行一次？(我猜答案是临时表，但这也更像是补丁而不是补丁。)

问:还有其他我没有注意到的主要性能问题吗？

谢谢

0

1

2

Answer 1

就目前而言，这一问题已通过以下方式得到解决：

• 将AllEligibleEntries转换为临时表
• 将NumberOfEligibleEntries转换为局部变量，从临时表的select中获得
• 在临时表的操作之间将SelectedEntries转换为DELETE
• 把剩下的CTE保持原样。

与选项(重新编译)方法相比，性能要好得多(只有几个100 is )，而且(现在是多个)执行计划累积起来要简单得多。

我仍然对这个问题的反馈感兴趣，尽管我可能不会很快重新讨论代码。

谢谢。