SQL Server 2008 全文搜索 – 慢 [英] SQL Server 2008 Full Text Search – SLOW

问题描述

我在 SQL Server 2008 SP2 中使用 asp.net

I'm using asp.net with SQL Server 2008 SP2

我有一张启用全文搜索的表格.

I have one table with Full Text Search Enabled.

我的硬件是:2GHZ 四核，4GB 内存，SAS 硬盘.

My Hardware is: 2GHZ Quad-Core, 4GB RAM, SAS HDD.

该表约有500万条记录，其结构为:

Pages table:

ID (INT)
GroupID (INT)
GroupStart (bit)
Col1 varchar(900)
Col2 nvarchar(450)
Col3 nvarchar(450)
Col4 nvarchar(450)
Col5 nvarchar(450)

我在主键 ID 上有一个 fts 索引.

i have a fts index on the primary key ID.

我正在使用以下查询来搜索表:

I'm using the following query to search the table:

SELECT * FROM (  
SELECT * , ROW_NUMBER()  OVER(  ORDER BY  KEY_TBL.Rank DESC  ) AS RowNumber , COUNT(*) OVER() as TotalRows  
FROM Pages p  
INNER JOIN  
CONTAINSTABLE(Pages, *,N' FORMSOF (INFLECTIONAL, movies)') AS KEY_TBL  
ON p.ID = KEY_TBL.[KEY]  
WHERE  (p.GroupID IS NULL OR p.GroupStart = 1) 
) LS  
WHERE RowNumber BETWEEN 0 AND 10  
ORDER BY RowNumber ASC;

如果总行数约为 500，则查询需要 1 秒完成.

The query takes 1s to complete if total rows is about 500.

如果总行数约为 10,000，则需要 5 秒才能完成.

and takes 5s to complete if total rows is about 10,000.

如果总行数约为 100,000，则需要 60 秒才能完成.

and takes 60s to complete if total rows is about 100,000.

知道为什么查询需要这么长时间吗?

Any idea why is the query taking so long?

我的查询、sql server 有问题还是硬件问题?

Is there something wrong in my query , sql server or is it a hardware problem?

推荐答案

考虑限制 CONTAINSTABLE 返回的匹配数量 -> CONTAINSTABLE(Pages, *,N' FORMSOF (INFLECTIONAL, movies)', 100 )

Consider restricting number of matches returned by CONTAINSTABLE -> CONTAINSTABLE(Pages, *,N' FORMSOF (INFLECTIONAL, movies)', 100 )

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