SQL Server全文搜索非常慢 [英] SQL Server Full Text Search Very Slow

问题描述

我有一个存储过程，它搜索一个包含全文FREETEXT的大约200000多行的表。

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

以下是它的基础知识：

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

问题是它的运行速度较慢，我希望它。它需要大约3秒钟来加载页面。在我使用like运算符时，相同的页面在少于1秒的时间内加载。

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.

推荐答案

以我的经验，全文索引函数在包含OR运算符的子句中效果不佳。我必须通过调整查询来使用UNION来获得相同的行为。

In my experience, full text index functions do not work well in a clause that contains an "OR" operator. I have had to get the same behavior by adjusting my query to use a UNION. Try this and see if you can get better performance.

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
( 
 select * from
 MyTable A
    Where 
     @searchKey='""'
UNION
select * from MyTable B
    where FreeText((B.Title,B.Description),@searchKey)
) as innerTable

) 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

这篇关于SQL Server全文搜索非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！