SQL Server Full Text Search Very Slow

I have a stored procedure that searches a table which has about 200000+ rows with full text FREETEXT.

Here is the basics of it:

declare @searchKey varchar(150)
if @searchKey Is Null OR LEN(@searchKey)=0
    Set @searchKey='""';
Set @searchKey='car';
declare @perPage int
Set @perPage=40
declare @pageNo int
Set @pageNo=1

declare @startIndex int,@endIndex int;
Set @startIndex=@perPage*@pageNo-@perPage+1;
Set @endIndex=@perPage*@pageNo;

Select totalItems
--i pull other colums as well
from (
    Select Row_Number() over(order by CreateDate DESC) As rowNumber
,COUNT(*) OVER() as totalItems
--other columns are pulled as well
from MyTable P 
    Where 
@searchKey='""'
    OR FreeText((P.Title,P.Description),@searchKey)
) tempData
--where rowNumber>=@startIndex AND rowNumber<=@endIndex
where 
rowNumber>=CASE WHEN @startIndex>0 AND @endIndex>0 THEN @startIndex ELSE rowNumber END
AND rowNumber<=CASE WHEN @startIndex>0 AND @endIndex>0 THEN @endIndex ELSE rowNumber END
order by rowNumber

The problem is its running slower then i would like it. Its taking about 3 seconds to load the page. Same page was loading in less then 1 sec when i was using like operator.

Answers


Need Your Help

When, if ever, is loop unrolling still useful?

performance language-agnostic optimization micro-optimization

I've been trying to optimize some extremely performance-critical code (a quick sort algorithm that's being called millions and millions of times inside a monte carlo simulation) by loop unrolling.

MVC routing with “optional” routes

c# asp.net-mvc asp.net-mvc-routing

I'm a bit baffled by how MVC is figuring out my routing details. Let me see if I can explain this right.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.