Parameter Sniffing

存储过程:
create procedure da_xm
@ipdwname varchar(50)
as
declare @t_ipdwcode varchar(50);
begin
select top 1 @t_ipdwcode=aaaa from test1 where abcd=@ipdwname;
select sum(abcd) from test
where aa=@t_ipdwcode
and gltr_project <>''
and year(gltr_eff_dt)=year(getdate());
end

说明:test中有1000万以上条数据,
如果将值@t_ipdwcode以手工方式填入过程,执行速度为1秒,但如果通过传入变量的方式传入,要等待2分钟以上.

解决办法有:
1. 用WITH Recompile, 如

SQL code

 

EXEC da_xm @ipdwname='abced'WITH RECOMPILE;

在2005里有: 2. Query hint RECOMPILE, 如:

SQL code

 

selectsum(abcd) from test

where aa=@t_ipdwcode

and gltr_project <>''

andyear(gltr_eff_dt)=year(getdate())

OPTION (RECOMPILE);

3. 用OPTIMIZE FOR, 如:

SQL code

 

selectsum(abcd) from test

where aa=@t_ipdwcode

and gltr_project <>''

andyear(gltr_eff_dt)=year(getdate())

OPTION (OPTIMIZE FOR (@t_ipdwcode='abcde'));