以文本方式查看主题

-  昂捷论坛  (http://enjoyit.com.cn/bbs/index.asp)
--  □-通用类  (http://enjoyit.com.cn/bbs/list.asp?boardid=27)
----  [分享][原创]SQL Server中存储过程比直接运行SQL语句慢的原因  (http://enjoyit.com.cn/bbs/dispbbs.asp?boardid=27&id=8679)
--  作者：gqznick
--  发布时间：2012/12/28 16:40:02
--  [分享][原创]SQL Server中存储过程比直接运行SQL语句慢的原因

SQL Server中存储过程比直接运行SQL语句慢的原因:

最近在BI项目中遇到一个问题，在直接sqlserver中直接 exec up_fact_d_gdsclass ‘2012-12-12’，执行时间大概需要很长时间，大概20分钟左右；但是在打开存储过程取一句一句执行代码确非常快，大概2分钟就可以执行完成，相差近10倍；如下：

ALTER PROC [dbo].[up_fact_d_gdsclass]
@dst date
as


select
   a.c_dt,b.c_adno,b.c_child_adno,b.c_class,b.c_child_class,
   a.c_store_id,b.c_type,b.c_tax_rate,
   isnull(sum(c.c_order_n),0) as c_number_order,
   isnull(sum(c.c_at_order),0) as c_at_order,isnull(sum(a.c_ret_n),0) as c_number_ret,
   isnull(sum(a.c_at_in_ret),0) as c_at_ret,isnull(sum(a.c_rec_n),0) as c_number_rec,
   isnull(sum(a.c_at_in_rec),0) as c_at_rec,isnull(sum(a.c_move_in_n),0) as c_number_move_in,

。。。。

在一般我们定义存储过程的变量的时候用上面这种方式；

 

ALTER PROC [dbo].[up_fact_d_gdsclass]
@dst date
as


declare @dt date
set @dt=@dst
select
   a.c_dt,b.c_adno,b.c_child_adno,b.c_class,b.c_child_class,
   a.c_store_id,b.c_type,b.c_tax_rate,
   isnull(sum(c.c_order_n),0) as c_number_order,
   isnull(sum(c.c_at_order),0) as c_at_order,isnull(sum(a.c_ret_n),0) as c_number_ret,
   isnull(sum(a.c_at_in_ret),0) as c_at_ret,isnull(sum(a.c_rec_n),0) as c_number_rec,
   isnull(sum(a.c_at_in_rec),0) as c_at_rec,isnull(sum(a.c_move_in_n),0) as c_number_move_in,

。。。

当在存储过程中再声明一个变量来接受外部传入的这个变量时，执行的效率非常高，也可以将内部的变量赋一个缺省值；

 

原因在于：

在SQL Server中有一个叫做 “Parameter sniffing”的特性。SQL Server在存储过程执行之前都会制定一个执行计划。在上面的例子中，SQL在编译的时候并不知道@thedate的值是多少，所以它在执行执行计划的时候就要进行大量的猜测。假设传递给@thedate的参数大部分都是非空字符串，而FACT表中有40%的thedate字段都是null，那么SQL Server就会选择全表扫描而不是索引扫描来对参数@thedate制定执行计划。全表扫描是在参数为空或为0的时候最好的执行计划。但是全表扫描严重影响了性能。

假设你第一次使用了Exec pro_ImAnalysis_daily @thedate=’20080312’那么SQL Server就会使用20080312这个值作为下次参数@thedate的执行计划的参考值，而不会进行全表扫描了，但是如果使用@thedate=null,则下次执行计划就要根据全表扫描进行了。

有两种方式能够避免出现“Parameter sniffing”问题：

(1)通过使用declare声明的变量来代替参数：使用set @variable=@thedate的方式，将出现@thedate的sql语句全部用@variable来代替。

(2) 将受影响的sql语句隐藏起来，比如：

a) 将受影响的sql语句放到某个子存储过程中，比如我们在@thedate设置成为今天后再调用一个字存储过程将@thedate作为参数传入就可以了。

b) 使用sp_executesql来执行受影响的sql。执行计划不会被执行，除非sp_executesql语句执行完。

c) 使用动态sql（”EXEC(@sql)”来执行受影响的sql。

由此提升了BI在抽取和聚合大数据时的效率。

参考：http://www.cnblogs.com/wuming/archive/2009/05/06/1450980.html

 
--  作者：qwe8520
--  发布时间：2015/5/14 21:21:58
--  
学习了。。 ブランドコピー私達がヴィトン激安時代にグッチ激安従ってベルト激安成長プラダコピーする時に