Technical: Microsoft – SQL Server – Query – Like Clause – Optimization – Using Query Hint – Optimize for “value”

Technical: Microsoft – SQL Server – Query – Like Clause  – Optimization – Using Query Hint – Optimize for “value”

Introduction

SQL Server Like Clause – Another path to helping SQL Server use our Index.

Background

SQL


declare @zipcode varchar(10)

set @zipcode = '1718103%'

select *
from   tempdb.dbo.student
where  zipcode like '171803%';

select *
from  dbo.student
where zipcode like @zipcode
optimize (optimize for (@zipcode unknown)) 
;

select *
from   dbo.student
where  zipcode like @zipcode
option (optimize for (@zipcode = '10'))

Execution Plan

optimizeFor

Statistics I/O



Table 'student'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'student'. Scan count 5, logical reads 4649, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'student'. Scan count 5, logical reads 4650, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'student'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Observation

If you have control over the query being passed in try using the optimize hint and pass in an actual value for the parameter \ argument being sought.

If you do not have control over the query being passed-in, consider creating a plan guide and see if it helps.

I think plan guides are ready-made for this based on:

Plan Guides
http://msdn.microsoft.com/en-us/library/ms190417.aspx

 

One thought on “Technical: Microsoft – SQL Server – Query – Like Clause – Optimization – Using Query Hint – Optimize for “value”

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s