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.