i just discovered this too,
there a stored procedure called "yaf_forum_updatelastpost" that is the main cause.
it's originally like this :
ALTER procedure [dbo].[yaf_forum_updatelastpost](@ForumID int) as
begin
update [dbo].[yaf_Forum] set
LastPosted = (select top 1 y.Posted from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
LastTopicID = (select top 1 y.TopicID from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
LastMessageID = (select top 1 y.MessageID from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
LastUserID = (select top 1 y.UserID from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
LastUserName = (select top 1 y.UserName from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
LastUserDisplayName = (select top 1 y.UserDisplayName from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc)
where ForumID = @ForumID
end
We have 2 problems, first it doesn't update the "LastUserName" or the same pre calculated fields,
and it's too long to execute and doesn't seems to work...
So i changed it this way first, to look at the "lastuser" etc.. kind of fields, without doing those "INNER JOIN" on the messages:
update [dbo].[yaf_Forum] set
LastPosted = (select top 1 x.LastPosted
from [dbo].[yaf_Topic] x with(nolock)
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),[/indent][/indent]
LastTopicID = (select top 1 x.TopicID
from [dbo].[yaf_Topic] x with(nolock)
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastMessageID = (select top 1 x.LastMessageID
from [dbo].[yaf_Topic] x with(nolock)
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastUserID = (select top 1 x.LastUserID
from [dbo].[yaf_Topic] x with(nolock)
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastUserName = (select top 1 x.LastUserName
from [dbo].[yaf_Topic] x with(nolock)
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastUserDisplayName = (select top 1 x.LastUserDisplayName
from [dbo].[yaf_Topic] x with(nolock)
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc)
where ForumID = @ForumID
Now as you said, we need the have those subforums and parent forums updated correctly, so i changed it again this way
to udpate the current forum with the last stats from the latest sub forum and then update each sub forum individually
IF NOT EXISTS(SELECT 1 FROM yaf_Forum WHERE ParentID=@ForumID)
update [dbo].[yaf_Forum] set
LastPosted = (select top 1 x.LastPosted from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastTopicID = (select top 1 x.TopicID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastMessageID = (select top 1 x.LastMessageID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastUserID = (select top 1 x.LastUserID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastUserName = (select top 1 x.LastUserName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
LastUserDisplayName = (select top 1 x.LastUserDisplayName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc)
where ForumID = @ForumID
ELSE
BEGIN
DECLARE @ChildID int
SET @ChildID = (SELECT TOP(1) ForumID FROM yaf_Forum WHERE ParentID=@ForumID ORDER BY LastPosted DESC)
--Update Current Forum last stats
UPDATE [dbo].[yaf_Forum] SET
LastPosted = (select top 1 x.LastPosted from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc),
LastTopicID = (select top 1 x.TopicID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc),
LastMessageID = (select top 1 x.LastMessageID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc),
LastUserID = (select top 1 x.LastUserID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc),
LastUserName = (select top 1 x.LastUserName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc),
LastUserDisplayName = (select top 1 x.LastUserDisplayName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc)
WHERE ForumID = @ForumID
--Update Children Forums last stats
DECLARE @TblForum TABLE(ForumdID int)
INSERT INTO @TblForum
SELECT ForumID
FROM yaf_Forum
WHERE ParentID=@ForumID
WHILE EXISTS(SELECT 1 FROM @TblForum)
BEGIN
DECLARE @ID int
EXEC [yaf_forum_updatelastpost] @ID
DELETE FROM @TblForum WHERE ForumdID = @ID
END
END
I've tested and it seems to work fine.
it also helps to load the default forum page faster when selecting the last User,message etc.. values.
On the main SP (yaf_forum_listread), we have this :
LastUser = IsNull(t.LastUserName,(select x.[Name] from [dbo].[yaf_User] x with(nolock) where x.UserID=t.LastUserID)),
which is fine but too slow when all the "lastUser" kind of fields are always empty...
It's too bad that yaf isn't optimized yet for larger databases.
Tell me what you think, i hope it will help others looking at this