SQL SERVER數(shù)據(jù)庫中存儲過程使用循環(huán)語句

yuelianga 2011-01-02

展開全文

SQL SERVER數(shù)據(jù)庫中存儲過程使用循環(huán)語句

http://database.51cto.com 2010-09-06 11:05 佚名互聯(lián)網(wǎng) 我要評論(0)

摘要：SQL SERVER數(shù)據(jù)庫建立存儲過程時，可以使用循環(huán)語句，下面就將為您介紹這種SQL SERVER數(shù)據(jù)庫中存儲過程使用循環(huán)語句的方法，供您參考。
標簽：SQL SERVER 語句

SQL SERVER數(shù)據(jù)庫建立存儲過程時，可以使用循環(huán)語句，下面就將為您介紹這種SQL SERVER數(shù)據(jù)庫中存儲過程使用循環(huán)語句的方法，供您參考。

CREAT PROCEDURE tester
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @userId varchar(50)
    DECLARE @count int
    SET @count = 0
    SELECT @count = count(*) FROM   UserService_User WHERE Account like '%111%'
   WHILE @count > 0
    BEGIN
        SELECT @userId = Id FROM   UserService_User WHERE Account like '%111%'
        exec UserService_RemoveUserByUserId @userId
        SET @count = @count -1
    END
END

說明：

1、此存儲過程在SQL SERVER 2005上測試通過，值得注意的是，循環(huán)體中，語句是使用BEGIN……END包括的，而不是網(wǎng)絡上常說的WHILE ……END WHILE結構，其他的循環(huán)語句，如LOOP ……UNTIL……END LOOP也不能通過編譯，也許是版本的問題，但在SQL SERVER2005中，循環(huán)體使用BEGIN……END就可以，而不能使用網(wǎng)絡上常說的WHILE ……END WHILE結構。

2、循環(huán)體中 UserService_RemoveUserByUserId 是一個存儲過程的名稱，@userId為該存儲過程的參數(shù)，如果有多個參數(shù)，使用“,”分開就可以了，這也是存儲過程調用另一個存儲過程的一種方法。

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

http://database.51cto.com 2010-09-06 11:24 佚名互聯(lián)網(wǎng) 我要評論(0)

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

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

在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設置成為今天后再調用一個字存儲過程將@thedate作為參數(shù)傳入就可以了。
<!-- 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) 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