WildCard Performance Impact

  • 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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.


    - Craig Farrell

    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