October 26, 2012 at 3:19 pm
Hi everyone,
I am using WildCard % in one of my queries. Weird is that when the wildcard % is used at the first place its creating lot of performance impact and 100 percent usage of cpu. Just let me explain with an explain.
'%ga' occupies 100percent usage of cpu whereas 'ga%" doesnt but the results differ.
Can anyone suggest an alternative which can help the performance and reduce the cpu usage.
Thanks
October 26, 2012 at 3:28 pm
That's because when you use a wildcard in the beginning of a string, you won't be able to use indexes.
I'm not sure if you can increase performance using PATINDEX or CHARINDEX.
Give it a try and test.
For this problem, search in the web for SARGable to obtain a more complete explanation.
October 26, 2012 at 4:18 pm
tanvishriya (10/26/2012)
Hi everyone,I am using WildCard % in one of my queries. Weird is that when the wildcard % is used at the first place its creating lot of performance impact and 100 percent usage of cpu. Just let me explain with an explain.
'%ga' occupies 100percent usage of cpu whereas 'ga%" doesnt but the results differ.
Can anyone suggest an alternative which can help the performance and reduce the cpu usage.
Thanks
Luis is correct in his description above of why index searching is failing between the two. To help you find alternatives to your methodology, we'd have to see what you're trying to do in full, along with the DDL/Indexes of the base table(s) that you're trying to work against.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy