"LIKE" clause and performance

[katbear]

I seem to have narrowed down the performance problem somewhat.

Seems to be tied to a "like" clause, e.g.

...
AND e.myCol LIKE CASE @myParam
WHEN '<All>' THEN '%'
ELSE @myParam END

If I specify NULL for @myParam, the query runs in 15 seconds. If I specifiy a value for @myParam, the query slows to a grinding halt, ie) never returns.

I remember reading on this board a while back that using "LIKE" is not good for performance.

Can someone please enlighten me on this again...

Thanks!

 

[gmmastros]

>> I remember reading on this board a while back that using "LIKE" is not good for performance.

That is not necessarily true. Under certain conditions, the performance of a like query can be fast.

If the column you are searching is indexed, AND you are searching from the beginning of the data, then you can get index seeks (which are fast).

Think of it this way, suppose you have a dictionary and you wanted to find all the words that start with 'ST'. How long would it take you to find those entries? Not long. Now... suppose you wanted to find all the words that ended with 'ST'. You would have to scan through the entire dictionary and examine every word (not so much fun).

Hopefully, this helps to clarify the issue. For your immediate issue, please explain what you want to accomplish with this query.

-George

"the screen with the little boxes in the window." - Moron