title: SQL Server Full Text Search Very Slow
body: <p>I have a stored procedure that searches a table which has about 200000+ rows with full text FREETEXT.</p>
  
  <p>Here is the basics of it:</p>
  
  <pre><code>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&gt;=@startIndex AND rowNumber&lt;=@endIndex
  where 
  rowNumber&gt;=CASE WHEN @startIndex&gt;0 AND @endIndex&gt;0 THEN @startIndex ELSE rowNumber END
  AND rowNumber&lt;=CASE WHEN @startIndex&gt;0 AND @endIndex&gt;0 THEN @endIndex ELSE rowNumber END
  order by rowNumber
  </code></pre>
  
  <p>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.</p>