Performance degradation in SQL Server FullText search when combining conditions with OR operator

Data and Databases
1

I’m working with SQL Server 2008 and have set up a FullText index on two database columns - Employees.GivenName and Contact.FamilyName. I’ve been testing query performance and noticed something strange.

When I run individual FullText searches, they perform really well:

SELECT *
FROM (Employees INNER JOIN Contact ON Employees.ID=Contact.EmployeeID)
WHERE CONTAINS(GivenName, 'John')

=> 480ms

SELECT *
FROM (Employees INNER JOIN Contact ON Employees.ID=Contact.EmployeeID)
WHERE CONTAINS(FamilyName, 'John')

=> 25ms

But when I combine them with OR, performance drops significantly:

SELECT *
FROM (Employees INNER JOIN Contact ON Employees.ID=Contact.EmployeeID)
WHERE CONTAINS(GivenName, 'John') OR CONTAINS(FamilyName, 'John')

=> 28,450ms

Even mixing FullText with LIKE operations shows poor results:

SELECT *
FROM (Employees INNER JOIN Contact ON Employees.ID=Contact.EmployeeID)
WHERE GivenName LIKE '%John%' OR CONTAINS(FamilyName, 'John')

=> 2,100ms

For comparison, regular LIKE queries perform much better individually:

SELECT *
FROM (Employees INNER JOIN Contact ON Employees.ID=Contact.EmployeeID)
WHERE GivenName LIKE '%John%'

=> 590ms

I’ve tried rebuilding the FullText catalog but the issue remains. What causes this massive performance hit when using OR with FullText searches? Is there a way to optimize these combined queries?

2

The performance degradation occurs because SQL Server’s query optimizer struggles with OR operations involving FullText predicates across different tables. When you use OR with CONTAINS functions, the optimizer often abandons the FullText index strategy and resorts to less efficient execution plans. I encountered this exact issue in a previous project where combining FullText searches resulted in similar slowdowns. The solution that worked best was restructuring the query using UNION ALL instead of OR. Execute each CONTAINS condition separately and combine the results, ensuring you handle potential duplicates appropriately. Additionally, consider using FREETEXT instead of CONTAINS if exact matching is not critical, as it sometimes performs better in complex scenarios. Check your execution plans to confirm the optimizer is utilizing the FullText indexes properly in your rewritten queries.

3

hmm interesting issue! have you tried checking the execution plan to see what’s actually happening under the hood? i’m curious - how big is your dataset and are there any other indexes on those tables that might be conflicting with the fulltext search?

4

yea, this is a common thing with sql server fulltext searches. using OR can lead to table scans instead of leveraging the index. maybe try UNION – it’s a bit more complicated but can really speed things up!