How to make LIKE '%Search% faster in SQL Server

Question

I have a below query which is taking up a lot of time:-

 SELECT 
      p.CrmId,
      park.Name
 FROM
     Property p
 INNER JOIN
     Som som ON som.CrmId = p.SystemOfMeasurementId 
 LEFT JOIN
     Park park ON park.CrmId = p.ParkId
 WHERE 
     ((p.City like '%' + @search + '%' ) 
      OR (p.Address1 like '%' + @search + '%') 
      OR (p.Address2 like '%' + @search + '%' )
      OR (p.State like '%' + @search + '%' ) 
      OR (park.Name like '%' + @search + '%' ) 
      OR (p.ZipCode like '%' + @search + '%')
     )
     AND (@usOnly = 0 or (p.CrmCountryId = @USA_COUNTRY_ID))

I have read that indexes don't work well when we use LIKE '%Search%.

Any ideas on how to make this faster?

Answer 1

You are right... queries with a leading wildcard are awful for performance. To get around this, Sql Server has something called full text search. You create a special FULL TEXT Index for each of the columns you want to search, and then update your code to use the CONTAINS keyword:

SELECT 
  p.CrmId,
  park.Name
        from Property p
        inner join Som som on som.CrmId = p.SystemOfMeasurementId 
        left join Park park on park.CrmId = p.ParkId
  WHERE 
  (
       Contains(p.City, @search)
    or Contains(p.Address1, @search)
    or Contains(p.Address2, @search)
    or Contains(p.State, @search) 
    or Contains(park.Name, @search) 
    or Contains(p.ZipCode, @search)
 )
 AND (@usOnly = 0 or (p.CrmCountryId = @USA_COUNTRY_ID))

Unfortunately, all those OR conditions are still likely to make this pretty slow, and FULL TEXT wasn't intended as much for shorter strings like City or State, or for casting wide nets like this. You may find you'll do much better for this kind of search by integrating with a tool like Solr or ElasticSearch. In addition to writing a better and faster search, these tools will help you create sane rankings for returning results in an order that makes sense and is relevant to the input.

Another strategy it to create a computed column that concatenates your address and name text into a single column, and then do a single FULL TEXT index on that one field, with a single CONTAINS() call.