JOINS - optimize cost of LIKE/OR criteria The Active table has the Account Name in 1 column and the Master table has the Account Name in any one of 6 separate address columns and I'm trying to match them.  Also, they don't necessarily exactly match, so I'm using the LIKE operator.

The Active table is about 250,000 records and the Master table is about 1,500,000 total records, but the account range specified in the WHERE clause is about 5,000 records.

The query takes a long time, so I assume I'm doing something dumb and so I'm trying to figure out a way to optimize it.  The COALESCE function was a late addition added out of desperation, so I'm not sure I'm even using it appropriately.  Originally everything was broken out separately with OR operators. Every field involved is nvarchar. 

SELECT  
Active.AcctNbr, Active.Name, Master.Addr1, Master.Addr2, Master.Addr3, Master.AcctNbr

FROM Active

INNER JOIN Master ON
Active.Name LIKE COALESCE (('%' + Master.Addr1 + '%'),('%' + Master.Addr2 + '%'),('%' + Master.Addr3 + '%'))

WHERE LEFT(Master.AcctNbr,3) = '123'
There are several factors that are contributing to your slow performance. 
Using the like with the wild card on both sides of the text is going to cause the query to scan all of the rows in both tables multiple times to match your join predicate because the expression is not SARGable.  The LEFT operator in your where statement is also going to force your query to run in a single thread because again it's going to have to scan all of the rows in the master table to determine which rows will have the first three characters equal to 123.



There are a couple things you can try to get a little better performance out of the query.  

The first would be to change your where statement from LEFT(Master.AcctNbr,3) = '123' to Master.AcctNbr like '123%'.  This will make the expression SARGable and should give you a seek on the master table assuming that you have the column AcctNbr indexed.



The second would be, building off the first change, to break the query out into three parts and union the results together.  This may not make a huge difference however it could help the query processor by technically providing it three independent queries and the query processor might decide to run the query in parallel.  (Going parallel might only happen if the query plan meets a certain threshold of cost based on statistics and the complexity of the statement along with the settings of SQL Server.)

   SELECT  
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr1 + '%')
WHERE #Master.AcctNbr like '123%'
UNION
SELECT  
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr2 + '%')
WHERE #Master.AcctNbr like '123%'
UNION
SELECT  
#Active.AcctNbr, #Active.Name, #Master.Addr1, #Master.Addr2, #Master.Addr3, #Master.AcctNbr
FROM #Active
INNER JOIN #Master ON
#Active.Name LIKE ('%' + #Master.Addr3 + '%')
WHERE #Master.AcctNbr like '123%'




You might also have some hardware constraints depending on the # of CPU cores, amount of memory available and the speed of the disks your temp database is on.