Hi everyone,
i've recently imported tons of data onto the latest version of YAF (we were using a version from 2001 something like that, that we modified a thousand times..)
and the 'yaf_forum_moderators' stored procedure takes more than 30 seconds to load (see debug info below), which makes the default page load for a long time..
When i execute the procedure with an empty value, i see we retrieve our 315 forums with the same Moderator ID, same ModeratorDisplayName etc.
here is an example :
ForumID, ForumName, ModeratorID, ModeratorName, ModeratorEmail, ModeratorAvatar, ModeratorAvatarImage, ModeratorDisplayName, Style, IsGroup
474 'Les mamans d'Avril 2013' 11 Moderateurs ' ' ' ' 0 Moderateurs ' ' 1
475 'Les mamans de Mai 2013' 11 Moderateurs ' ' ' ' 0 Moderateurs ' ' 1
So my questions are :
Do you think that transforming some forums into subforums can make the page load faster ?
And should I, can i optimize the stored procedure ? if so, any idea how ?
Debug info :
11 SQL Queries: 33,465 Seconds (98,85% of Total Page Load Time).
[dbo].[yaf_shoutbox_getmessages]: 0,001
: 0,002
[dbo].[yaf_smiley_listunique]: 0,007
EXEC [dbo].[yaf_forum_moderators] @0: 31,982
EXEC [dbo].[yaf_category_list] @0,@1: 0,001
[dbo].[yaf_forum_listread]: 0,567
[dbo].[yaf_topic_latest]: 0,634
[dbo].[yaf_active_list]: 0,025
[dbo].[yaf_active_stats]: 0,001
[dbo].[yaf_user_list]: 0,120
[dbo].[yaf_user_list]: 0,125
We're using .NET frk 4.0 and testing on a virtual machine at the moment.
Here is the stored procedure :
select
ForumID = a.ForumID,
ForumName = f.Name,
ModeratorID = a.GroupID,
ModeratorName = b.Name,
ModeratorEmail = '',
ModeratorAvatar = '',
ModeratorAvatarImage = CAST(0 as bit),
ModeratorDisplayName = b.Name,
Style = case(@StyledNicks)
when 1 then b.Style
else '' end,
IsGroup=1
from
[dbo].[yaf_Forum] f WITH(NOLOCK)
INNER JOIN [dbo].[yaf_ForumAccess] a WITH(NOLOCK)
ON a.ForumID = f.ForumID
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) ON b.GroupID = a.GroupID
INNER JOIN [dbo].[yaf_AccessMask] c WITH(NOLOCK) ON c.AccessMaskID = a.AccessMaskID
where
(b.Flags & 1)=0 and
(c.Flags & 64)<>0
union all
select
ForumID = access.ForumID,
ForumName = f.Name,
ModeratorID = usr.UserID,
ModeratorName = usr.Name,
ModeratorEmail = usr.Email,
ModeratorAvatar = ISNULL(usr.Avatar, ''),
ModeratorAvatarImage = CAST((select count(1) from [dbo].[yaf_User] x where x.UserID=usr.UserID and AvatarImage is not null)as bit),
ModeratorDisplayName = usr.DisplayName,
Style = case(@StyledNicks)
when 1 then usr.UserStyle
else '' end,
IsGroup=0
from
[dbo].[yaf_User] usr WITH(NOLOCK)
INNER JOIN (
select
UserID = a.UserID,
ForumID = x.ForumID,
ModeratorAccess = MAX(ModeratorAccess)
from
[dbo].[yaf_vaccessfull] as x WITH(NOLOCK)
INNER JOIN [dbo].[yaf_UserGroup] a WITH(NOLOCK) on a.UserID=x.UserID
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) on b.GroupID=a.GroupID
WHERE
ModeratorAccess <> 0 AND x.AdminGroup = 0
GROUP BY a.UserId, x.ForumID
) access ON usr.UserID = access.UserID
JOIN [dbo].[yaf_Forum] f WITH(NOLOCK)
ON f.ForumID = access.ForumID
JOIN [dbo].[yaf_Rank] r
ON r.RankID = usr.RankID
where
access.ModeratorAccess<>0
order by
IsGroup desc,
ModeratorName asc
Thank you all for your precious help !