Search code examples
sqlsearchoptimizationsql-like

What are some best practices for optimizing multiple column LIKE SQL queries?

I have a search query that I'm inheriting and attempting to optimize. I am curious to hear if anyone has any best practices and recommendations for such. The production server is still SQL Server 2000 also.

The query is an advanced customer search stored procedure that accepts 5 different search criteria parameters (i.e. first name, last name, address, phone, etc.) to search a multi-million record table. There are indexes on all joined columns and columns in the WHERE clause. In addition, the initial query dumps the records into a table variable for paging capacity.

INSERT INTO   @tempCustTable (CustomerID, FirstName, LastName, City, StateProvince, Zip, PhoneNumber)
SELECT  DISTINCT cu.CustomerID, cu.FirstName, cu.LastName, a.City,
a.StateProvince, a.Zip, p.PhoneNumber
FROM Customer cu WITH(NOLOCK)
LEFT OUTER JOIN Address a WITH(NOLOCK) ON cu.CustomerID = a.CustomerID
LEFT OUTER JOIN Phone p WITH(NOLOCK) ON cu.CustomerID = p.CustomerID
WHERE  (cu.LastName = @LastName OR cu.LastName LIKE @LastName + '%') 
AND (@FirstName IS NULL OR cu.FirstName = @FirstName OR cu.FirstName LIKE @FirstName + '%')
AND (@StateProvince = '' OR a.StateProvince LIKE @StateProvince)
AND (@City = '' OR a.City LIKE @City + '%')
AND (@Zip = '' OR a.Zip = @Zip OR a.Zip LIKE @Zip + '%')
ORDER BY cu.LastName, cu.FirstName

Does anyone have any recommendations on how I could improve the performance of the query?

Solution

  • isn't this whole line

    AND (@Zip = '' OR a.Zip = @Zip OR a.Zip LIKE @Zip + '%')

    the same as this

    AND (a.Zip LIKE @Zip + '%')

    for sure 

    AND (a.Zip LIKE @Zip + '%')

    it is the same as 

    a.Zip = @Zip OR a.Zip LIKE @Zip + '%'