Post reply

When not to like LIKE

  • March 13, 2011 at 2:30 pm

    #235151

    I ran into an interesting challenge during some stored proc testing last week. While watching the io stats, this nasty line popped up:

    Table ‘MyTable’. Scan count 45379, logical reads 156892, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    After some troubleshooting and testing, I narrowed it down to one line in a WHERE clause: AND ( ID LIKE @FromValue ).

    The variable @FromFFCValue is set in the stored proc as:

    IF ( @FromFlag = ‘YES’ )

    SET @FromValue = @LOWER_SUFFIX

    ELSE

    SET @FromValue = @UPPER_SUFFIX

    While testing, I changed the variable to a hard-coded value, like so:

    AND ( ID LIKE ‘%XX01′ )

    The IO stats:

    Table ‘MyTable’. Scan count 27, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Yes, much better, but I can’t hard-code a value in. It is calculated in the proc based on input params.

    I ended up with:

    AND ( RIGHT( ID, 4 ) = @FromValue ), which was much friendlier in the IO stats:

    Table ‘MyTable’. Scan count 27, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    I'm not sure if there is much more I can do here, I thought it was interesting how SQL server treats the variable in the LIKE operation.

  • March 13, 2011 at 2:46 pm

    #1298147

    If you run a LIKE '%something' statement against a table you'll end up with a table/index scan.

    One option would be to add an indexed computed persisted column with REVERSE(ID). Then you could query LIKE ' gnihtemos%' and you'll most probably get an index seek.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 13, 2011 at 2:58 pm

    #1298148

    I guess what I thought what was interesting was the io results between the LIKE operator and a hard-coded value vs the LIKE operator and a variable.

  • March 13, 2011 at 3:05 pm

    #1298149

    This is at play: http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 13, 2011 at 3:26 pm

    #1298153

    Thanks for the link.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply