YAFLogo

shide
  • shide
  • 80.6% (Honored)
  • YAF Lover Topic Starter
10 years ago
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 !

Sponsor
bbobb
  • bbobb
  • 100% (Exalted)
  • YAF Developer
10 years ago
I don't know if something is broken now, but the sp should be cached.

It can be loaded a lot of time, because it loads all data on all forums.

Different approaches exixts, but in practice only a single unlucky user gets into the trouble when the cache expires or IIS server restarts. In theory it can be rewritten, but it's a time-consuming and I'm not sure that it's really required.

shide
  • shide
  • 80.6% (Honored)
  • YAF Lover Topic Starter
10 years ago
i don't know if something is broken or not but we have not less than 315 forums,

so the the good thing is as you said, that it only take that much time to load when the app starts,

otherwise it's fine.

Thanks