我的索引填充了来自 DatasSearch_fr 表的 1200 万行

字段是:

[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] [nvarchar](max) NOT NULL,
[DataId] [varchar](200) NOT NULL,
[DataTypeId] [int] NOT NULL

像这样使用 FREETEXTTABLE:

SELECT * FROM FREETEXTTABLE(DatasSearch_fr, (Data), 'din', LANGUAGE 1036) AS FT

查询立即返回 12000 行

但是通过像这样使用 FREETEXTTABLE:

SELECT DataId, DataTypeId, MAX(Rank) as Rank FROM DatasSearch_fr
INNER JOIN FREETEXTTABLE(DatasSearch_fr, (Data), 'din', LANGUAGE 1036) AS FT ON FT.[Key] = Id
Group By DataId, DataTypeId

查询在 10 或 15 秒内返回 4400 行...

我的问题不是返回 4400 行，这一点在逻辑上是由于 Max(rank) 和 Group by...但是 10 或 15 秒似乎很太多了，而且这个最慢的响应时间并不总是搜索所有关键字的情况。

您知道改善此响应时间的方法吗？

感谢您的帮助， 塞巴斯蒂安

最佳答案

通过使用

set statistics io on

我有这样的结果:

Table 'DatasSearch_fr'. Scan count 5, logical reads 37861, physical reads 0, read-ahead 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

如果有人有想法。 :)

编辑: 下面，查询执行计划

  |--Parallelism(Gather Streams)
       |--Hash Match(Aggregate, HASH:([DataBase].[dbo].[DatasSearch_fr].[DataId], [DataBase].[dbo].[DatasSearch_fr].[DataTypeId]), RESIDUAL:([DataBase].[dbo].[DatasSearch_fr].[DataId] = [DataBase].[dbo].[DatasSearch_fr].[DataId] AND [DataBase].[dbo].[DatasSearch_fr].[DataTypeId] = [DataBase].[dbo].[DatasSearch_fr].[DataTypeId]) DEFINE:([Expr1007]=MAX(CONVERT(int,[Expr1005],0))))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([DataBase].[dbo].[DatasSearch_fr].[DataId], [DataBase].[dbo].[DatasSearch_fr].[DataTypeId]))
                 |--Hash Match(Inner Join, HASH:(FulltextMatch.[docid])=([DataBase].[dbo].[DatasSearch_fr].[Id]))
                      |--Bitmap(HASH:(FulltextMatch.[docid]), DEFINE:([Bitmap1012]))
                      |    |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]) DEFINE:([Expr1005]=MAX([Expr1004])))
                      |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:(FulltextMatch.[docid]), ORDER BY:(FulltextMatch.[docid] ASC))
                      |              |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid], FulltextMatch.[colid]) DEFINE:([Expr1004]=SUM([Expr1008])))
                      |                   |--Compute Scalar(DEFINE:([Expr1008]=freetexttablerank((0),FulltextMatch.[termfrequency],FulltextMatch.[columnweight]*CONVERT_IMPLICIT(float(53),FulltextMatch.[documentlength],0),FulltextMatch.[columnweight]*CONVERT_IMPLICIT(float(53),FulltextMatch.[avdl],0),FulltextMatch.[termweight])))
                      |                        |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:(FulltextMatch.[docid], FulltextMatch.[colid]))
                      |                             |--Table-valued function
                      |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([DataBase].[dbo].[DatasSearch_fr].[Id]))
                           |--Index Scan(OBJECT:([DataBase].[dbo].[DatasSearch_fr].[IX_DatasSearch_fr_IdNew]),  WHERE:(PROBE([Bitmap1012],[DataBase].[dbo].[DatasSearch_fr].[Id],N'[IN ROW]')))

https://stackoverflow.com/questions/17704379/