Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForumForumTurning: Stored Procedure Turning: Stored Procedure 'Forum_SearchGetResults' in Forum 4.4.3
Previous
 
Next
New Post
7/8/2008 8:25 PM
 

Hello, I turning Stored Procedure 'Forum_SearchGetResults' in Forum 4.4.3.

Turning Point:
  rewrite EXEC(@sql)  to sp_executesql @sql with parameters.

Our Result:
  6 times fast, 40% less reads of keyword search on our forum ( 11,000 posts, 2,300 threads, 33 forums).

New Source:
ALTER PROCEDURE [dbo].[Forum_SearchGetResults]
(
    @Filter  NVARCHAR(500),
    @PageIndex INT,
    @PageSize INT,
    @UserID  INT,
    @ModuleID INT,
    @FromDate DATETIME,
    @ToDate DATETIME,
    @ThreadStatusID INT
)
AS
    -- Create a temp table to store the select results
 CREATE TABLE #PageIndex
    (
     IndexID INT IDENTITY (1, 1) NOT NULL,
     ThreadID INT
    )
    -- Create dynamic SQL to populate temporary table
 DECLARE @sql NVARCHAR(3000)

--  2008/7/9 : Original
-- SELECT  @sql =    'INSERT INTO #PageIndex(ThreadID) SELECT DISTINCT(P.ThreadID) CreatedDate ' +    
--                'FROM dbo.Forum_Posts P ' +
--                'INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID ' +
--                'INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID ' +
--                'INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID ' +       
--                'INNER JOIN dbo.Forum_Posts LP ON T.ThreadID = LP.ThreadID ' +
--                'WHERE P.ThreadID = T.ThreadID AND ' +
--                'T.ForumID = F.ForumID AND ' +       
--                'F.GroupID = G.GroupID ' +
--                'AND P.[CreatedDate] >  ''' + CONVERT(VARCHAR, @FromDate) + ''' ' +
--                'AND P.[CreatedDate] < ''' + CONVERT(VARCHAR, @ToDate) + ''' ' +
--                'AND P.IsApproved = 1 ' +
--                'AND F.IsActive = 1 ' +
--                'AND P.IsLocked = 0 ' +
--                'AND G.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) +
--                'AND ( (F.EnableForumsThreadStatus = 0) ' +
--                'OR (F.EnableForumsThreadStatus = 1 AND T.ThreadStatus = ' + CONVERT(VARCHAR, @ThreadStatusID) + ') ' +
--                'OR (F.EnableForumsThreadStatus = 1 AND -1 = ' + CONVERT(VARCHAR, @ThreadStatusID) + ') ) ' +
--                'AND ( F.PublicView = 1 OR ' +
--                    ' F.ForumID IN ( ' +
--                        ' SELECT ForumID ' +
--                        ' FROM  dbo.Forum_ForumPermission ' +
--                        ' WHERE AllowAccess=1 AND (RoleID IN (SELECT RoleID FROM dbo.UserRoles WHERE UserID = ' + CONVERT(VARCHAR, @UserID) + ' )' +
--                        ' OR EXISTS (SELECT TOP 1 1 FROM dbo.Users WHERE UserID = ' + CONVERT(VARCHAR, @UserID) + '  and IsSuperUser=1))))  ' +
--                @Filter + ' GROUP BY P.ThreadID , P.CreatedDate ORDER BY P.ThreadID DESC '
-- EXEC(@sql)
-- ]

-- [ 2008/7/9 NEW
     SELECT  @sql =    'INSERT INTO #PageIndex(ThreadID) SELECT DISTINCT(P.ThreadID) CreatedDate ' +    
                'FROM dbo.Forum_Posts P ' +
                'INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID ' +
                'INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID ' +
                'INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID ' +       
                'INNER JOIN dbo.Forum_Posts LP ON T.ThreadID = LP.ThreadID ' +
                'WHERE P.ThreadID = T.ThreadID AND ' +
                'T.ForumID = F.ForumID AND ' +       
                'F.GroupID = G.GroupID ' +
                'AND P.[CreatedDate] > @FromDate ' +
                'AND P.[CreatedDate] < @ToDate ' +
                'AND P.IsApproved = 1 ' +
                'AND F.IsActive = 1 ' +
                'AND P.IsLocked = 0 ' +
                'AND G.ModuleID = @ModuleID ' +
                'AND ( (F.EnableForumsThreadStatus = 0) ' +
                'OR (F.EnableForumsThreadStatus = 1 AND T.ThreadStatus = @ThreadStatusID) ' +
                'OR (F.EnableForumsThreadStatus = 1 AND -1 = @ThreadStatusID)) ' +
                'AND ( F.PublicView = 1 OR ' +
                    ' F.ForumID IN ( ' +
                        ' SELECT ForumID ' +
                        ' FROM  dbo.Forum_ForumPermission ' +
                        ' WHERE AllowAccess=1 AND (RoleID IN (SELECT RoleID FROM dbo.UserRoles WHERE UserID = @UserID )' +
                        ' OR EXISTS (SELECT TOP 1 1 FROM dbo.Users WHERE UserID = @UserID  and IsSuperUser=1))))  ' +
                @Filter +
                ' GROUP BY P.ThreadID , P.CreatedDate ' +
                ' ORDER BY P.ThreadID DESC '

 EXEC sp_executesql @sql,
     N' @FromDate DATETIME, @ToDate Datetime, @ModuleID int, @ThreadStatusID int, @UserID int',
        @FromDate, @ToDate, @ModuleID, @ThreadStatusID, @UserID
-- ]

    -- All of the rows are inserted into the table - now select the correct subset
 DECLARE @PageLowerBound INT
 DECLARE @PageUpperBound INT
 DECLARE @RecordCount INT
    -- Set the page bounds
 SET @PageLowerBound = @PageSize * @PageIndex
 SET @PageUpperBound = @PageLowerBound + @PageSize + 1
 SELECT @RecordCount = COUNT(*) FROM #PageIndex

    -- Select the data out of the temporary table
 SELECT
     PageIndex.ThreadID,
     P.[Subject],
     P.[CreatedDate],
     T.[ForumID],
     T.[Replies],
     T.[Views],
     F.[Name] AS ForumName,
     RecordCount = @RecordCount,
     T.IsPinned,
     T.LastPostedPostID,
     T.ThreadStatus,
     ISNULL((SELECT COUNT([ThreadID]) FROM dbo.Forum_ThreadRating  WHERE ThreadID = PageIndex.ThreadID), 0) AS RatingCount,
     ISNULL((SELECT AVG([Rate]) FROM dbo.Forum_ThreadRating  WHERE ThreadID = PageIndex.ThreadID), 0) AS Rating
 FROM
     dbo.Forum_Posts P
     INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID
     INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID
     INNER JOIN #PageIndex PageIndex ON T.ThreadID = PageIndex.ThreadID
 WHERE
     PageIndex.IndexID > @PageLowerBound AND
     PageIndex.IndexID < @PageUpperBound AND
     PageIndex.ThreadID = P.ThreadID AND 
     P.[ThreadID] = T.[ThreadID] AND
     P.[CreatedDate] > @FromDate AND
     P.[CreatedDate] < @ToDate AND
     T.[ForumID] = F.[ForumID] AND
     F.[IsActive] = 1 AND
        (P.[CreatedDate] = (SELECT MAX([CreatedDate]) FROM dbo.Forum_Posts WHERE ThreadID = PageIndex.ThreadID)) AND
        ( (F.EnableForumsThreadStatus = 0) OR
        (F.EnableForumsThreadStatus = 1 AND T.ThreadStatus = @ThreadStatusID) OR
        (F.EnableForumsThreadStatus = 1 AND -1 = @ThreadStatusID) ) AND
        (F.PublicView = 1 OR
     F.ForumID IN (
         SELECT ForumID
         FROM dbo.Forum_ForumPermission
         WHERE AllowAccess=1 AND (RoleID IN (SELECT RoleID FROM dbo.UserRoles WHERE UserID = @UserID)
         OR EXISTS (SELECT TOP 1 1 FROM dbo.Users WHERE UserID = @UserID AND IsSuperUser=1))))    
 ORDER BY
     P.CreatedDate DESC
----

Regards,

 
New Post
7/9/2008 4:03 PM
 

Is this a correction or ehancement of your first post?

 
New Post
7/9/2008 8:37 PM
 

Yosh, I have rewritten the sproc because what is there for the initial select is wrong. I did include the "rewrite EXEC(@sql)  to sp_executesql @sql with parameters" but without the parameters because I am still doing the convert in the select @sql statement. I did some profiling and found no difference but the sp_executesql is faster in theory.

 

ALTER

PROCEDURE [dbo].[dnn_Forum_SearchGetResults]

(

@Filter

NVARCHAR(500),

@PageIndex

INT,

@PageSize

INT,

@UserID

INT,

@ModuleID

INT,

@FromDate

DATETIME,

@ToDate

DATETIME,

@ThreadStatusID

INT

)

AS

 

-- Create a temp table to store the select results

 

 

CREATE TABLE #PageIndex(

IndexID

INT IDENTITY (1, 1) NOT NULL,

CreatedDate

DATETIME NOT NULL,

ThreadID

INT

 

)

 

-- Create dynamic SQL to populate temporary table

 

DECLARE @sql NVARCHAR(2000)

 

 

 

 

 

 

 

 

SELECT @sql = 'INSERT INTO #PageIndex(ThreadID, CreatedDate) SELECT (ThreadID), ' +'(SELECT CreatedDate FROM dbo.dnn_Forum_Threads FT INNER JOIN dbo.dnn_Forum_Posts FP ' +'ON FT.LastPostedPostID = FP.PostID ' +'WHERE FT.ThreadID = PS.ThreadID) As LastPostDate ' +'FROM dbo.dnn_Forum_vw_PostSearch PS ' +'WHERE [CreatedDate] > ''' + CONVERT(VARCHAR, @FromDate) + ''' ' +'AND [CreatedDate] < ''' + CONVERT(VARCHAR, @ToDate) + ''' ' +'AND IsApproved = 1 ' +

 

'AND IsActive = 1 ' +

 

'AND ModuleID = ' + CONVERT(VARCHAR, @ModuleID) +

 

 

 

 

'AND ( (EnableForumsThreadStatus = 0) ' +'OR (EnableForumsThreadStatus = 1 AND ThreadStatus = ' + CONVERT(VARCHAR, @ThreadStatusID) + ') ' +'OR (EnableForumsThreadStatus = 1 AND -1 = ' + CONVERT(VARCHAR, @ThreadStatusID) + ') ) ' +'AND ( PublicView = 1 OR ' +

 

' ForumID IN ( ' +

 

' SELECT ForumID ' +

 

' FROM dbo.dnn_Forum_ForumPermission ' +

 

 

' WHERE AllowAccess=1 AND ' +'(RoleID IN (SELECT RoleID FROM dbo.dnn_UserRoles WHERE UserID = ' + CONVERT(VARCHAR, @UserID) + ' )' +

 

 

 

' OR EXISTS ' +'(SELECT TOP 1 1 FROM dbo.dnn_Users WHERE UserID = ' + CONVERT(VARCHAR, @UserID) +' AND IsSuperUser=1)))) ' +

@Filter

+ 'GROUP BY ThreadID ORDER BY LastPostDate DESC'

 

 

EXEC sp_executesql @sql-- All of the rows are inserted into the table - now select the correct subset

 

DECLARE @PageLowerBound INT

 

DECLARE @PageUpperBound INT

 

DECLARE @RecordCount INT

 

-- Set the page bounds

 

 

 

 

SET @PageLowerBound = @PageSize * @PageIndexSET @PageUpperBound = @PageLowerBound + @PageSize + 1-- Select the data out of the temporary table

 

SELECT

 

(I.ThreadID),

[Subject]

,

 

--[Name],

FP

.[CreatedDate],

T

.[ForumID],

[Replies]

,

[Views]

,

 

(SELECT COUNT(*) FROM #PageIndex) AS RecordCount,

IsPinned

,

LastPostedPostID

,

ThreadStatus

,

 

ISNULL((SELECT COUNT([ThreadID]) FROM dbo.dnn_Forum_ThreadRating WHERE ThreadID = I.ThreadID), 0) AS RatingCount,

 

 

 

 

 

ISNULL((SELECT AVG([Rate]) FROM dbo.dnn_Forum_ThreadRating WHERE ThreadID = I.ThreadID), 0) AS RatingFROM dbo.dnn_Forum_Threads TINNER JOIN #PageIndex I ON T.ThreadID = I.ThreadIDINNER JOIN dbo.dnn_Forum_Posts FP ON T.LastPostedPostID = FP.PostIDWHERE

I

.IndexID > @PageLowerBound AND

I

.IndexID < @PageUpperBound

ORDER

BY CreatedDate DESC


Chris Paterra

Get direct answers to your questions in the Community Exchange.
 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForumForumTurning: Stored Procedure Turning: Stored Procedure 'Forum_SearchGetResults' in Forum 4.4.3


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out