Full Text Search "Contains" is slower than "Like %"

Question

I have an issue with Full-Text Indexing on SQL Server 2008 x64.

I have three tables:

TableA with 90 000 rows

TableB with 12 000 000 rows

TableC with 22 000 000 rows

I created FTS catalogs with autopopulation.

When I queried TableA :

Select * from TableA where Contains(field1, '"j*"')

I see 11000 records less then 1 second

But when I queried TableB or TableC with same query I see 250 records in 2 seconds. It is obviously very slow.

Query with "like %" instead of "contains" executes less then 1 second for the same tables.

Can the problem exist because of large tables B and C? TableA queried successfully.

Maybe those tables need more time for indexing? (but they are indexing(populating) 3 days already)

Some details:

For tables B and C I always see "Population Status = Processing notifications" (9)

Property "TableFulltextDocsProcessed" always increases

(My SQL Server have a mirrored instance.)

Answer 1

I do not know if your contains query really use the fulltext index. I think it has to do a full table scan. As I have understood the full-text index index words and word stem in different languages. Your like query

Select * from TableA where Contains(field1, '"j*"')

only has the char 'j' in it if you did the same search with

Select field1 from TableA where Contains(field1, 'fish')

compared to

Select field1 from TableA where field1 like '%fish%'

In this quote they talk a lot about words not characters. SQL Server 2005 Full-Text Search: Internals and Enhancements

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language.

So I am wondering if the j* works if the phrase: 'j' has to be a word in the languages that the fulltext is used with.. see CONTAINS (Transact-SQL)

Specifies a match of words or phrases beginning with the specified text. Enclose a prefix term in double quotation marks ("") and add an asterisk () before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. The clause should be specified this way: CONTAINS (column, '"text"'). The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase)

What do the execution plans look like?