I've been working on a Community Server project where we're migrating a customer from an existing platform into Community Server 2007. There are over 260,000 posts in their forums. I ran into a performance issue with the Search Barrel provider. The stored proc was taking a wee bit too long to scan through the posts.
I messed around with the stored procedure cs_SearchBarrel_Search and came up with what should be a pretty good performance improvement. The original stored proc took 38 seconds to execute a particular search. With this version, it takes 12 seconds.
The key changes are to a) set a primary key on a temporary table used to build the list of search results and b) join fewer records together to build the final result set.
I'm posting the updated stored proc here as well as to the CS forums.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[cs_SearchBarrel_Search] (
@SearchSQL NText,
@PageIndex int = 0,
@PageSize int = 25,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
DECLARE @StartTime datetime
DECLARE @RowsToReturn int
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @Count int
DECLARE @TotalRecords int
-- Used to calculate cost of query
SET @StartTime = GetDate()
-- Set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
--SET ROWCOUNT @RowsToReturn
-- Calculate the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the results in
CREATE TABLE #SearchResults
(
IndexID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
PostID int,
SectionID int,
Weight int,
PostDate datetime
)
-- Fill the temp table
INSERT INTO #SearchResults (PostID, SectionID, Weight, PostDate)
exec (@SearchSQL)
SET @TotalRecords = @@rowcount
SET ROWCOUNT @RowsToReturn
-- SELECT actual search results from this table
SELECT
P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.Subject, P.PostDate,
P.FormattedBody, P.IPAddress, P.PostType, P.SettingsID, P.UserTime,
P.ApplicationPostType, P.PostName, P.UserTime,
AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
S.ApplicationKey, S.GroupID, S.ApplicationType, S.Name as SectionName
FROM
cs_Sections S
JOIN #SearchResults R on R.SectionID = S.SectionID
JOIN cs_Posts P on P.PostID = R.PostID
WHERE
R.IndexID > @PageLowerBound AND
R.IndexID < @PageUpperBound AND
P.SettingsID = @SettingsID
ORDER BY IndexID
DROP Table #SearchResults
SELECT @TotalRecords
SELECT Duration = GetDate() - @StartTime
END