著名的"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
     
     '
     
     ));