Search finally works right!!
Posted: 5/15/2008 6:56:38 PM
By: Comfortably Anonymous
Times Read: 1,784
0 Dislikes: 0
Topic: MessageBase Code Development

Ever since I put in the ability to search messages within MessageBase, it's never worked right. In fact kind of flaky. I'd search for messages containing words that I knew the message contained, and lots of the time I wouldn't find the message at all. If I was lucky, some times I'd find replies to the message, but not the parent message.

It was really confusing and I was thinking that SQL Server 2005 Full-Text Search was just a flaky product.

Well.........

It turned out it was my fault. I feel quite stupid in retrospect.

When you click on Search, it sends your word(s) to a stored procedure running on the database server. It runs, and returns which messages match the search. However, I just found a bug in the code which caused it to drop any message posted by a user who was not logged in (aka Comfortably Anonymous). Which, a lot of the messages I would test the search against happened to be from anonymous users, so I'd never get those messages returned by the search. I fixed the bug, and voila - the searches now seem to work perfectly! :)

For the technically minded out there, I needed to use a LEFT JOIN instead of an INNER JOIN, and had to use the T-SQL ISNULL() function to swap in the words "Comfortably Anonymous" in the case that no user name was found for the message, as happened in the case of an Anonymous User. 

Here's the Before and After code for the stored proc:

BEFORE

SELECT Message.MsgNum, Message.TopicNum, Message.UserNum, Message.MsgTime, Message.Subject, Message.Body, Topic.Subject AS TopicDesc, UserInfo.Name as UserName, Results.Rank
FROM Message
INNER JOIN FREETEXTTABLE(Message, (Body, Subject), @SearchPhrase) AS Results ON Results.[Key] = Message.MsgNum
INNER JOIN Topic ON Message.TopicNum = Topic.TopicNum 
INNER JOIN UserInfo ON Message.UserNum = UserInfo.UserNum
ORDER BY Rank DESC

AFTER (Changes in Green)

SELECT Message.MsgNum, Message.TopicNum, Message.UserNum, Message.MsgTime, Message.Subject, Message.Body, Topic.Subject AS TopicDesc, ISNULL(UserInfo.Name,'Comfortably Anonymous') as UserName, Results.Rank
FROM Message
INNER JOIN FREETEXTTABLE(Message, (Body, Subject), @SearchPhrase) AS Results ON Results.[Key] = Message.MsgNum
INNER JOIN Topic ON Message.TopicNum = Topic.TopicNum
LEFT JOIN UserInfo ON Message.UserNum = UserInfo.UserNum
ORDER BY Rank DESC

Rating: (You must be logged in to vote)