大表上的简单有序查询出现奇怪的全文搜索性能问题
我的表有大约7M记录，其结构如下：
create table Locations (LocationID (PK, int, not null),LocationName varchar(100) NOT NULL /*e.g. 'Paris'*/, ItemsNo (int, null), ICode char(1) null /*e.g. 'C'*/, LocationExtended nvarchar(max) null)
HTML版本在这里： “创建表位置（LocationID（PK，int，not null），LocationName varchar（100）NOT NULL，ItemsNo（int，null），ICode char（1）null，LocationExtended nvarchar（max）null）”
我的查询非常简单：
select top 20 LocationName, ItemsNo from Locations where contains(LocationExtended,@SearchTerms) and Icode='C' and ItemsNo is not null order by ItemsNO desc
这里的 HTML 版本：“从包含(LocationExtended,@SearchTerms) 和 Icode='C' 的位置中选择前 20 个 LocationName、ItemsNo，并且 ItemsNo 不为空，按 ItemsNO desc 排序”
当然，LocationExtended 字段有一个全文目录。
现在让我们稍微了解一下参数 @SearchTerms 和索引：
如果我在 LocationID 列上只有聚集索引，则 @SearchTerms='Paris and France' 的执行时间会很快（因为在LocationExtended 字段包含 Paris 和 France 关键字），并且 @SearchTerms='France' 的执行时间较慢（因为对于法国的每个城市/城镇，LocationExtended 字段中有数千条记录）。
换句话说，结果类似于： 对于 @SearchTerms='巴黎和法国' 返回：“巴黎”和“巴黎北部”和“巴黎南部” for @SearchTerms='France' 返回“Paris”、“Nice”、“Marseille”等...
因为我需要这两种方案都快速运行，所以我使用 Icode 在 Locations 表上创建了一个非聚集索引ItemsNO 列和 LocationID 包含列。
通过创建这个新索引，我得到了相反的结果：@SearchTerms='France' 的执行时间超快，但 @SearchTerms='Paris and France' 的执行时间很慢
现在我陷入了这一点。
我提到我正在 Windows 2003 Server 上运行 SQL Server 2008
任何想法将不胜感激。 多谢。
My table has about 7M records and its structure is like follows:
create table Locations (LocationID (PK, int, not null),LocationName varchar(100) NOT NULL /*e.g. 'Paris'*/, ItemsNo (int, null), ICode char(1) null /*e.g. 'C'*/, LocationExtended nvarchar(max) null)
HTML version here:
"create table Locations (LocationID (PK, int, not null),LocationName varchar(100) NOT NULL, ItemsNo (int, null), ICode char(1) null, LocationExtended nvarchar(max) null)"
My query is really simple:
select top 20 LocationName, ItemsNo from Locations where contains(LocationExtended,@SearchTerms) and Icode='C' and ItemsNo is not null order by ItemsNO desc
HTML version here: "select top 20 LocationName, ItemsNo from Locations where contains(LocationExtended,@SearchTerms) and Icode='C' and ItemsNo is not null order by ItemsNO desc"
Of course there is a full-text catalog for the LocationExtended field.
Now let's play a little with the parameter @SearchTerms and with the indexes:
If I only have a clustered Index on the LocationID column I get FAST execution times for @SearchTerms='Paris and France' (because there are a very few records in the LocationExtended field containing both Paris and France keywords) and SLOW execution times for @SearchTerms='France' (because there are thousands of records in LocationExtended field, for every city/town in France).
In other words the results are something like:
for @SearchTerms='Paris and France' returns: "Paris" and "Paris North" and "South Paris"
for @SearchTerms='France' returns "Paris", "Nice", "Marseille",...., etc...
Because I need both scenarios to run fast I created a non clustered index on the Locations table with Icode and ItemsNO columns and LocationID included column.
By making this new index I got the results reversed: super fast execution times for @SearchTerms='France' but slow times for @SearchTerms='Paris and France'
Now I am stuck at this point.
I mention that I am running SQL Server 2008 on Windows 2003 Server
Any ideea would be appreciated.
Thanks a lot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最后这对我来说是有效的：
索引：LocationID 上的聚集索引和 ItemsNO 上的非聚集索引，其中 LocationID 作为包含列
查询：
<代码>选择
前 20 个 * from ( select ROW_NUMBER() over (order by ItemsNO DESC) as RowID,ItemsNO,LocationName
从
地点
在哪里
contains(LocationExtended,@SearchTerms) and ItemsNO>0 and ICode='C') t
这将执行时间从 850 毫秒减少到约 45 毫秒
Finally this worked out for me:
Indexes: clustered Index on the LocationID and nonclustered index on ItemsNO with LocationID as included colums
The query:
selectTOP 20 * from ( select ROW_NUMBER() over (order by ItemsNO DESC) as RowID,ItemsNO,LocationName
FROM
Locations
where
contains(LocationExtended,@SearchTerms) and ItemsNO>0 and ICode='C') t
This reduced execution time from 850 ms to ~ 45 ms