Full text search "Contains" is slower than "Like%"

Question

I have a problem with full-text indexing on SQL Server 2008 x64.

I have three tables:

Table A with 90,000 rows

TableB with 12,000,000 rows

TableC with 22,000,000 rows

I created FTS directories with autopopulation.

When I requested TableA:

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

I see 11,000 entries in less than 1 second

But when I query TableB or TableC with the same query, I see 250 records in 2 seconds. Obviously, this is very slow.

A query with "like%" instead of "contains" takes less than 1 second for the same tables.

Could the problem be due to large tables B and C? Table A was successfully requested.

Perhaps these tables take longer to index? (but they have been indexing (filling out) for 3 days already)

Some information:

For tables B and C, I always see “Population Status = Processing Notifications” (9)

The "TableFulltextDocsProcessed" property always increases

(My SQL Server has a mirrored instance.)

Answer 1

I do not know if your query really contains a full-text index. I think he should do a full table scan. Because I understood full-text index words and dictionary words in different languages. Your similar request

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

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

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

compared with

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

In this quote, they talk a lot about words, not about symbols. SQL Server 2005 Full Text Search: Internal and Improvements

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

So I'm wondering if j * works if the phrase: 'j' should be a word in languages ​​that use the full text. see CONTAINS (Transact-SQL)

Match words or phrases starting with the specified text. Bind the prefix term in double quotation marks (") and add an asterisk () before the end of the quotation mark so that all text starting with a simple term appears before the asterisk. The article should be specified as follows: CONTAINS (column, '" text "'). Asterisk matches zero, one or more characters (from the root of a word or words in a word or phrase)

What do the execution plans look like?