How do you full-text search multiple criteria on left-joined tables in SQL Server?

Question

I have a query that originally looks like this:

select c.Id, c.Name, c.CountryCode, c.CustomerNumber, cacc.AccountNumber, ca.Line1, ca.CityName, ca.PostalCode
from dbo.Customer as c
left join dbo.CustomerAddress as ca on ca.CustomerId = c.Id
left join dbo.CustomerAccount as cacc on cacc.CustomerId = c.Id
where  c.CountryCode = 'XX' and (cacc.AccountNumber like '%C17%' or c.Name like '%op%'       
or ca.Line1 like '%ae%' or ca.CityName like '%ab%' or ca.PostalCode like '%10%')

On a database with 90,000 records this query takes around 7 seconds to execute (obviously all the joins and likes are taxing).

I have been trying to find a way to bring the query execution time down with full-text search on the columns concerned. However, I haven't seen an example of a full-text search that has three table joins like this, especially since my join condition is not part of the search term.

Is there a way to do this in full-text search?

Answer 1

NOTE: This isn't really an answer, just an attempt to clarify what might actually be causing the performance problem(s).

90,000 records is really a fairly small data set and the query is relatively simple with just two join. Do you have indexes on CustomerAddress.CustomerId and CustomerAccount.CustomerId? That seems more likely to be causing performance issues than the where condition LIKE predicates. Are you typically searching to find a match on all of those columns at the same time?

Answer 2

@David

Yep, there are indexes on the Ids.

I've tried adding indexes on the CustomerAddress stuff (CityName, PostalCode, etc.) and it brought down the query to 3 seconds, but I still find that too slow for something like this.

Note that all of the text fields (with the exception of the ids) are nvarchars, and Line1 is an nvarchar 1000, so that might affect the speed, but still.

Answer 3

I would echo David's suggestion. You'd probably want to examine how the RDBMS is executing your query (e.g., via table scans or using indexes).

One quick check would be to time just the part of the query involving the text search. Something like this:

SELECT  ca.Line1, ca.CityName, ca.PostalCode
FROM    CustomerAddress as ca
WHERE   ca.CustomerId = <some id number>
AND     (ca.Line1 LIKE '%ae%' OR ca.CityName LIKE '%ab%' OR ca.PostalCode LIKE '%10%');

If that takes a long time, then the LIKEs are the issue (remove one expression at a time from the ORed line to see if just one of those columns is causing the slowdown). If it's quick, then the joins are suspect.

You could write a similar query for the CustomerAccount table as well.

Answer 4

Run it through the query analyzer and see what the query plan is. My guess would be that the double root (ie. %ae%) searches are causing it do do a table scan when looking for the matching rows. Double root searches are inherently slow, as you can't use any kind of index to match them usually.