sugession: Tuning 'Forum_Thread_GetAll' - Forum Module [Lead: Chris Paterra]

Forum DotNetNuke® Pro... Forum Module [L... sugession: Tuning 'Forum_Thread_GetAll'

6/6/2008 3:57 AM

	Yoshihiro Kawabata
4 posts

sugession: Tuning 'Forum_Thread_GetAll'

Hello,

Stored Procedure 'Forum_Thread_GetAll' turning idea.
After rewrite this, This stored Procedure's exectue time became 50 times faster on our DNN site.

Test Environment:

DotNetNuke: 4.8.3
Forums: 04.04.03

Rewrited 'Forums_Thread_GetAll'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Forum_Thread_GetAll]
    (
      @ModuleID INT,
      @ForumID INT,
      @PageSize INT,
      @PageIndex INT,
      @Filter NVARCHAR(500),
      @PortalID INT
    )
AS
    DECLARE @sql NVARCHAR(2000)
    DECLARE @PageLowerBound INT
-- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex

-- Create a temp table to store the select results
    CREATE TABLE #PageIndex
        (
          IndexID INT IDENTITY(1, 1)
                      NOT NULL,
          ThreadID INT
        )

IF @ForumID IS NULL
SELECT @ForumID = -1
-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table

-- [ NEW: 2008/06/04 kawabata
    SELECT @sql = 'INSERT INTO #PageIndex (ThreadID)' + ' SELECT T.ThreadID' + ' FROM dbo.Forum_Threads T' + ' WHERE T.ThreadID IN' +
'   (SELECT TOP ' + CONVERT(VARCHAR, @PageSize + @PageLowerBound) + ' T.ThreadID' + '     FROM dbo.Forum_Threads T' +
'     INNER JOIN dbo.Forum_Posts FP ON T.ThreadID = FP.ThreadID' + '     INNER JOIN dbo.Forum_Posts LP ON T.ThreadID = LP.ThreadID' +
'     INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID' + '     INNER JOIN dbo.Forum_Groups FG ON F.GroupID = FG.GroupID' +
'     WHERE FG.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) + '     AND (T.ForumID = ' + CONVERT(VARCHAR, @ForumID) + ' OR (' + CONVERT(VARCHAR, @ForumID) + ' = -1 AND F.PublicView = 1 AND T.IsPinned = 0))' +
'     AND LP.PostID = T.LastPostedPostID AND FP.PostID = T.ThreadID' +
'     AND (FP.IsApproved = 1) AND (FP.IsLocked = 0) ' + @Filter +
'     ORDER BY T.IsPinned DESC, LP.CreatedDate DESC)'
-- ] NEW: 2008/06/04 kawabata
-- [ OLD: 2008/06/04 kawabata
--    SELECT @sql = 'INSERT INTO #PageIndex (ThreadID)' + ' SELECT T.ThreadID' + ' FROM dbo.Forum_Threads T' + ' WHERE T.ThreadID IN' +
--'   (SELECT TOP ' + CONVERT(VARCHAR, @PageSize) + ' T.ThreadID' + '     FROM dbo.Forum_Threads T' +
--'     INNER JOIN dbo.Forum_Posts FP ON T.ThreadID = FP.ThreadID' + '     INNER JOIN dbo.Forum_Posts LP ON T.ThreadID = LP.ThreadID' +
--'     INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID' + '     INNER JOIN dbo.Forum_Groups FG ON F.GroupID = FG.GroupID' +
--'     WHERE FG.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) + '     AND (T.ForumID = ' + CONVERT(VARCHAR, @ForumID) + ' OR (' + CONVERT(VARCHAR, @ForumID) + ' = -1 AND F.PublicView = 1 AND T.IsPinned = 0))' +
--'     AND LP.PostID = T.LastPostedPostID AND FP.PostID = T.ThreadID' +
--'     AND (FP.IsApproved = 1) AND (FP.IsLocked = 0) ' + @Filter + '     AND T.ThreadID NOT IN' +
--'       (SELECT TOP ' + CONVERT(VARCHAR, @PageLowerBound) + ' T.ThreadID' +
--'          FROM dbo.Forum_Threads T' +
--'          INNER JOIN dbo.Forum_Posts FP ON T.ThreadID = FP.ThreadID' +
--'          INNER JOIN dbo.Forum_Posts LP ON T.ThreadID = LP.ThreadID' +
--'          INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID' +
--'          INNER JOIN dbo.Forum_Groups FG ON F.GroupID = FG.GroupID' +
--'          WHERE FG.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) +
--'          AND (T.ForumID = ' + CONVERT(VARCHAR, @ForumID) + ' OR (' + CONVERT(VARCHAR, @ForumID) + ' = -1 AND F.PublicView = 1 AND T.IsPinned = 0))' +
--'          AND LP.PostID = T.LastPostedPostID AND FP.PostID = T.ThreadID' +
--'          AND (FP.IsApproved = 1) AND (FP.IsLocked = 0) ' + @Filter +
--'          ORDER BY T.IsPinned DESC, LP.CreatedDate DESC)' + ' ORDER BY T.IsPinned DESC, LP.CreatedDate DESC)'
-- ] OLD: 2008/06/04 kawabata
PRINT @sql
EXEC ( @sql )

-- [ ADD: 2008/06/04 kawabata
DELETE FROM #PageIndex
WHERE IndexID <= @PageLowerBound
-- ] ADD: 2008/06/04 kawabata

    SELECT FP.Subject,
            FP.Body,
            FP.CreatedDate,
     FP.UserID As StartedByUserID,
            LP.CreatedDate AS LastPostedDate,
            T.ThreadID,
            T.Views,
            T.ForumID,
            T.LastPostedPostID AS LastApprovedPostID,
            T.ObjectID,
            T.IsPinned,
            T.PinnedDate,
            FP.IsClosed,
            T.ThreadStatus,
            T.AnswerPostID,
            T.Replies,
            ISNULL(( SELECT COUNT(TR.ThreadID)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE TR.ThreadID = T.ThreadID
                   ), 0) AS RatingCount,
            ISNULL(( SELECT AVG(TR.Rate)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE TR.ThreadID = T.ThreadID
                   ), 0) AS Rating
    FROM    dbo.Forum_Threads T
            INNER JOIN #PageIndex PageIndex ON T.ThreadID = PageIndex.ThreadID
            INNER JOIN dbo.Forum_Posts FP ON T.ThreadID = FP.PostID
            INNER JOIN dbo.Forum_Posts LP ON T.LastPostedPostID = LP.PostID
    ORDER BY T.IsPinned DESC,
            LP.CreatedDate DESC

SET @sql = 'SELECT COUNT (DISTINCT T.ThreadID) AS TotalRecords' +
' FROM dbo.Forum_Threads T ' + ' INNER JOIN dbo.Forum_Posts FP ON T.ThreadID = FP.ThreadID' +
' INNER JOIN dbo.Forum_Posts LP ON T.ThreadID = LP.ThreadID' + ' INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID' +
' INNER JOIN dbo.Forum_Groups FG ON F.GroupID = FG.GroupID' + ' WHERE FG.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) +
' AND (T.ForumID = ' + CONVERT(VARCHAR, @ForumID) + ' OR (' + CONVERT(VARCHAR, @ForumID) + ' = -1 AND F.PublicView = 1 AND T.IsPinned = 0))' +
' AND LP.PostID = T.LastPostedPostID' + ' AND FP.PostID = T.ThreadID' + ' AND (FP.IsApproved = 1) AND (FP.IsLocked = 0) ' + @Filter

EXEC ( @sql )

Regards,

Yoshihiro Kawabata
dotnetnuke.jp