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

 

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

posted @ 2014-05-15 14:54  princessd8251  阅读(157)  评论(0)    收藏  举报