title: FreeTextTable bad performance with SQL Server 2008
body: <p>My index is populated with 12 millions of rows from DatasSearch_fr table</p>
  
  <p>Fields are :</p>
  
  <pre><code>[Id] [int] IDENTITY(1,1) NOT NULL,
  [Data] [nvarchar](max) NOT NULL,
  [DataId] [varchar](200) NOT NULL,
  [DataTypeId] [int] NOT NULL
  </code></pre>
  
  <p>By using FREETEXTTABLE like this:</p>
  
  <pre><code>SELECT * FROM FREETEXTTABLE(DatasSearch_fr, (Data), 'din', LANGUAGE 1036) AS FT
  </code></pre>
  
  <p>Query returns 12 000 rows instantly</p>
  
  <p>But by using  FREETEXTTABLE like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Query returns 4400 rows in 10 or 15 seconds...</p>
  
  <p>Problem for me is not 4400 rows returned, this point is logically dûe to <code>Max(rank)</code> and <code>Group by</code>...But the 10 or 15 seconds seem very too much and this slowest response time is not always case with all keywords searched.</p>
  
  <p>Do you know a way to improve this response time ?</p>
  
  <p>Thanks for your help,
  Sébastien</p>