Why will LIKE be faster than =?

Question

An employee recently encountered a situation where a request to search for security permissions took ~ 15 seconds to run using the comparison = UserID (which is UNIQUEIDENTIFIER). Needless to say, users were less impressed.

Due to disappointment, my employee changed the comparison = use LIKE, and the request accelerated to 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 performance change?

(Broad and vague question, I know)

Answer 1

Perhaps this was a bad execution plan that was cached; The transition to the LIKE instruction has just caused the creation of a new execution plan. The same acceleration may have been noticed if a person ran sp_recompile in the table in question and then rerun the query =.

Answer 2

Another possibility is that this is a complex query, and type conversion takes place via the = operator for each row. LIKE slightly changes the semantics, so type conversion should not be very dependent on execution planning. I would suggest that your colleague take a look at the execution plan with = in place and see if there is something like

CONVERT(varchar, variable) = othervariable 

at run time. In the wrong circumstances, one type method can slow down a request by two orders of magnitude.

Answer 3

In some cases, LIKE may be faster than an equivalent function, such as SUBSTRING , when an index can be used.

Can you give the exact SQL ?

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

Compare execution plans.

Answer 4

Well, if he ran two queries one by one, then it is likely that the data should have been read from disk for the first request, but were still in the RDBMS data cache for the second ...

If this is what happened, then if he started them in the reverse order, he would see opposite results ... If he used both with the exact value (without wildcards), then the query plan should be identical.

Answer 5

Have you tried updating statistics on this table / database? Maybe worth a try.