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?