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,