SQL语句中加入变量后会大大降低其执行效率

 有些时候，在SQL语句中加入变量后会大大降低其执行效率
数据库da，表ta (a1,a2,a3)
数据库db，表tb (b1,b2,b3)

SQL code 1

  
  

   
   
   
   declare
   
    
   
   @v1
   
    
   
   int
   
   ,
   
   @v2
   
    
   
   varchar
   
   (
   
   10
   
   ),
   
   @v3
   
    
   
   int
   
   

   
   select
   
    
   
   @v1
   
   =
   
   1
   
   ,
   
   @v2
   
   =
   
   '
   
   20080801
   
   '
   
   ,
   
   @v3
   
   =
   
   10
   
   


   
   select
   
    
   
   *
   
    
   
   from
   
    ta

   
   where
   
     a1
   
   =
   
   @v1
   
   

   
   and
   
       a3 
   
   in
   
   (
       
   
   select
   
     
   
   distinct
   
    b3 
   
   from
   
    db.abc.dbo.tb
       
   
   where
   
      b1 
   
   =
   
   @v1
   
   
       
   
   and
   
        b2 
   
   =
   
   @v2
   
   
       
   
   and
   
        b3 
   
   =
   
   @v3)

   
   --
   
   此方式平均执行时间为14分钟
  
  

 
 

  
  SQL code2
 
 
 
 

  
   
 
 
 
 

  
  declare @v1 int,@v2 varchar(10),@v3 int ,@sql varhcar(8000)
select @v1=1,@v2='20080801',@v3=10

set @sql='
select * from ta
where  a1=@v1
and    a3 in(
       select  distinct b3 from db.abc.dbo.tb
       where   b1 ='+@v1+'
       and     b2 ='+@v2+'
       and     b3 ='+@v3+')'

exec (@sql)
--用这种方式执行起来只用2秒