SQL Server 2008 extremely slow using FTS on joined tables

Question

I have two tables

Product (id int, brandid int,Name nvarchar(1000)) 2+ million rows 
Brand (id int,name nvarchar(1000)) 20k rows

FullText index is enabled on both table's name field.

If I do a search such as

SELECT count(*) 
FROM   Product p join Brand b
         on p.BrandID = b.ID
WHERE  contains(b.name,'calvin')

Runs super fast (less than a second). Same result if ran against p.name field.

But this query

SELECT count(*) 
FROM   Product p join Brand b
         on p.BrandID = b.ID
WHERE  contains(b.name,'calvin')
       OR
       contains(p.Name,'calvin')

takes over a minute (several minutes actually). If OR is changed to AND it is also super fast.

I cannot use unions or containstable since I'm using nhibernate.

Answer 1

I recommend you read up SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned.

Any query with OR is a possible source of performance problems. An OR between the two tables of a join, that is like an invitation for disaster since the optimizer has basically lost any information how to optimize the join. Throw in full text condition, which will make cardinaility prediction (number of rows that match the condition) a wild guess at best and you got yourself a perfect storm.

Get rid of the OR. Period. Modify your requirements, discard the middle man (ORM layers).