著名的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

    

select sum(abcd) from test 
where aa=@t_ipdwcode 
and gltr_project <>'' 
and year(gltr_eff_dt)=year(getdate())
OPTION (RECOMPILE);

3. 用OPTIMIZE FOR, 如:

SQL code

    

select sum(abcd) from test 
where aa=@t_ipdwcode 
and gltr_project <>'' 
and year(gltr_eff_dt)=year(getdate())
OPTION (OPTIMIZE FOR (@t_ipdwcode = 'abcde'));