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

Question

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.

Answer 1

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.

Answer 2

Anytime you start a LIKE search with a wildcard, you're doing a scan. Unless you can narrow your search criteria to include the first character (which may not be feasible), you'll need to resort to Full Text Search.

Answer 3

how about

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

or

CONTAINS(field1 + field2 + field3, @DESC)

Answer 4

Do you really need to start with a wildcard? Why? Often you can force users to type in the first character at least. I bring this up becasue some developers just use the wildcard as a habit not becasue there is a requirement. In most cases users will be able to type the first character unless the filed stores long strings (like say official airport names). Otherwise you really need to use full-text indexing although KM's trick with the reverse is pretty cool if you don't need the wildcard at the end.

If you can avoid doing the performance killing things, then do so.

Answer 5

Whilst I agree with the accepted answer that Full Text Indexing would be the best solution and am by no means advocating the use of leading wildcard searches if they have to be performed then there are potential steps that can be taken to make the performance of them less bad.

Kalen Delaney in the book "Microsoft SQL Server 2008 Internals" says:

Collation can make a huge difference when SQL Server has to look at almost all characters in the strings. For instance, look at the following:

SELECT COUNT(*) FROM tbl WHERE longcol LIKE '%abc%'

This may execute 10 times faster or more with a binary collation than a nonbinary Windows collation. And with varchar data, this executes up to seven or eight times faster with a SQL collation than with a Windows collation.