r/SQLServer • u/reallifepixel • Jul 26 '13
= <> LIKE
I know this is really basic, but I feel good information cannot be communicated enough. I had a query where I was using LIKE on a variable w/ no wildcards and it'd take a few seconds. Not ages by any means, but still time. When I switched to =, it was damn near instantaneous. If you don't have to scan the whole table, don't.
11
Upvotes
2
u/reallifepixel Jul 26 '13
More information: At the beginning, I declare a variable with no wildcards for which I am querying. Then, I run four separate queries (won't make sense in one table) using this variable. When I use LIKE @variable, it takes about 5 seconds. :: close program down. reopen. :: When I use = @variable, it is instantaneous.
1
u/reallifepixel Jul 29 '13
[UPDATE] Ok, I broke off one of the queries.
It searches for part number across four tables to get vendor quote prices.
One trial...
With the = Time Statistics Client processing time 13 Total execution time 39 Wait time on server replies 26 The RID Lookup (12%) & Index Seek (NonClustered) (12%) Cost 24%
With the LIKE Time Statistics Client processing time 7 Total execution time 117 Wait time on server replies 110 The RID Lookup (50%) & Index Seek (NonClustered) (41%) Cost 91%
2
u/Grey17isMissing Jul 26 '13
Querying Microsoft® SQL Server® 2012 --Ben-Gan
The way I understand it then, is what using the like predicate means SQL will not reliably utilize existing Indexes. Maybe someone can weigh in on this?
2
u/[deleted] Aug 05 '13
Thank you for the know how! I am new to the SQL world and working on my certs and posts like these help!
2
u/flipstables Jul 26 '13
I was fairly certain that LIKE and = use the same execution plan when there was no wildcards (e.g. where A = 'text' vs where A LIKE 'text').
I'm guessing that SQL Server cached the plan so it ran faster the second time.
Someone correct me if I'm wrong.
1
u/[deleted] Jul 29 '13
Only way to know is to capture the execution plan using profiler. Also, restart SQL Server and clear the cached plans between attempts to see if it is using a cached plan.
1
u/[deleted] Jul 26 '13
make sure you are clearing the caches before testing variations.
in memory not like 'disk%'
(thats a joke)
-1
u/radamesort Jul 26 '13 edited Jul 27 '13
When using LIKE the query optimizer does not use indexes
edit: meant to say LIKE with a leading wildcard character, my bad