1

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'
  • 1
    At least I would change the criteria to be Master.AcctNbr like '123%' – James Z Jul 17 '15 at 17:28
2

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.

Query Provided

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.

Query Provided changing left to like

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%'

Change to a union

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.

1

Using wildcards for data that "they don't necessarily exactly match" seems likely to cause you a lot of grief. A search that begins with '%' will turn that into a table scan since no index can be used in searching the names.

From your comments you apparently recognize that this is a thorny problem and you are unlikely to get perfect results.

Since you are matching (roughly) on names, have you considered creating FULL TEXT indices for the tables and columns you are searching? Once the full text index is built and maintained, the likelihood of quickly finding candidate rows goes up.

Here is a simple example of working on your problem from one of my earlier posts from a few years ago: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b88e4423-ba46-40bb-be1a-ff629e71b3fd/full-text-search-is-missing-results?forum=sqlsearch

A sample script using the sys.dm_fts_keywords dynamic management function.

DECLARE @SearchText NVARCHAR(1000)
SET @SearchText = ''

-- This is a single token being searched for, but you can accumulate
-- several such tokens to create a more complex search.
SELECT @SearchText = @SearchText + display_term + ' OR ' 
  FROM sys.dm_fts_index_keywords ( DB_ID('DBName'), 
  object_id('TableName') )
  WHERE display_term like '%colat%'

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)
-- Contents "chocolate OR percolator" in my test case

SELECT * FROM TableName
WHERE CONTAINS(*, @SearchText)

In this case the OR of candidate tokens provides the list to Full Text Search. The performance is based on the ability of the function to return those values which depends in turn on the size of the total text list. (You can also create a physical table that you periodically repopulate with the contents of the function, so that you can query it more quickly.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.