前言:
前段时间,发现了手动执行存储过程比SQL还慢,且不只慢一点,百度后才发现原因
解决:
代码
ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]
@var_thedate VARCHAR(30)
AS
BEGIN
DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@var_thedate ;
INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)
SELECT AA.THEDATE,ALLUSER,NEWUSER
FROM
( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER
FROM FACT
WHERE THEDATE=@var_thedate
GROUP BY THEDATE
) AA
LEFT JOIN
(SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER
FROM FACT T1
WHERE NOT EXISTS(
SELECT 1
FROM FACT T2
WHERE T2.THEDATE<@var_thedate
AND T1.USERID=T2.USERID)
AND T1.THEDATE=@var_thedate
GROUP BY THEDATE
) BB
ON AA.THEDATE=BB.THEDATE);
GO
在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。
采用(1)的方法改造例子中的存储过程,如下:
代码
ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]
@var_thedate VARCHAR(30)
AS
BEGIN
declare @THEDATE VARCHAR(30)
IF @var_thedate IS NULL
BEGIN
SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);
END
SET @THEDATE=@var_thedate;
DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;
INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)
SELECT AA.THEDATE,ALLUSER,NEWUSER
FROM
( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER
FROM FACT
WHERE THEDATE=@THEDATE
GROUP BY THEDATE
) AA
LEFT JOIN
(SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER
FROM FACT T1
WHERE NOT EXISTS(
SELECT 1
FROM FACT T2
WHERE T2.THEDATE<@THEDATE
AND T1.USERID=T2.USERID)
AND T1.THEDATE=@THEDATE
GROUP BY THEDATE
) BB
ON AA.THEDATE=BB.THEDATE);
GO