SQL Server-聚焦什麼時候用OPTION(COMPILE)呢？

在SQL SERVER中任何時候第一次呼叫儲存過程時，此時儲存過程將會被SQL SERVER優化且查詢計劃在記憶體中會被快取。由於查詢計劃快取，當執行相同的儲存過程時，它都將使用相同的查詢計劃，從而無需每次執行時對同一儲存過程進行優化和編譯。因此，如果我們需要每天執行相同的儲存過程若干次，那麼可以節省大量的時間和硬體資源。

如果每次執行的儲存過程中的在WHERE子句中具有相同的引數，則重複使用儲存過程的相同查詢計劃是有意義的。但是，如果執行相同的儲存過程，但是引數的值會改變呢？發生什麼取決於引數的典型性。如果儲存過程的引數的值從執行到執行相似，那麼快取的查詢計劃將正常工作，查詢將按照執行最佳來。但是，如果引數不是典型的，那麼被重用的快取查詢計劃可能不是最優的，導致查詢執行更慢，因為它使用的查詢計劃並不是真正為所使用的引數設計的。下面我們藉助AdventureWorks2012例項資料庫來用例項講解上述所描述的情況。

DECLARE
    @AddressLine1 NVARCHAR(60) = NULL,
    @AddressLine2 NVARCHAR(60) = NULL,
    @City NVARCHAR(30) = NULL,
    @PostalCode NVARCHAR(15) = NULL,
    @StateProvinceID INT = NULL 

SET @City = 'Bothell'
SET @PostalCode = '98011'
SET @StateProvinceID = 79

DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(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 (@AddressLine1 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine1 LIKE ''%'' + @AddressLine1 + ''%'''

IF (@AddressLine2 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine2 LIKE ''%'' + @AddressLine2 + ''%'''

IF (@City IS NOT NULL)
    SET @SQL = @SQL + ' AND a.City LIKE ''%'' + @City + ''%'''

IF (@PostalCode IS NOT NULL)
    SET @SQL = @SQL + ' AND a.PostalCode LIKE ''%'' + @PostalCode + ''%'''

IF (@StateProvinceID IS NOT NULL)
    SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID' 

SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),
                         @AddressLine2 NVARCHAR(60),
                         @City NVARCHAR(30),
                         @PostalCode NVARCHAR(15),
                         @StateProvinceID INT'

EXECUTE sp_executesql @SQL,@ParamDefinition,
                           @AddressLine1  = @AddressLine1,
                           @AddressLine2 = @AddressLine2,
                           @City = @City,
                           @PostalCode = @PostalCode,
                           @StateProvinceID = @StateProvinceID

GO
複製程式碼

我們執行上述查詢1次，看到查詢結果如下和計劃快取次數如下：

此時我們將外部變數StateProvinceID型別修改為SMALLINT，然後再來執行查詢和快取計劃，此時會出現查詢計劃使用次數是為2，還是出現兩條次數都為1呢？

此時我們再來將動態SQL中內部變數StateProvinceID型別修改為SMALLINT，此時會出現查詢計劃使用次數是為3，還是出現兩條，次數分別為2和1呢？

由上可知，如果我們修改外部變數引數型別不會影響查詢計劃快取即會達到重用目的，若修改動態SQL內部變數引數型別則不會重用查詢計劃快取。

大多數情況下，我們可能不需要擔心上述問題。但是，在某些情況下，假設從查詢的執行到執行的引數變化很大，則會引起問題。

如果我們確定儲存過程通常執行正常，但有時執行緩慢，則很可能會看到上述問題。在這種情況下，我們可以做的是改變儲存過程，並新增WITH RECOMPILE選項。

新增此選項後，儲存過程將始終重新編譯自身，並在每次執行時建立一個新的查詢計劃。當然這會消除查詢計劃重用的好處，但確保了每次執行查詢時都使用正確的查詢計劃。如果儲存過程中有多個查詢，那麼它將重新編譯儲存過程中的所有查詢，即使那些不受非典型引數影響的查詢也是如此。

講完OPTION(COMPILE)，接下來我們講講如何建立高效能的儲存過程。有些童鞋可能會建立如下儲存過程。

CREATE PROC [dbo].[HighPerformanceExample]
(
    @AddressLine1 NVARCHAR(60) = NULL,
    @AddressLine2 NVARCHAR(60) = NULL,
    @City NVARCHAR(30) = NULL,
    @PostalCode NVARCHAR(15) = NULL,
    @StateProvinceID SMALLINT = NULL 
)
AS 
SET NOCOUNT ON

SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid 
FROM Person.Address AS a
WHERE (a.AddressLine1 = @AddressLine1 OR @AddressLine1 IS NULL) AND
      (a.AddressLine2 = @AddressLine2 OR @AddressLine2 IS NULL) AND
      (a.City = @City OR @City IS NULL) AND
      (a.PostalCode = @PostalCode OR @PostalCode IS NULL) AND
      (a.StateProvinceID = @StateProvinceID OR @StateProvinceID IS NULL)

--或者
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid 
FROM Person.Address AS a
WHERE a.AddressLine1 = COALESCE(@AddressLine1, a.AddressLine1) AND
      a.AddressLine2 = COALESCE(@AddressLine2, a.AddressLine2) AND
      a.City = COALESCE(@City, a.City) AND
      a.PostalCode = COALESCE(@PostalCode, a.PostalCode) AND
      a.StateProvinceID = COALESCE(@StateProvinceID, a.StateProvinceID) 

--或者
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid 
FROM Person.Address AS a
WHERE a.AddressLine1 = CASE WHEN @AddressLine1 IS NULL THEN a.AddressLine1 ELSE @AddressLine1 END
 AND  a.AddressLine2 = CASE WHEN @AddressLine2 IS NULL THEN a.AddressLine1 ELSE @AddressLine2 END
 AND  a.City = CASE WHEN @City IS NULL THEN a.City ELSE @City END
 AND  a.PostalCode = CASE WHEN @PostalCode IS NULL THEN a.PostalCode ELSE @PostalCode END
 AND  a.StateProvinceID = CASE WHEN @StateProvinceID IS NULL THEN a.StateProvinceID ELSE @StateProvinceID END 


GO

SET NOCOUNT OFF

複製程式碼

上述無論怎樣執行都將表現的非常糟糕。因為SQL SERVER不能將其很好地進行優化，如果這是由不同的引數組合產生，那麼我們可能會得到一個絕對糟糕的計劃。不難理解，當執行一個儲存過程，並且還沒有生成一個查詢快取計劃。所以，管理員可能會更新統計資訊或強制重新編譯（或者，甚至重新啟動SQL Server）來嘗試解決此問題，但這些都不是最佳解決方案。OPTION(COMPILE)重新編譯是個好東西，但是我們是不是像如下簡單加上重新編譯就可以了呢。

SELECT ...
FROM ...
WHERE ...
OPTION (RECOMPILE);
複製程式碼

如果我們要使用重新編譯，那麼我們是否需要考慮以下兩個問題呢？

如果我們知道一個特定的語句總是返回相同數量的行並使用相同的計劃（並且我們已測試過並知道這一點），那麼我們會正常建立儲存過程並讓計劃得到快取。
如果我們知道一個特定的語句從執行到執行是不一樣的，最佳查詢計劃也會有所不同（我們也應該從執行多個測試樣本中知道這一點），然後我們會如正常一樣建立儲存過程，然後使用OPTION(RECOMPILE)以確保語句的計劃不會被儲存過程快取或儲存。在每次執行時，儲存過程將獲得不同的引數，如此一來語句將在每次執行時得到一個新的計劃。

上述已經描述的很明朗了，使用或者不使用重新編譯的前提不過是需不需要查詢計劃快取還是重新生成一個查詢計劃，但是我們怎麼知道到底是否需要查詢計劃快取呢？這就要看對建立的儲存過程是否穩定了，如果穩定我們就從快取中去取，否則使用重新編譯查詢。歸根結底一句話概述：重新編譯不穩定（可變）的計劃，但為穩定（不可變）的計劃，我們把它們放在快取中重用。

為了實現這點，我們需要分析所查詢的儲存過程，例如在每個企業下有對應的使用者，我們想象一下所呈現的UI介面，首先是所有使用者，查詢條件則是企業下拉框，然後是使用者名稱或者員工工號等。當沒有任何篩選條件時則走查詢計劃快取，若選擇企業，或者還選擇了員工相關篩選條件則重新編譯。類似如下儲存過程。

CREATE PROC [dbo].[HighPerformanceExample]
(
    @AddressLine1 NVARCHAR(60) = NULL,
    @AddressLine2 NVARCHAR(60) = NULL,
    @City NVARCHAR(30) = NULL,
    @PostalCode NVARCHAR(15) = NULL,
    @StateProvinceID SMALLINT = NULL 
)
AS 
SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500),@Recompile  BIT = 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 (@StateProvinceID IS NOT NULL)
    SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID' 

IF (@AddressLine1 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine1 LIKE @AddressLine1'

IF (@AddressLine2 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine2 LIKE @AddressLine2'

IF (@City IS NOT NULL)
    SET @SQL = @SQL + ' AND a.City LIKE @City'

IF (@PostalCode IS NOT NULL)
    SET @SQL = @SQL + ' AND a.PostalCode LIKE @PostalCode'


IF (@StateProvinceID IS NOT NULL)
    SET @Recompile = 0

IF (PATINDEX('%[%_?]%',@AddressLine1) >= 4
        OR PATINDEX('%[%_?]%', @AddressLine2) = 0)
    AND (PATINDEX('%[%_?]%', @City) >= 4
        OR PATINDEX('%[%_?]%', @PostalCode) = 0)
    SET @Recompile = 0

IF @Recompile = 1
BEGIN
    SET @SQL = @SQL + N' OPTION(RECOMPILE)';
END;

SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),
                         @AddressLine2 NVARCHAR(60),
                         @City NVARCHAR(30),
                         @PostalCode NVARCHAR(15),
                         @StateProvinceID SMALLINT'

EXECUTE sp_executesql @SQL,@ParamDefinition,
                           @AddressLine1  = @AddressLine1,
                           @AddressLine2 = @AddressLine2,
                           @City = @City,
                           @PostalCode = @PostalCode,
                           @StateProvinceID = @StateProvinceID

GO

SET NOCOUNT OFF
複製程式碼

本節我們講解了如何在儲存過程中使用OPTION(COMPILE)，並且使得儲存過程效能達到最佳，我想這是根據實際場景分析儲存過程相對來說首選和最佳的方法，以至於我們不必每次都重新編譯。從而給我們長期更好的可擴充套件性。

版權宣告：本文內容由網際網路使用者自發貢獻，版權歸作者所有，本社群不擁有所有權，也不承擔相關法律責任。如果您發現本社群中有涉嫌抄襲的內容，歡迎傳送郵件至：yqgroup@service.aliyun.com 進行舉報，並提供相關證據，一經查實，本社群將立刻刪除涉嫌侵權內容。

原文連結

SQL Server-聚焦什麼時候用OPTION(COMPILE)呢？

探討OPTION(COMPILE)問題

相關文章