SQLServer 參數化查詢經歷分享。本站提示廣大學習愛好者：（SQLServer 參數化查詢經歷分享）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLServer 參數化查詢經歷分享正文
甚麼是參數化查詢?
一個簡略懂得參數化查詢的方法是把它看作只是一個T-SQL查詢，它接收掌握這個查詢前往甚麼的參數。經由過程應用分歧的參數，一個參數化查詢前往分歧的成果。要取得一個參數化查詢，你須要以一種特定的方法來編寫你的代碼，或它須要知足一組特定的尺度。
有兩種分歧的方法來創立參數化查詢。第一個方法是讓查詢優化器主動地參數化你的查詢。另外一個方法是經由過程以一個特定方法來編寫你的T-SQL代碼，並將它傳遞給sp_executesql體系存儲進程，從而編程一個參數化查詢。這篇文章的前面部門將引見這個辦法。
參數化查詢的症結是查詢優化器將創立一個可以重用的緩存籌劃。經由過程主動地或編程應用參數化查詢，SQL Server可以優化相似T-SQL語句的處置。這個優化清除了對應用高尚資本為這些相似T-SQL語句的每次履行創立一個緩存籌劃的需求。並且經由過程創立一個可重用籌劃，SQL Server還削減了寄存進程緩存中相似的履行籌劃所需的內存應用。
如今讓我們看看使得SQL Server創立參數化查詢的分歧方法。
參數化查詢是如何主動創立的?
微軟編寫查詢優化器代碼的人養精蓄銳地優化SQL Server處置你的T-SQL敕令的方法。我想這是查詢優化器稱號的由來。這些盡可能削減資本和最年夜限制地進步查詢優化器履行機能的辦法之一是檢查一個T-SQL語句並肯定它們能否可以被參數化。要懂得這是若何任務的，讓我們看看上面的T-SQL語句：
SELECT *
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesOrderID = 56000;
GO在這裡，你可以看到這個敕令有兩個特色。第一它簡略，第二它在WHERE謂詞中包括一個用於SalesOrderID值的指定值。查詢優化器可以辨認這個查詢比擬簡略和SalesOrderID有一個參數(“56000”)。是以，查詢優化器可以主動地參數化這個查詢。
假如你應用上面的SELECT語句來檢查一個只包括用於下面語句的緩存籌劃的、清潔的緩沖池，那末你會看到查詢優化器將T-SQL查詢重寫為一個參數化T-SQL語句：
SELECT stats.execution_count AS cnt,
p.size_in_bytes AS [size],
[sql].[text] AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats
ON stats.plan_handle = p.plan_handle;
GO當我在一個SQL Server 2008實例上運轉這個敕令時，我獲得上面的輸入，(留意，輸入被從新格局化了，以便它更容易讀)：
cnt size plan_text
--- ------- --------------------------------------------------------------
1 49152 (@1 int)SELECT * FROM [AdventureWorks].[Sales].[SalesOrderHeader]
WHERE [SalesOrderID]=@1
假如你看看下面輸入中的plan_text字段，你會看到它不像本來的T-SQL文本。如前所述，查詢優化器將這個查詢從新編寫為一個參數化T-SQL語句。在這裡，你可以看到它如今有一個數據類型為(int)的變量(@1)，它在之前的SELECT語句中被界說的。別的在plan_text的末尾， 值“56000”被調換為變量@1。既然這個T-SQL語句被重寫了，並且被存儲為一個緩存籌劃，那末假如將來一個T-SQL敕令和它年夜致雷同，只要SalesOrderID字段被賦的值分歧的話，它便可以被用於重用。讓我們在舉措中看看它。
假如我在我的機械上運轉上面的敕令：
DBCC FREEPROCCACHE;
GO
SELECT *
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesOrderID = 56000;
GO
SELECT *
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesOrderID = 56001;
GO
SELECT stats.execution_count AS cnt,
p.size_in_bytes AS [size],
[sql].[text] AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats
ON stats.plan_handle = p.plan_handle;
GO
我從最初的SELECT語句獲得上面的輸入，(留意，輸入被從新格局化以便它更容易讀)：
cnt size plan_text
--- -------- --------------------------------------------------------------
2 49152 (@1 int)SELECT * FROM AdventureWorks].[Sales].[SalesOrderHeader]
WHERE [SalesOrderID]=@1
在這裡，我起首釋放進程緩存，然後我履行兩個分歧、但卻相似的非參數化查詢來看看查詢優化器是會創立兩個分歧的緩存籌劃照樣創立用於這兩個查詢的一個緩存籌劃。在這裡，你可以看到查詢優化器現實上很聰慧，它參數化第一個查詢並緩存了籌劃。然後當第二個相似、但有一個分歧的SalesOrderID值的查詢發送到SQL Server時，優化器可以辨認曾經緩存了一個籌劃，然後重用它來處置第二個查詢。你可以這麼說是由於“cnt”字段如今注解這個籌劃被用了兩次。
數據庫設置裝備擺設選項PARAMETERIZATION可以影響T-SQL語句如何被主動地參數化。關於這個選項有兩種分歧的設置，SIMPLE和FORCED。當PARAMETERIZATION設置被設置為SIMPLE時，只要簡略的T-SQL語句才會被參數化。要引見這個，看下上面的敕令：
SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56000
這個查詢相似於我後面的示例，除在這裡我添加了一個額定的JOIN尺度。當數據庫AdventureWorks的PARAMETERIZATION選項被設置為SIMPLE時，這個查詢不會被主動地參數化。SIMPLE PARAMETERIZATION設置告知查詢優化器只參數化簡略的查詢。然則被選項PARAMETERIZATION被設置為FORCED時，這個查詢將被主動地參數化。
當你設置數據庫選項為應用FORCE PARAMETERIZATION時，查詢優化器試圖參數化一切的查詢，而不只僅是簡略的查詢。你能夠會以為這很好。然則在某些情形下，當數據庫設置PARAMETERIZATION為FORCED時，查詢優化器將選擇不是很幻想的查詢籌劃。當數據庫設置PARAMETER為FORCED時，它轉變查詢中的字面常量。這能夠招致當查詢中觸及盤算字段時索引和索引視圖不被選中介入到履行籌劃中，從而招致一個有效的籌劃。FORCED PARAMETERIZATION選項能夠是改良具有年夜量相似的、傳遞過去的參數稍有分歧的查詢的數據庫機能的一個很好的處理計劃。一個在線發賣運用法式，它的客戶對你的產物履行年夜量的相似搜刮， 產物值分歧，這能夠是一個可以或許受害於FORCED PARAMETERIZATION的很好的運用法式類型。
不是一切的查詢從句都邑被參數化。例如查詢的TOP、TABLESAMPLE、 HAVING、GROUP BY、ORDER BY、OUTPUT...INTO或FOR XML從句不會被參數化。
應用sp_execute_sql來參數化你的T-SQL
你不須要依附於數據庫的PARAMETERIZATION選項來使得查詢優化器參數化一個查詢。你可以參數化你本身的查詢。你經由過程從新編寫你的T-SQL語句並應用“sp_executesql”體系存儲進程履行重寫的語句來完成。正如曾經看到的，下面包含一個“JOIN”從句的SELECT語句在數據庫的PARAMETERIZATION設置為SIMPLE時沒有被主動參數化。讓我從新編寫這個查詢以便查詢優化器將創立一個可重用的參數化查詢履行籌劃。
為了解釋，讓我們看兩個相似的、不會被主動參數化的T-SQL語句，並創立兩個分歧的緩存履行籌劃。然後我將從新編寫這兩個查詢使得它們都應用雷同的緩存參數化履行籌劃。
讓我們看看這個代碼：
DBCC FREEPROCCACHE
GO
SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56000
GO
SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56001
GO
SELECT stats.execution_count AS cnt,
p.size_in_bytes AS [size],
LEFT([sql].[text], 200) AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle;
GO
在這裡，我釋放了進程緩存，然後運轉這兩個包括一個JOIN的、分歧的非簡略的T-SQL語句。然後我將檢討緩存籌劃。這是這個應用DMV 的SELECT語句的輸入(留意，輸入被從新格局化了，以便它更容易讀)：
cnt size plan_text
--- ----------- -------------------------------------------------------------------------------
1 49152 SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D
ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56001
1 49152 SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D
ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56000正如你從這個輸入看到的，這兩個SELECT語句沒有被查詢優化器參數化。優化器創立了兩個分歧的緩存履行籌劃，每個都只被履行了一次。我們可以經由過程應用sp_executesql體系存儲進程來贊助優化器為這兩個分歧的SELECT語句創立一個參數化履行籌劃。
上面是下面的代碼被從新編寫來應用sp_executesql 體系存儲進程：
DBCC FREEPROCCACHE;
GO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56000;
GO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56001;
GO
SELECT stats.execution_count AS exec_count,
p.size_in_bytes AS [size],
[sql].[text] AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle;
GO好像你所看到的，我從新編寫了這兩個SELECT語句，使它們經由過程應用“EXEC sp_executesql”語句來履行。對這些EXEC語句中的每個，我都傳遞三個分歧的參數。第一個參數是根本的SELECT語句，然則我將SalesOrderID的值用一個變量(@SalesOrderID)替換。在第二個參數中，我肯定了@SalesOrderID的數據類型，在這個例子中它是一個integer。然後在最初一個參數中，我傳遞了SalesOrderID的值。這個參數將掌握我的SELECT依據SalesOrderID值所生成的成果。sp_executesql的每次履行中前兩個參數都是一樣的。然則第三個參數分歧，由於每一個都有分歧的SalesOrderID值。
如今當我運轉下面的代碼時，我從DMV SELECT語句獲得上面的輸入(留意，輸入被從新格局化了，以便它更容易讀)：
cnt size plan_text
--- ----------- -----------------------------------------------------------------------------------------
2 49152 (@SalesOrderID INT)SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = @SalesOrderID
從這個輸入，你可以看出，我有一個參數化緩存籌劃，它被履行了兩次，為每一個EXEC語句各履行了一次。
應用參數化查詢來節儉資本和優化機能
在語句可以被履行之前，每一個T-SQL語句都須要被評價，並且須要樹立一個履行籌劃。創立履行籌劃會占用名貴的CPU資本。當履行籌劃被創立後，它應用內存空間將它存儲在進程緩存中。下降CPU和內存應用的一個辦法是應用參數化查詢。雖然數據庫可以被設置為對一切查詢FORCE參數化，然則這不老是最好的選擇。經由過程懂得你的哪些T-SQL語句可以被參數化然後應用sp_executesql存儲進程，你可以贊助SQL Server節儉資本並優化你的查詢的機能。
