SQL Server中存儲(chǔ)過(guò)程慢于SQL語(yǔ)句直接執(zhí)行的原因

作者：佚名 2010-09-06 11:24:32

SQL Server數(shù)據(jù)庫(kù)中，存儲(chǔ)過(guò)程的執(zhí)行總是要比SQL語(yǔ)句直接執(zhí)行要慢，這究竟是為什么呢？本文將帶您尋找答案。

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

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

有兩種方式能夠避免出現(xiàn)“Parameter sniffing”問(wèn)題：
<!--(1)通過(guò)使用declare聲明的變量來(lái)代替參數(shù)：使用set @variable=@thedate的方式，將出現(xiàn)@thedate的sql語(yǔ)句全部用@variable來(lái)代替。
<!--(2) 將受影響的sql語(yǔ)句隱藏起來(lái)，比如：
<!-- a)      將受影響的sql語(yǔ)句放到某個(gè)子存儲(chǔ)過(guò)程中，比如我們?cè)贎thedate設(shè)置成為今天后再調(diào)用一個(gè)字存儲(chǔ)過(guò)程將@thedate作為參數(shù)傳入就可以了。
<!-- b)      使用sp_executesql來(lái)執(zhí)行受影響的sql。執(zhí)行計(jì)劃不會(huì)被執(zhí)行，除非sp_executesql語(yǔ)句執(zhí)行完。
<!-- c)      使用動(dòng)態(tài)sql（”EXEC(@sql)”來(lái)執(zhí)行受影響的sql。
采用（1）的方法改造例子中的存儲(chǔ)過(guò)程，如下：

代碼：

ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]@var_thedate VARCHAR(30) ASBEGIN 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

Sql Server兩個(gè)版本中顯示所有表信息的語(yǔ)句

SQL Server中一個(gè)語(yǔ)句塊實(shí)現(xiàn)多條語(yǔ)句插入的方法

SQL Server數(shù)據(jù)庫(kù)用戶(hù)創(chuàng)建的方法（使用SQL語(yǔ)句）

使用SQL語(yǔ)句查看SQL Server事務(wù)日志的方法

責(zé)任編輯：段燃來(lái)源：互聯(lián)網(wǎng)

SQL Server 語(yǔ)句