We are installing a new forum (yaf) for our site. One of the stored procedures is extremely slow - in fact it always times out in the browser. If I run it in MSSMS it takes nearly 10 minutes to complete. Is there a way to find out what part of this query if taking so long?
The Query:
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 a.CategoryID=@CategoryID) and
((@ParentID is null and b.ParentID is null) or b.ParentID=@ParentID) and
x.UserID = @UserID
order by
a.SortOrder,
b.SortOrder
IO Statistics:
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 Statistics:
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
