When not to like LIKE

Question

When not to like LIKE

sqlpadawan_1

SSCommitted

Points: 1571
More actions
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.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

sqlpadawan_1 SSCommitted Points: 1571 More actions · Answer 4

sqlpadawan_1

SSCommitted

Points: 1571

March 13, 2011 at 3:26 pm

#1298153

Thanks for the link.