SQL Server中存儲過程比直接運行SQL語句慢的原因

在很多的資料中都描述說SQLSERVER的存儲過程較普通的SQL語句有以下優(yōu)點：

存儲過程只在創(chuàng)造時進行編譯即可，以后每次執(zhí)行存儲過程都不需再重新編譯，而我們通常使用的SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。

經(jīng)常會遇到復雜的業(yè)務邏輯和對數(shù)據(jù)庫的操作，這個時候就會用SP來封裝數(shù)據(jù)庫操作。當對數(shù)據(jù)庫進行復雜操作時(如對多個表進行 Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結合一起使用?？梢詷O大的提高數(shù)據(jù) 庫的使用效率，減少程序的執(zhí)行時間，這一點在較大數(shù)據(jù)量的數(shù)據(jù)庫的操作中是非常重要的。在代碼上看，SQL語句和程序代碼語句的分離，可以提高程序代碼的 可讀性。

存儲過程可以設置參數(shù)，可以根據(jù)傳入?yún)?shù)的不同重復使用同一個存儲過程，從而高效的提高代碼的優(yōu)化率和可讀性。

安全性高,可設定只有某此用戶才具有對指定存儲過程的使用權存儲過程的種類：

系統(tǒng)存儲過程：以sp_開頭,用來進行系統(tǒng)的各項設定.取得信息.相關管理工作,如 sp_help就是取得指定對象的相關信息。

擴展存儲過程 以XP_開頭,用來調(diào)用操作系統(tǒng)提供的功能

exec master..xp_cmdshell ‘ping 10.8.16.1’

用戶自定義的存儲過程,這是我們所指的存儲過程常用格式

模版：Create procedure procedue_name [@parameter data_type][output]
[with]{recompile|encryption} as sql_statement

解釋：output：表示此參數(shù)是可傳回的

with {recompile|encryption} recompile:表示每次執(zhí)行此存儲過程時都重新編譯一次;encryption:所創(chuàng)建的存儲過程的內(nèi)容會被加密。

但是最近我們項目組中有人寫了一個存儲過程,其計算時間為1個小時47分鐘,而有的時候運行時間都超過了兩個小時,同事描述說如果將存儲過程中的語句拿出來直接運行也就10分鐘左右就運行完畢，我沒當回事，但是今天我自己寫的存儲過程也遇到了這個問題，在查找資料后原因終于找到了原因，原來是Parameter sniffing問題。

下面看我是如何將運行一個小時以上的存儲過程優(yōu)化成在一分鐘之內(nèi)完成的：

原存儲過程

CREATE PROCEDURE [dbo].[pro_ImAnalysis_daily]

@THEDATE VARCHAR(30)

AS

BEGIN

IF @THEDATE IS NULL

BEGIN

SET @THEDATE=CONVERT(VARCHAR(30),GETDATE()-1,112);

END

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

每日執(zhí)行:exec pro_ImAnalysis_daily @thedate=null
耗時：1小時47分~2小時13分

經(jīng)過查找資料，原因如下(由于源文是一篇英文，有些地方寫的我不是特別清楚，原文見http://groups.google.com/group/microsoft.public.sqlserver.server/msg/ad37d8aec76e2b8f?hl=en&lr=&ie=UTF-8&oe=UTF-8)：

在SQL Server中有一個叫做 “Parameter sniffing”的特性。SQL Server在存儲過程執(zhí)行之前都會制定一個執(zhí)行計劃。在上面的例子中，SQL在編譯的時候并不知道@thedate的值是多少，所以它在執(zhí)行執(zhí)行計劃的時候就要進行大量的猜測。假設傳遞給@thedate的參數(shù)大部分都是非空字符串，而FACT表中有40%的thedate字段都是null，那么SQL Server就會選擇全表掃描而不是索引掃描來對參數(shù)@thedate制定執(zhí)行計劃。全表掃描是在參數(shù)為空或為0的時候最好的執(zhí)行計劃。但是全表掃描嚴重影響了性能。

假設你第一次使用了Exec pro_ImAnalysis_daily @thedate=’20080312’那么SQL Server就會使用20080312這個值作為下次參數(shù)@thedate的執(zhí)行計劃的參考值，而不會進行全表掃描了，但是如果使用@thedate=null,則下次執(zhí)行計劃就要根據(jù)全表掃描進行了。

有兩種方式能夠避免出現(xiàn)“Parameter sniffing”問題：

(1)通過使用declare聲明的變量來代替參數(shù)：使用set @variable=@thedate的方式，將出現(xiàn)@thedate的sql語句全部用@variable來代替。

(2)  將受影響的sql語句隱藏起來，比如：

a) 將受影響的sql語句放到某個子存儲過程中，比如我們在@thedate設置成為今天后再調(diào)用一個字存儲過程將@thedate作為參數(shù)傳入就可以了。數(shù)據(jù)分析培訓

b) 使用sp_executesql來執(zhí)行受影響的sql。執(zhí)行計劃不會被執(zhí)行，除非sp_executesql語句執(zhí)行完。

c) 使用動態(tài)sql（”EXEC(@sql)”來執(zhí)行受影響的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

測試執(zhí)行速度為10分鐘，我又檢查了一下這個SQL，發(fā)現(xiàn)這個SQL有問題，這個SQL使用了not exists，在一個大表里面使用not exists是不太明智的，所以，我又對這個sql進行了改進，改成如下：

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 @thedate as thedate,

count(distinct case when today>0 then userid else null end) as alluser,

count(distinct case when dates=0 then userid else null end) as newuser

from

(

select userid,

count(CASE WHEN thedate>=@thedate then null else thedate end) as dates,

count(case when thedate=@thedate then thedate else null end) as today

from   FACT

group by userid

)as fact

GO

測試結果為30ms以下。