解决慢速查询

Question

我们正在为我们的网站安装新的论坛（yaf）。其中一个存储过程非常慢 - 实际上它总是在浏览器中超时。如果我在MSSMS中运行它，则需要将近10分钟才能完成。有没有办法找出这个查询的哪一部分，如果这么长时间？

查询：

DECLARE @BoardID int 
DECLARE @UserID int 
DECLARE @CategoryID int = null 
DECLARE @ParentID int = null 

SET @BoardID = 1 
SET @UserID = 2 

    select 
    a.CategoryID, 
    Category  = a.Name, 
    ForumID   = b.ForumID, 
    Forum   = b.Name, 
    Description, 
    Topics   = [dbo].[yaf_forum_topics](b.ForumID), 
    Posts   = [dbo].[yaf_forum_posts](b.ForumID), 
    Subforums  = [dbo].[yaf_forum_subforums](b.ForumID, @UserID), 
    LastPosted  = t.LastPosted, 
    LastMessageID = t.LastMessageID, 
    LastUserID  = t.LastUserID, 
    LastUser  = IsNull(t.LastUserName,(select Name from [dbo].[yaf_User] x where x.UserID=t.LastUserID)), 
    LastTopicID  = t.TopicID, 
    LastTopicName = t.Topic, 
    b.Flags, 
    Viewing   = (select count(1) from [dbo].[yaf_Active] x JOIN [dbo].[yaf_User] usr ON x.UserID = usr.UserID where x.ForumID=b.ForumID AND usr.IsActiveExcluded = 0), 
    b.RemoteURL, 
    x.ReadAccess 
from 
    [dbo].[yaf_Category] a 
    join [dbo].[yaf_Forum] b on b.CategoryID=a.CategoryID 
    join [dbo].[yaf_vaccess] x on x.ForumID=b.ForumID 
    left outer join [dbo].[yaf_Topic] t ON t.TopicID = [dbo].[yaf_forum_lasttopic](b.ForumID,@UserID,b.LastTopicID,b.LastPosted) 
where 
    a.BoardID = @BoardID and 
    ((b.Flags & 2)=0 or x.ReadAccess<>0) and 
    (@CategoryID is null or [email protected]) and 
    ((@ParentID is null and b.ParentID is null) or [email protected]) and 
    x.UserID = @UserID 
order by 
    a.SortOrder, 
    b.SortOrder 

IO统计：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_Active'. Scan count 14, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_User'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_Topic'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_Category'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_Forum'. Scan count 0, logical reads 488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_UserGroup'. Scan count 231, logical reads 693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_ForumAccess'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_AccessMask'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'yaf_UserForum'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

客户端统计：

Client Execution Time 11:54:01   
Query Profile Statistics    
    Number of INSERT, DELETE and UPDATE statements 0  0.0000 
    Rows affected by INSERT, DELETE, or UPDATE statements 0  0.0000 
    Number of SELECT statements 8  8.0000 
    Rows returned by SELECT statements 19  19.0000 
    Number of transactions 0  0.0000 
Network Statistics   
    Number of server roundtrips 3  3.0000 
    TDS packets sent from client 3  3.0000 
    TDS packets received from server 34  34.0000 
    Bytes sent from client 3166  3166.0000 
    Bytes received from server 128802  128802.0000 
Time Statistics   
    Client processing time 156478  156478.0000 
    Total execution time 572009  572009.0000 
    Wait time on server replies 415531  415531.0000 

Execution Plan

Answer 1

看看Do you use [email protected] OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform以了解如何使用sp_executesql更好地执行此操作。

此外，这看起来像一个函数调用？

left outer join [dbo].[yaf_Topic] t ON t.TopicID = [dbo].[yaf_forum_lasttopic](b.ForumID,@UserID,b.LastTopicID,b.LastPosted) 

也许你的函数是搞乱这个事情了，硬编码的东西，而不是函数调用，看看它是否有剧烈的差异

Answer 2

有了这些类型的IO统计，查询必须b锁定。在SP运行时，请检查sys.dm_exec_requests，查看您的过程运行窗口（@@ SPID）的session_id，并参阅blocking_session_id，wait_type，wait_time和wait_resource。这些应该引导你了解阻塞的原因。

Answer 3

我注意到这个表是你最大IO的罪魁祸首：

表'yaf_UserGroup'。扫描计数231，逻辑读取693，物理读取0，预读0，lob逻辑读取0，lob物理读取0次，lob预读0

我还注意到，违规表是在查询中的任何地方都没有提及。这意味着它必须在查询中使用的某个UDF中引用。我的猜测是，UDF阻止查询有效地使用您的yaf_UserGroup表上的索引。您可以通过将有问题的UDF的逻辑直接合并到查询中来纠正此问题。

最后，无论你如何解决这个问题，它看起来像yaf是开源的。这意味着您应该将您的修补回馈给原始项目。这将有助于保持代码与基础项目的内联更紧密，以便将来的更新不会撤销您的性能修复。

Answer 4

我没有看到你的执行计划有什么不好。

大多数情况下，另一个会话会锁定您的查询，或者您的服务器负荷过重。

Answer 5

相关联的子查询是逐行运行的。将它们转换为连接。

与您的UDFs一样的东西。将它们转换为连接可以加快速度，因为它们也是逐行运行的。

什么SQL威胁说。