摘要：?上一篇我們探討了在靜態(tài)語句中使用 WHERE Column=@Param OR @Param IS NULL的問題，有對OPTION(COMPILE)的評論配深，那這節(jié)我們來探討OPTION(COMPILE)的問題携添。
上一篇我們探討了在靜態(tài)語句中使用
WHERE Column=@Param OR @Param IS NULL的問題，有對OPTION(COMPILE)的評論篓叶，那這節(jié)我們來探討OPTION(COMPILE)的問題烈掠。
探討OPTION(COMPILE)問題
在SQL SERVER中任何時候第一次調用存儲過程時羞秤，此時存儲過程將會被SQL SERVER優(yōu)化且查詢計劃在內(nèi)存中會被緩存。由于查詢計劃緩存左敌，當運行相同的存儲過程時瘾蛋，它都將使用相同的查詢計劃，從而無需每次運行時對同一存儲過程進行優(yōu)化和編譯矫限。因此瘦黑，如果我們需要每天運行相同的存儲過程若干次，那么可以節(jié)省大量的時間和硬件資源奇唤。
如果每次運行的存儲過程中的在WHERE子句中具有相同的參數(shù)幸斥，則重復使用存儲過程的相同查詢計劃是有意義的。但是咬扇，如果運行相同的存儲過程甲葬，但是參數(shù)的值會改變呢？發(fā)生什么取決于參數(shù)的典型性懈贺。如果存儲過程的參數(shù)的值從執(zhí)行到執(zhí)行相似经窖，那么緩存的查詢計劃將正常工作，查詢將按照執(zhí)行最佳來梭灿。但是画侣，如果參數(shù)不是典型的，那么被重用的緩存查詢計劃可能不是最優(yōu)的堡妒，導致查詢運行更慢配乱，因為它使用的查詢計劃并不是真正為所使用的參數(shù)設計的。下面我們借助AdventureWorks2012實例數(shù)據(jù)庫來用實例講解上述所描述的情況皮迟。
DECLARE@AddressLine1NVARCHAR(60) =NULL,? ? @AddressLine2NVARCHAR(60) =NULL,? ? @CityNVARCHAR(30) =NULL,? ? @PostalCodeNVARCHAR(15) =NULL,? ? @StateProvinceIDINT=NULLSET@City ='Bothell'SET@PostalCode ='98011'SET@StateProvinceID =79DECLARE@SQLNVARCHAR(MAX),@ColumnNameVARCHAR(4000),@ParamDefinitionNVARCHAR(500)SET@ColumnName ='a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'SET@SQL='SELECT '+ @ColumnName +' FROM Person.Address AS a WHERE 1 = 1'IF(@AddressLine1ISNOTNULL)SET@SQL= @SQL+' AND a.AddressLine1 LIKE ''%'' + @AddressLine1 + ''%'''IF(@AddressLine2ISNOTNULL)SET@SQL= @SQL+' AND a.AddressLine2 LIKE ''%'' + @AddressLine2 + ''%'''IF(@CityISNOTNULL)SET@SQL= @SQL+' AND a.City LIKE ''%'' + @City + ''%'''IF(@PostalCodeISNOTNULL)SET@SQL= @SQL+' AND a.PostalCode LIKE ''%'' + @PostalCode + ''%'''IF(@StateProvinceIDISNOTNULL)SET@SQL= @SQL+' AND a.StateProvinceID = @StateProvinceID'SET@ParamDefinition = N'@AddressLine1 NVARCHAR(60),
? ? ? ? ? ? ? ? ? ? ? ? @AddressLine2 NVARCHAR(60),
? ? ? ? ? ? ? ? ? ? ? ? @City NVARCHAR(30),
? ? ? ? ? ? ? ? ? ? ? ? @PostalCode NVARCHAR(15),
? ? ? ? ? ? ? ? ? ? ? ? @StateProvinceID INT'EXECUTEsp_executesql @SQL,@ParamDefinition,? ? ? ? ? ? ? ? ? ? ? ? ? @AddressLine1? = @AddressLine1,? ? ? ? ? ? ? ? ? ? ? ? ? @AddressLine2 = @AddressLine2,? ? ? ? ? ? ? ? ? ? ? ? ? @City = @City,? ? ? ? ? ? ? ? ? ? ? ? ? @PostalCode = @PostalCode,? ? ? ? ? ? ? ? ? ? ? ? ? @StateProvinceID = @StateProvinceIDGO
我們運行上述查詢1次搬泥，看到查詢結果如下和計劃緩存次數(shù)如下：
此時我們將外部變量StateProvinceID類型修改為SMALLINT，然后再來運行查詢和緩存計劃伏尼，此時會出現(xiàn)查詢計劃使用次數(shù)是為2忿檩，還是出現(xiàn)兩條次數(shù)都為1呢？
此時我們再來將動態(tài)SQL中內(nèi)部變量StateProvinceID類型修改為SMALLINT爆阶，此時會出現(xiàn)查詢計劃使用次數(shù)是為3燥透，還是出現(xiàn)兩條，次數(shù)分別為2和1呢辨图？
由上可知班套，如果我們修改外部變量參數(shù)類型不會影響查詢計劃緩存即會達到重用目的，若修改動態(tài)SQL內(nèi)部變量參數(shù)類型則不會重用查詢計劃緩存徒役。
大多數(shù)情況下孽尽，我們可能不需要擔心上述問題窖壕。但是忧勿，在某些情況下杉女，假設從查詢的執(zhí)行到執(zhí)行的參數(shù)變化很大，則會引起問題鸳吸。
如果我們確定存儲過程通常運行正常熏挎，但有時運行緩慢，則很可能會看到上述問題晌砾。在這種情況下坎拐，我們可以做的是改變存儲過程，并添加WITH RECOMPILE選項养匈。
添加此選項后哼勇，存儲過程將始終重新編譯自身，并在每次運行時創(chuàng)建一個新的查詢計劃呕乎。當然這會消除查詢計劃重用的好處积担，但確保了每次運行查詢時都使用正確的查詢計劃。如果存儲過程中有多個查詢猬仁，那么它將重新編譯存儲過程中的所有查詢帝璧，即使那些不受非典型參數(shù)影響的查詢也是如此。
講完OPTION(COMPILE)湿刽，接下來我們講講如何創(chuàng)建高性能的存儲過程的烁。有些童鞋可能會創(chuàng)建如下存儲過程。
CREATEPROC [dbo].[HighPerformanceExample](? ? @AddressLine1NVARCHAR(60) =NULL,? ? @AddressLine2NVARCHAR(60) =NULL,? ? @CityNVARCHAR(30) =NULL,? ? @PostalCodeNVARCHAR(15) =NULL,? ? @StateProvinceIDSMALLINT=NULL)ASSETNOCOUNTONSELECTa.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguidFROMPerson.AddressASaWHERE(a.AddressLine1 = @AddressLine1OR@AddressLine1ISNULL)AND(a.AddressLine2 = @AddressLine2OR@AddressLine2ISNULL)AND(a.City = @CityOR@CityISNULL)AND(a.PostalCode = @PostalCodeOR@PostalCodeISNULL)AND(a.StateProvinceID = @StateProvinceIDOR@StateProvinceIDISNULL)--或者SELECTa.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguidFROMPerson.AddressASaWHEREa.AddressLine1 =COALESCE(@AddressLine1, a.AddressLine1)ANDa.AddressLine2 =COALESCE(@AddressLine2, a.AddressLine2)ANDa.City =COALESCE(@City, a.City)ANDa.PostalCode =COALESCE(@PostalCode, a.PostalCode)ANDa.StateProvinceID =COALESCE(@StateProvinceID, a.StateProvinceID)--或者SELECTa.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguidFROMPerson.AddressASaWHEREa.AddressLine1 =CASEWHEN@AddressLine1ISNULLTHENa.AddressLine1ELSE@AddressLine1ENDANDa.AddressLine2 =CASEWHEN@AddressLine2ISNULLTHENa.AddressLine1ELSE@AddressLine2ENDANDa.City =CASEWHEN@CityISNULLTHENa.CityELSE@CityENDANDa.PostalCode =CASEWHEN@PostalCodeISNULLTHENa.PostalCodeELSE@PostalCodeENDANDa.StateProvinceID =CASEWHEN@StateProvinceIDISNULLTHENa.StateProvinceIDELSE@StateProvinceIDENDGOSETNOCOUNTOFF
上述無論怎樣執(zhí)行都將表現(xiàn)的非常糟糕诈闺。因為SQL SERVER不能將其很好地進行優(yōu)化渴庆，如果這是由不同的參數(shù)組合產(chǎn)生，那么我們可能會得到一個絕對糟糕的計劃雅镊。不難理解把曼，當執(zhí)行一個存儲過程，并且還沒有生成一個查詢緩存計劃漓穿。所以嗤军，管理員可能會更新統(tǒng)計信息或強制重新編譯（或者，甚至重新啟動SQL Server）來嘗試解決此問題晃危，但這些都不是最佳解決方案叙赚。OPTION(COMPILE)重新編譯是個好東西，但是我們是不是像如下簡單加上重新編譯就可以了呢僚饭。
SELECT...FROM...WHERE...OPTION(RECOMPILE);
如果我們要使用重新編譯震叮，那么我們是否需要考慮以下兩個問題呢？
如果我們知道一個特定的語句總是返回相同數(shù)量的行并使用相同的計劃（并且我們已測試過并知道這一點）鳍鸵，那么我們會正常創(chuàng)建存儲過程并讓計劃得到緩存苇瓣。
如果我們知道一個特定的語句從執(zhí)行到執(zhí)行是不一樣的，最佳查詢計劃也會有所不同（我們也應該從執(zhí)行多個測試樣本中知道這一點）偿乖，然后我們會如正常一樣創(chuàng)建存儲過程击罪，然后使用OPTION(RECOMPILE)以確保語句的計劃不會被存儲過程緩存或保存哲嘲。在每次執(zhí)行時，存儲過程將獲得不同的參數(shù)媳禁，如此一來語句將在每次執(zhí)行時得到一個新的計劃眠副。
上述已經(jīng)描述的很明朗了，使用或者不使用重新編譯的前提不過是需不需要查詢計劃緩存還是重新生成一個查詢計劃竣稽，但是我們怎么知道到底是否需要查詢計劃緩存呢囱怕？這就要看對創(chuàng)建的存儲過程是否穩(wěn)定了，如果穩(wěn)定我們就從緩存中去取毫别，否則使用重新編譯查詢娃弓。歸根結底一句話概述：重新編譯不穩(wěn)定（可變）的計劃，但為穩(wěn)定（不可變）的計劃岛宦，我們把它們放在緩存中重用忘闻。
為了實現(xiàn)這點，我們需要分析所查詢的存儲過程恋博，例如在每個企業(yè)下有對應的用戶齐佳，我們想象一下所呈現(xiàn)的UI界面，首先是所有用戶债沮，查詢條件則是企業(yè)下拉框炼吴，然后是用戶名或者員工工號等。當沒有任何篩選條件時則走查詢計劃緩存疫衩，若選擇企業(yè)硅蹦，或者還選擇了員工相關篩選條件則重新編譯。類似如下存儲過程闷煤。
CREATEPROC [dbo].[HighPerformanceExample](? ? @AddressLine1NVARCHAR(60) =NULL,? ? @AddressLine2NVARCHAR(60) =NULL,? ? @CityNVARCHAR(30) =NULL,? ? @PostalCodeNVARCHAR(15) =NULL,? ? @StateProvinceIDSMALLINT=NULL)ASSETNOCOUNTONDECLARE@SQLNVARCHAR(MAX),@ColumnNameVARCHAR(4000),@ParamDefinitionNVARCHAR(500),@RecompileBIT=1;SET@ColumnName ='a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'SET@SQL='SELECT '+ @ColumnName +' FROM Person.Address AS a WHERE 1 = 1'IF(@StateProvinceIDISNOTNULL)SET@SQL= @SQL+' AND a.StateProvinceID = @StateProvinceID'IF(@AddressLine1ISNOTNULL)SET@SQL= @SQL+' AND a.AddressLine1 LIKE @AddressLine1'IF(@AddressLine2ISNOTNULL)SET@SQL= @SQL+' AND a.AddressLine2 LIKE @AddressLine2'IF(@CityISNOTNULL)SET@SQL= @SQL+' AND a.City LIKE @City'IF(@PostalCodeISNOTNULL)SET@SQL= @SQL+' AND a.PostalCode LIKE @PostalCode'IF(@StateProvinceIDISNOTNULL)SET@Recompile =0IF(PATINDEX('%[%_?]%',@AddressLine1) >=4ORPATINDEX('%[%_?]%', @AddressLine2) =0)AND(PATINDEX('%[%_?]%', @City) >=4ORPATINDEX('%[%_?]%', @PostalCode) =0)SET@Recompile =0IF@Recompile =1BEGINSET@SQL= @SQL+ N' OPTION(RECOMPILE)';END;SET@ParamDefinition = N'@AddressLine1 NVARCHAR(60),
? ? ? ? ? ? ? ? ? ? ? ? @AddressLine2 NVARCHAR(60),
? ? ? ? ? ? ? ? ? ? ? ? @City NVARCHAR(30),
? ? ? ? ? ? ? ? ? ? ? ? @PostalCode NVARCHAR(15),
? ? ? ? ? ? ? ? ? ? ? ? @StateProvinceID SMALLINT'EXECUTEsp_executesql @SQL,@ParamDefinition,? ? ? ? ? ? ? ? ? ? ? ? ? @AddressLine1? = @AddressLine1,? ? ? ? ? ? ? ? ? ? ? ? ? @AddressLine2 = @AddressLine2,? ? ? ? ? ? ? ? ? ? ? ? ? @City = @City,? ? ? ? ? ? ? ? ? ? ? ? ? @PostalCode = @PostalCode,? ? ? ? ? ? ? ? ? ? ? ? ? @StateProvinceID = @StateProvinceIDGOSETNOCOUNTOFF
本節(jié)我們講解了如何在存儲過程中使用OPTION(COMPILE)童芹，并且使得存儲過程性能達到最佳，我想這是根據(jù)實際場景分析存儲過程相對來說首選和最佳的方法鲤拿，以至于我們不必每次都重新編譯假褪。從而給我們長期更好的可擴展性。
版權聲明：本文內(nèi)容由互聯(lián)網(wǎng)用戶自發(fā)貢獻近顷，版權歸作者所有生音，本社區(qū)不擁有所有權，也不承擔相關法律責任窒升。如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容缀遍，歡迎發(fā)送郵件至：yqgroup@service.aliyun.com?進行舉報，并提供相關證據(jù)饱须，一經(jīng)查實域醇，本社區(qū)將立刻刪除涉嫌侵權內(nèi)容。
