Search code examples
sqlsql-servert-sqloptimizationsql-like

How can I optimize/refactor a TSQL "LIKE" clause?

I have a table with 117000 or so records. I need to perform a search that checks 3 separate fields for a given string pattern.

My where clause is as follows:

field1 LIKE '%' + @DESC + '%'
OR field2 LIKE '%' + @DESC + '%'
OR field3 LIKE '%' + @DESC + '%'

This seems to take about 24 seconds regardless of input...

Is there a better way to do this? Less than 10 (or 5!) seconds would be much more preferable.

Thanks for any help.

Solution

  • Use Full Text Search and CONTAINS. LIKE cannot be optimized when searching in the middle of the field, ie. when the LIKE expression starts with an '%', so it will always do a full table scan.