SQL Server full-text search performance dramatical

Question

Suppose I have two tables tab1, tab2. Full text indexes created on all varchar columns on these two tables. Then issue following SQL:

SELECT *  
  FROM tab1 a 
  JOIN tab2 b on a.ID = b.ID
 WHERE CONTAINS(a.*, @keystring) 
    OR CONTAINS(b.*,@keystring)

It is pretty slowly(almost 30 seconds). But If I issue following SQL:

SELECT * 
  FROM tab1 a 
  JOIN tab2 b on a.ID = b.ID
 WHERE CONTAINS(a.*, @keystring) 

...or:

SELECT *  
  FROM tab1 a 
  JOIN tab2 b on a.ID = b.ID
 WHERE CONTAINS(b.*,@keystring)

The performance is pretty good(less than second)

How to resolve this problem?

Answer 1

have you tried:

SELECT *  
  FROM tab1 a  
  JOIN tab2 b on a.ID = b.ID 
 WHERE CONTAINS(a.*, @keystring)  
UNION 
SELECT *   
  FROM tab1 a  
  JOIN tab2 b on a.ID = b.ID 
 WHERE CONTAINS(b.*,@keystring) 

Or use UNION all if you don't care if there are duplicates where the keystring is both tables.

Look at your execution plans to see the differnce but OR often makes as query run much more slowly.

Answer 2

The real problem is that with the "OR" in place, a proper index cannot be chosen as the "correct" index would depend on the outcome of the first evaluation for every single row. Therefor, the DBMS selects one index (most probably the correct one for the first part of the "OR") and, in case the first evaluation comes back as "false", runs the second one unindexed. This makes it slow. The performance for this greatly depends on how often the first evaluations returns as "false".