公交车上荫蒂添的好舒服的电影-公用玩物(np双xing总受)-公用小荡货芊芊-公与妇仑乱hd-攻把受做哭边走边肉楼梯play-古装一级淫片a免费播放口

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

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

2.       經(jīng)常會(huì)遇到復(fù)雜的業(yè)務(wù)邏輯和對(duì)數(shù)據(jù)庫(kù)的操作，這個(gè)時(shí)候就會(huì)用SP來(lái)封裝數(shù)據(jù)庫(kù)操作。當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行 Update,Insert,Query,Delete時(shí)），可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。可以極大的提高數(shù)據(jù) 庫(kù)的使用效率，減少程序的執(zhí)行時(shí)間，這一點(diǎn)在較大數(shù)據(jù)量的數(shù)據(jù)庫(kù)的操作中是非常重要的。在代碼上看，SQL語(yǔ)句和程序代碼語(yǔ)句的分離，可以提高程序代碼的 可讀性。

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

4.       安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)存儲(chǔ)過(guò)程的種類：

A.       系統(tǒng)存儲(chǔ)過(guò)程：以sp_開頭,用來(lái)進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定.取得信息.相關(guān)管理工作,如 sp_help就是取得指定對(duì)象的相關(guān)信息。

B.       擴(kuò)展存儲(chǔ)過(guò)程 以XP_開頭,用來(lái)調(diào)用操作系統(tǒng)提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'

C.       用戶自定義的存儲(chǔ)過(guò)程,這是我們所指的存儲(chǔ)過(guò)程常用格式

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

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

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

 

   但是最近我們項(xiàng)目組中有人寫了一個(gè)存儲(chǔ)過(guò)程,其計(jì)算時(shí)間為1個(gè)小時(shí)47分鐘,而有的時(shí)候運(yùn)行時(shí)間都超過(guò)了兩個(gè)小時(shí),同事描述說(shuō)如果將存儲(chǔ)過(guò)程中的語(yǔ)句拿出來(lái)直接運(yùn)行也就10分鐘左右就運(yùn)行完畢，我沒(méi)當(dāng)回事，但是今天我自己寫的存儲(chǔ)過(guò)程也遇到了這個(gè)問(wèn)題，在查找資料后原因終于找到了原因，原來(lái)是Parameter sniffing問(wèn)題。

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

原存儲(chǔ)過(guò)程

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

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

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

    在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è)?/span>@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)

 

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

 

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

    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

測(cè)試結(jié)果為30ms以下。