FreeTextTable bad performance with SQL Server 2008

Question

My index is populated with 12 millions of rows from DatasSearch_fr table

Fields are :

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

By using FREETEXTTABLE like this:

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

Query returns 12 000 rows instantly

But by using FREETEXTTABLE like this:

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

Query returns 4400 rows in 10 or 15 seconds...

Problem for me is not 4400 rows returned, this point is logically dûe to Max(rank) and Group by...But the 10 or 15 seconds seem very too much and this slowest response time is not always case with all keywords searched.

Do you know a way to improve this response time ?

Thanks for your help, Sébastien

Answer 1

By using

set statistics io on

I have this result:

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.

If someone have an idea. :)

EDIT: Below, Query Execution Plan

  |--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]')))