●存储过程比sql语句慢

      今天碰到一个问题，有个存储过程执行需要1分钟，但是把存储过程复制出来，将参数赋值，然后执行，只要6秒。后来终于发现是Parameter sniffing问题。

原存储过程：

 

IF ( OBJECT_ID('sp_yp_jxctj', 'P') IS NOT NULL )
    DROP PROC sp_yp_jxctj;
GO
 
/*========================================================  
描述：
系统：
引用：
输入：
输出：
备注：
修改记录：
==========================================================*/
 
CREATE PROC sp_yp_jxctj
    @startDate  VARCHAR(50) ,   --开始时间
    @endDate    VARCHAR(50) ,   --结束时间
    @inputCode  VARCHAR(24) ,   --输入码
    @deptCode   VARCHAR(20) ,   --科室代码
    @drugType   CHAR(2) ,       --药品类型
    @drugAttr   VARCHAR(20) ,   --药品属性
    @dosage     VARCHAR(20) ,   --剂型
    @drugState  INT ,           --药品状态
    @rate       INT             --零差率
AS
    BEGIN
    ----------------------------
    --内容省略
    ----------------------------
    end;

    在SQL Server中有一个叫做 “Parameter sniffing”的特性。SQL Server在存储过程执行之前都会制定一个执行计划。

 

从网上找到的解决方式：

1、用变量替换掉参数（已验证，举例如下）

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

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

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

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

 

使用第一种方法修改后：

IF ( OBJECT_ID('sp_yp_jxctj', 'P') IS NOT NULL )
    DROP PROC sp_yp_jxctj;
GO
 
/*========================================================  
描述：
系统：
引用：
输入：
输出：
备注：
修改记录：
==========================================================*/
 
CREATE PROC sp_yp_jxctj
    @startDate VARCHAR(50) ,    --开始时间
    @endDate VARCHAR(50) ,      --结束时间
    @inputCode VARCHAR(24) ,    --输入码
    @deptCode VARCHAR(20) ,     --科室代码
    @drugType CHAR(2) ,         --药品类型
    @drugAttr VARCHAR(20) ,     --药品属性
    @dosage VARCHAR(20) ,       --剂型
    @drugState INT ,            --药品状态
    @rate INT                   --零差率
AS
    BEGIN
--用变量替换掉参数，以防出现“Parameter sniffing”问题
        DECLARE @deptType INT ,
            @startDate1 VARCHAR(50) = @startDate ,  --开始时间
            @endDate1   VARCHAR(50) = @endDate ,    --结束时间
            @inputCode1 VARCHAR(24) = @inputCode ,  --输入码
            @deptCode1  VARCHAR(20) = @deptCode ,   --科室代码
            @drugType1  CHAR(2)     = @drugType ,   --药品类型
            @drugAttr1  VARCHAR(20) = @drugAttr ,   --药品属性
            @dosage1    VARCHAR(20) = @dosage ,     --剂型
            @drugState1 INT         = @drugState ,  --药品状态
            @rate1      INT         = @rate;        --零差率
 
    ----------------------------
    --内容省略
    ----------------------------
    END;