關於T-SQL重編譯那點事，WITH RECOMPILE和OPTION(RECOMPILE)區別僅僅是存儲過程級重編譯和SQL語句級重編譯嗎

来源:http://www.cnblogs.com/wy123/archive/2017/01/09/6262800.html
-Advertisement-
Play Games

本文出處：http://www.cnblogs.com/wy123/p/6262800.html 在考慮重編譯T-SQL(或者存儲過程)的時候，有兩種方式可以實現強制重編譯（前提是忽略導致重編譯的其他因素的情況下，比如重建索引，更新統計信息等等）， 一是基於WITH RECOMPILE的存儲過程級別 ...


 

本文出處：http://www.cnblogs.com/wy123/p/6262800.html 

 

　   在考慮重編譯T-SQL(或者存儲過程)的時候，有兩種方式可以實現強制重編譯（前提是忽略導致重編譯的其他因素的情況下，比如重建索引，更新統計信息等等），
　　一是基於WITH RECOMPILE的存儲過程級別重編譯，另外一種是基於OPTION(RECOMPILE)的語句級重編譯。
　　之前瞭解的比較淺，僅僅認為是前者就是編譯整個存儲過程中的所有的語句，後者是重編譯存儲過程中的某一個語句，也沒有追究到底是不是僅僅只有這麼一點區別。
　　事實上在某些特定情況下，兩者的區別並非僅僅是存儲過程級重編譯和語句級重編譯的區別，
　　從編譯生成的執行計劃來看，這兩種強制編譯的方式內在機制差異還是比較大的。
　　這裡同時引申出來另外一個問題：The Parameter Embedding Optimization（怎麼翻譯？也沒有中文資料中提到The Parameter Embedding Optimization，勉強翻譯為“參數植入優化”）

　　本文通過一個簡單的示例來說明這兩者的區別(測試環境為SQL Server2014)。這裡首先感謝UEST同學提供的參考資料和指導建議。

 

WITH RECOMPILE 和 OPTION(RECOMPILE)使用上的區別

　　關於存儲過程級別的重編譯，典型用法如下，在存儲過程參數之後指定“WITH RECOMPILE”　

CREATE PROCEDURE TestRecompile_WithRecompile
(
    @p_parameter int
)WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
END
GO

 

　　關於語句級重編譯，典型用法如下，在某一條SQL語句的末尾指定OPTION(RECOMPILE)

CREATE PROCEDURE TestRecompile_OptionRecompile
(
    @p_parameter VARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL OPTION(RECOMPILE)
END
GO

 

　　按照慣例，先搭建一個測試環境
　　創建一張TestRecompile的表，也即上面存儲過程中用到的表，插入100W行數據，Id欄位上建立一個名字為idx_id的索引

CREATE TABLE TestRecompile
(
    Id int,
    Value varchar(50)
)
GO

DECLARE @i int = 0
WHILE @i<=1000000
BEGIN
    INSERT INTO TestRecompile VALUES (@i,NEWID())
    SET @i = @i+1
END


CREATE INDEX idx_Id ON TestRecompile(Id)
GO

 

WITH RECOMPILE 和 OPTION(RECOMPILE)使用時重編譯生成的執行計劃的異同

　　如果說With Recompile存儲過程級的重編譯和Option Recompile的SQL語句級的重編譯效果是一樣的話，
　　由上面的存儲過程可知，存儲過程中僅僅只有一句SQL代碼，那麼存儲過程級別的重編譯和SQL語句級別的重編譯都是編譯這一句SQL
　　如果這樣的話，兩者在輸入同樣參數的情況下執行計劃也應該是一樣的，那麼到底一樣不一樣呢？

　　首先來看TestRecompile_WithRecompile這個存儲過程的執行計劃，可以看到是一個索引掃描(INDEX SCAN)

　　

　　然後再來看TestRecompile_OptionRecompile的執行計劃，帶入同樣的參數

　　

　　至此，可以看出，雖然都用到索引，很明顯第一個語句是索引掃描（INDEX SCAN），第二個語句是索引查找（INDEX SEEK）
　　可以證明：在存儲過程級指定 WITH RECOMPILE 強制重編譯 和SQL語句級指定的OPTION(RECOMPILE)強制重編譯，相同條件下生成的執行計劃是不一樣的。

 

為什麼WITH RECOMPILE強制重編譯 和 OPTION(RECOMPILE)強制重編譯得到的執行計劃是不一樣的？

　　WITH RECOMPILE強制重編譯是每次運行存儲過程，都根據當前的參數情況做一次重編譯，
　　首先我們暫時先不糾結為什麼第一種方法用不到索引查找（INDEX的SEEK）。
　　事實上正式因為使用了Id = @p_parameter OR @p_parameter IS NULL這種寫法導致的，具體我後面做解釋。
　　那麼對於OPTION(RECOMPILE)強制重編譯存儲過程中同樣寫法的SQL語句，為什麼有能用到索引了呢？
　   因為在用OPTION(RECOMPILE)強制重編譯的時候，這裡涉及到一個“Parameter Embedding Optimization”編譯問題，
　　事實上我之前也沒有聽說過這個名詞，直譯過來就是“參數植入編譯”（不知道恰不恰當）
　   OPTION(RECOMPILE)強制重編譯在一定程度上增強和優化重編譯的效果，
　　參考這裡:https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options，文章中分析的極度牛逼，案例也非常精彩

 

　　原文中是這麼解釋的：
　　The Parameter Embedding Optimization takes this process a step further: query parameters are replaced with literal constant values during query parsing.
　　The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.
　    翻譯過來大概意思就是：
　　相比WITH RECOMPILE這種強制重編譯的方式，OPTION(RECOMPILE)中的Parameter Embedding Optimization機制更進一步，解析查詢的過程中，參數值被字面常量所替代
　　解析器神奇地把複雜的問題簡化。至於怎麼簡化了，還是強烈建議參考原文，演示的案例相當弔。

 

　　至於怎麼簡化，這裡大概做一下解釋，原文中的解釋更加詳細和有趣。
　　首先，SQL語句是這麼寫的：SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL
　　當“植入參數”之後，也即上文中使用的@p_parameter = 123456，SQL語句變成了SELECT * FROM TestRecompile WHERE Id = 12345 OR 12345 IS NULL
　　因為OR 12345 IS NULL是永遠不成立的，甚至可以認為是將SQL語句直接簡化成了SELECT * FROM TestRecompile WHERE Id = 12345 ，這樣子的話，在當前情況下，肯定是可以用到索引的。
　　因此，OPTION(RECOMPILE)強制重編譯的SQL在編譯並且簡化之後，就變成瞭如下的SQL，這裡解釋還是感覺有點牽強的，沒有原文有說服力。

 　　

　　那麼再回頭看WITH RECOMPILE強制重編譯，WITH RECOMPILE強制重編譯的時候，沒有能夠做到OPTION(RECOMPILE)強制重編譯中的“解析器神奇地把複雜的問題簡化”
　　參考這個鏈接：http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
　　對於類似WHERE Id = @p_parameter OR @p_parameter IS NULL這種查詢方式，

　　上述文章中是這麼解釋的：
　　The problem with these types of queries is that there is no stable plan.
　　The optimal plan differs completely depending on what paramters are passed.
　　The optimiser can tell that and it plays safe. It creates plans that will always work. That’s (one of the reasons) why in the first example it was an index scan, not an index seek.
　　翻譯過來大概意思就是：
　　這種類型的查詢問題在於沒有固定的執行計劃，
　　優化方案是基於具體傳入進來的參數值的，
　　優化器只能做到保證安全性（plays safe），他創建的執行計劃確保總是可以正常工作的。

 

　　我這裡補充解釋一下 it plays safe在我的理解：
　　如果@p_parameter 參數非空，走索引Seek完全沒有問題。
　   如果@p_parameter 為null，此時and (Id= @p_parameter or @p_parameter is null )這個條件恆成立，如果再走索引Seek會出現什麼結果？
　   如果繼續採用Index Seek的方式執行，語義上變成了是查找Id為null的值，這樣的話邏輯上已經錯誤了。
　　因此出現這種寫法，為了安全起見（上文所謂的plays safe），優化器只能選擇一個這種的索引的掃描的方案（所謂的always work的執行計劃）

　　關於OPTION(RECOMPILE)在SQL語句級重編譯神奇的魔力，他會根據具體的參數做到真正的重編譯，我們在做一個測試：
　　這一次設置@p_parameter = null，看看是不是又重新編譯了一個合理的執行計劃，沒錯，這次它生成了一個全表掃描的執行計劃，也是沒有問題的。
　　唯一有瑕疵的地方時，相對WITH RECOMPILE強制重編譯的方式，他的執行計劃沒有用到並行。這也是WITH RECOMPILE和OPTION(RECOMPILE)兩種強制重編譯生成執行計劃的區別
　　但是不能否認OPTION(RECOMPILE)強制重編譯中的Parameter Embedding Optimization這種優化機制的特性

　　

　　而此時WITH RECOMPILE強制重編譯方式的執行計劃，在傳入參數值為null的時候，生成的是並行的執行計劃

　　

 

現在來解釋為什麼非常強烈不建議寫這種SQL：SELECT * FROM TestRecompile WHERE Id = @p_parameter OR @p_parameter IS NULL

　　我在之前也寫過http://www.cnblogs.com/wy123/p/5958047.html，感覺沒有徹底解釋清楚索引抑制問題的原因。

　　開發中常見的一個潛在的多個條件的查詢SQL，具體的查詢條件是依賴於用戶輸入的，
　　比如提供給用戶三個查詢條件可選的查詢條件，用戶可以輸入一個，兩個或者三個，這個太常見了，也不用再解釋了
　　那麼我們就要構造出適應這種查詢的一種方案
　　面對這種catch-all-queries的查詢方式，其中方案之一就是類似於這種寫法
　　SELECT * FROM TestRecompile
　　WHERE (parameter1 = @p_parameter1 OR @p_parameter1 IS NULL)
　　　　and (parameter2 = @p_parameter2 OR @p_parameter2 IS NULL)
　　　　and (parameter3 = @p_parameter3 OR @p_parameter3 IS NULL)
　　這種最大的問題就是在查詢列上有索引，且查詢列上接收到的輸入參數非空的時候，是會抑制到索引的使用的
　　上文中演示了，雖然用到了Id 列上的索引，採用的是INDEX SCAN，比全表掃描（TABLE SCAN）強一點點，他跟真正用到INDEX SEEK在效率上講，完全是兩碼事，
　　所以我們在開發的過程中強烈不建議使用 Id = @p_parameter OR @p_parameter IS NULL這種寫法，
　　當然，在不考慮parameter sinffing問題的時候，我們首選參數化動態SQL，即便是非參數化動態SQL（EXEC的方式執行一個拼湊出來的字元串），也比Id = @p_parameter OR @p_parameter IS NULL這種方式好
　　如果有人進一步問：為什麼查詢條件中Id = @p_parameter OR @p_parameter IS NULL這種寫法會抑制到索引的使用，真的是一個很難解釋清楚的問題，解釋不清楚也是一件很尷尬的事。
　　這種邏輯之所以抑制到索引的最優化使用，真如上文分析的，優化器沒有真正的用到INDEX SEEK，是為了安全起見（上文所謂的plays safe）考慮
　　說道到這裡我又開始凌亂了，也就是WITH RECOMPILE和OPTION(RECOMPILE)這兩種方式的造強制，有一種只可意會不可言傳的感覺。
　　這就是即便是編譯的過程中知道具體的參數值，也做到編譯出來INDEX SEEK的執行計劃的原因
　　總是我在http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/這裡找到了跟我對該問題理解的相似的解釋，也算釋懷了

 

總結：本文通過一個簡單的案例，解釋了WITH RECOMPILE和OPTION(RECOMPILE)這種強制重編譯方式的區別，以及引申出來的The Parameter Embedding Optimization（第一次聽說）優化機制。
　　　很多時候，自己對一些知識只是想當然地去理解和使用，比如隨意使用WITH RECOMPILE和OPTION(RECOMPILE)，
　　　粗暴地認為這兩種強制重編譯的方式區別僅僅在於一個是存儲過程級的重編譯，一個是SQL語句級的重編譯。真正拿著case測試的時候，才發現，還真不一樣。

 

下一篇寫一個跟這個機制類似的同樣有意思的文章。

 

參考資料：https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

　　　　　http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

　　　　   http://www.sommarskog.se/dyn-search-2008.html

　　　　　同時，再次感謝Uest同學提供的參考資料和指導建議。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 數據意識崛起，從企業應用看BI軟體的未來發展
    未來的BI市場是巨大的，涉及的觸角也會越來越多。就我觀察到的企業應用以及數據類產品中發現，很多企業通過數據介面引入了更多外部的數據來完善分析，也有部分企業私有化部署的BI產品在往雲BI方向發展。與R語言、數據挖掘類技術的集成，也將進一步提高數據分析的深度。 ...
  • 關於T-SQL重編譯那點事，內聯函數和表值函數在編譯生成執行計劃的區別
    本文出處：http://www.cnblogs.com/wy123/p/6266724.html 最近在學習 WITH RECOMPILE和OPTION(RECOMPILE)在重編譯上的區別的時候，無意中發現表值函數和內聯表值函數編譯生成執行計劃的區別下文中將會對此問題展開討論。簡單地說就是：同樣一 ...
  • Oracle / PLSQL寫語句的時候常使用的函數
    下麵開始記錄一下，自己在Oracle或者PLSQL常用的幾個函數， 顯示效果如下圖所示： 與此同時，和他相同的函數是下麵的函數 顯示效果如下圖所示： ...
  • 創建商城數據表
    ...
  • Mac安裝mysql
    雙擊pkg文件安裝 一路向下，記得保存最後彈出框中的密碼（它是你mysql root賬號的密碼） 正常情況下，安裝成功。 此時只是安裝成功，但還需要額外的配置： (1) 進入系統偏好設置 (2) 點擊mysql 開啟mysql服務 此時我們在命令行輸入mysql -uroot -p命令會提示沒有co ...
  • memcached學習——memcached的記憶體分配機制Slab Allocation、記憶體使用機制LRU、常用監控記錄（四）
    記憶體分配機制Slab Allocation 本文參考博客：https://my.oschina.net/bieber/blog/505458 Memcached的記憶體分配是以slabs為單位的，會根據初始chunk大小、增長因數、存儲數據的大小實際劃分出多個不同的slabs class，slab c ...
  • memcached學習——大綱簡介 && 安裝(基於centos6.5)、啟動、關閉memcached（一）
    大綱簡介 安裝前，先簡單介紹一下memcached。 memcached是一個免費、開源、高性能的分散式緩存。設計memcached的初衷是為了加快web應用程式，減少DB負載。 安裝要求：支持大多數linux和基於BSD的系統，官方沒有給出windows版本，但是網上有memcached for ...
  • twemproxy代碼框架概述——剖析twemproxy代碼前編
    本篇將去探索twemproxy源碼的主幹流程，想來對於想要開始啃這份優秀源碼生肉的童鞋會有不小的幫助。這裡我們首先要找到 twemproxy正確的打開方式——twemproxy的文件結構，接著介紹twemproxy程式代碼框架，最後介紹twemproxy程式的主幹流程。主幹流程是本章節的重中之重。這 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類