Qwilm!2

Working in a database is a nice way to pass time

I spent a bit of time this afternoon rewriting our forum search stored procedure. The original is so bad I’m not going to post it, but the new sp is pretty solid. I’m getting 5 seconds and faster results without full text indexes and a little over 2 million records combined. I cut close to 30 seconds of bloat out of the execution by handling the fields better. Yay experience!

Now, if you at it and think “only 500 records?!” let me explain. That grabs on average two years worth of threads. There’s no point to in grabbing more.

CREATE PROCEDURE usp_Forums_Threads_Search
(@Forum int, @Search nvarchar(255)) AS

SET NOCOUNT ON

CREATE TABLE #ForumSearch (
[ID] int IDENTITY(1, 1) NOT NULL,
Thread int,
Username nvarchar(255),
Title nvarchar(255),
Replies int,
[Timestamp] datetime,
ReplyUser int,
ReplyTimestamp datetime
)

/* Searching Threads table */
INSERT INTO #ForumSearch (Thread, Username, Title, Replies, [Timestamp], ReplyUser, ReplyTimestamp)
SELECT TOP 500 dbo.Threads.Thread_ID, dbo.AXUsers.Username, dbo.Threads.Title, dbo.Threads.Replies, dbo.Threads.[Timestamp], dbo.Threads.ReplyUser, dbo.Threads.ReplyTimestamp
FROM dbo.Threads WITH (NOLOCK) INNER JOIN
dbo.AXUsers WITH (NOLOCK) ON dbo.Threads.[User] = dbo.AXUsers.User_ID
WHERE (dbo.Threads.Forum = @Forum) AND dbo.Threads.Title LIKE ('%' + @Search + '%') OR dbo.Threads.Body LIKE ('%' + @Search + '%') AND (dbo.Threads.Active = 1)
ORDER BY ISNULL(dbo.Threads.ReplyTimestamp, dbo.Threads.[Timestamp]) DESC

/* Searching Replies table */
INSERT INTO #ForumSearch (Thread, Username, Title, Replies, [Timestamp], ReplyUser, ReplyTimestamp)
SELECT TOP 500 dbo.Threads.Thread_ID, dbo.AXUsers.Username, dbo.Threads.Title, dbo.Threads.Replies, dbo.Threads.[Timestamp], dbo.Threads.ReplyUser,
dbo.Threads.ReplyTimestamp
FROM dbo.AXUsers WITH (NOLOCK) INNER JOIN
dbo.Threads WITH (NOLOCK) ON dbo.AXUsers.User_ID = dbo.Threads.[User] INNER JOIN
dbo.Replies WITH (NOLOCK) ON dbo.Threads.Thread_ID = dbo.Replies.Thread INNER JOIN
#ForumSearch WITH (NOLOCK) ON dbo.Replies.Thread = #ForumSearch.Thread
WHERE (dbo.Threads.Forum = @Forum) AND (dbo.Replies.Body LIKE '%' + @Search + '%') AND (dbo.Replies.Active = 1) AND (dbo.Replies.Approved = 1) AND (dbo.Replies.Thread <> #ForumSearch.Thread)
ORDER BY ISNULL(dbo.Threads.ReplyTimestamp, dbo.Threads.[Timestamp]) DESC

/* Returning results */
SELECT #ForumSearch.Thread, #ForumSearch.Username, #ForumSearch.Title, #ForumSearch.Replies, #ForumSearch.[Timestamp], dbo.udf_Empty2Null(REPLACE(dbo.AXUsers.Username, 'System Notice', '')) AS ReplyUser, #ForumSearch.ReplyTimestamp
FROM #ForumSearch WITH (NOLOCK) INNER JOIN
dbo.AXUsers WITH (NOLOCK) ON ISNULL(#ForumSearch.ReplyUser, 2) = dbo.AXUsers.User_ID
ORDER BY ISNULL(#ForumSearch.ReplyTimestamp, #ForumSearch.[Timestamp]) DESC

RETURN

DROP TABLE #ForumSearch

SET NOCOUNT OFF
GO

[ 0C ] [ March 19, 2007 ] [ Photography - Work ]

Leave a Reply