Why would LIKE be faster than =?

Question

A co-worker recently ran into a situation where a query to look up security permissions was taking ~15 seconds to run using an = comparison on UserID (which is a UNIQUEIDENTIFIER). Needless to say, the users were less than impressed.

Out of frustration, my co-worker changed the = comparison to use a LIKE and the query sped up to under 1 second.

Without knowing anything about the data schema (I don't have access to the database or execution plans), what could potentially cause this change in performance?

(Broad and vague question, I know)

Answer 1

Have you tried updating the statistics on this table/database? Might be worth a try.

Answer 2

In some cases, LIKE can be faster than an equivalent function like SUBSTRING when an index can be utilized.

Can you give the exact SQL?

Sometimes functions can stop the optimizer from being able to use an index.

Compare the execution plans.

Answer 3

It may have just been a poor execution plan that had been cached; Changing to the LIKE statement then just caused a new execution plan to be generated. The same speedup may have been noticed if the person had run sp_recompile on the table in question and then re-run the = query.

Answer 4

The other possibility is that this is a complex query and a type conversion is taking place across the = operator for every row. LIKE changes the semantics somewhat so that the type conversion does not have to weigh as heavily in execution planning. I would suggest that your coworker take a look at the execution plan with the = in place and see if there is something like

CONVERT(varchar, variable) = othervariable

in the execution step. In the wrong circumstances, a single typecast can slow a query by two orders of magnitude.

Answer 5

Well, if he ran the two queries one after the other, then it is quite likely that the data had to read from the disk for the first query, but was still in the RDBMS data cache for the second one...

If this is what happened, then if he ran them in the opposite order he would have seen the opposite results... If he used like with an exact value (no wildcards) then the query plan should have been identical..