A co-worker lately went right into a situation in which a query to find information about security permissions was taking ~just a few seconds to operate utilizing an = comparison on UserID (the industry UNIQUEIDENTIFIER). Obviously, the customers were under impressed.
From frustration, my co-worker transformed the = comparison to utilize a LIKE and also the query increased to under 1 second.
Not understanding anything concerning the data schema (I do not have the database or execution plans), what could cause this transformation in performance?
(Broad and vague question, I understand)
It might are just an undesirable execution plan that were cached Altering towards the LIKE statement then just triggered a brand new execution intend to be produced. Exactly the same speedup might have been observed when the person had run sp_recompile up for grabs under consideration after which re-run the = query.
Another possibility is this fact is really a complex query along with a type conversion is happening over the = operator for each row. LIKE changes the semantics somewhat to ensure that the kind conversion doesn't have to weigh as heavily in execution planning. I recommend that the colliege have a look in the execution plan using the = in position and find out if there's something similar to
CONVERT(varchar, variable) = othervariable
within the execution step. Within the wrong conditions, just one typecast can slow a question by two orders of magnitude.
In some instances,
LIKE could be faster than a similar function like
SUBSTRING when a catalog may be used.
Are you able to provide the exact
Sometimes functions can steer clear of the optimizer from having the ability to make use of an index.
Compare the execution plans.
Well, if he went the 2 queries one by one, then its most probably the data needed to read in the disk for that first query, but was still being within the RDBMS data cache for that second one...
If this sounds like what went down, then if he went them within the opposite order he'd have experienced the alternative results... If he used as with a precise value (no wildcards) then your query plan must have been identical..
Perhaps you have attempted upgrading the data about this table/database? May be worth an attempt.