Query cost not reliable when using FullText search

· Uncategorized

I activated fulltext search on a database and wanted to test following 3 queries:

SELECT c1 as [key] FROM [dbo].[TableFullTextSearch] WHERE [DocContent] LIKE '%uncurbed%';
SELECT c1 as [key] FROM [dbo].[TableFullTextSearch] WHERE CONTAINS([DocContent], 'uncurbed');
SELECT [key] FROM CONTAINSTABLE ([TableFullTextSearch], [DocContent], 'uncurbed');

A Clustered index was put on [c1], a fulltext index was put on [DocContent].
Results were as expected, the query using LIKE generated a clustered index scan, the other two used the fulltext index.
Elapse time according to STATISTICS TIME showed following:

  • 5895 ms using LIKE
  • 0 ms using CONTAINS
  • 0 ms using CONTAINSTABLE

But when investigating the query execution plan, the query cost relative to the bach was as follows:

  • 23% using LIKE
  • 46% using CONTAINS
  • 31% using CONTAINSTABLE

Conclusion: ignore executionplan when using fulltext search?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: