SQL參數化查詢的另一個理由命中執行計劃

2020-07-25 13:14:02

字體：大中小

來源：轉載

供稿：網友

1概述

SQL語言的本質就是一串偽代碼，表達的是做什么，而不是怎么做的意思。如其它語言一樣，SQL語句需要編譯之后才能運行，所以每一條SQL是需要通過編譯器解釋才能運行的(在這之間還要做SQL的優化)。而這些步驟都是需要運行成本，所以在數據庫中有一個叫做執行計劃的東西，編譯器會將編譯過后的SQL存入執行計劃當中，當遇到同樣的SQL時，就直接調用執行計劃來執行，而不需要再次編譯。
通過對上面執行計劃的認識，為了提高數據庫運行的效率，我們需要盡可能的命中執行計劃，這樣就可以節省運行時間。

2相關SQL

2.1查看當前數據庫中所有的執行計劃：

復制代碼代碼如下:

 
SELECT cp.usecounts AS '使用次數' 
,objtype AS '類型' 
,st.[text] AS 'SQL文本' 
,plan_handle AS '計劃句柄' 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE st.text not like '%sys%' 

2.2刪除執行計劃

復制代碼代碼如下:

 
--刪除所有計劃 
DBCC FREEPROCCACHE 

2.3測試腳本（創建員工表，并向其插入1000條數據）

復制代碼代碼如下:

 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')) 
DROP TABLE [dbo].Employee 
GO 
--人員表 
CREATE TABLE dbo.Employee 
( 
id int, 
name nvarchar(50) 
); 

--插入測試數據 
DECLARE @I INT=0,@ENDI INT=1000; 
WHILE(@I<@ENDI) 
BEGIN 
SET @I+=1; 
INSERT dbo.Employee(id,name) VALUES(@I,'蔣大華'+CAST(@I AS NVARCHAR(20))); 
END; 

3測試執行計劃

3.1 先執行刪除所有執行計劃，然后執行SELECT * FROM Employee ，最后查看執行計劃（2.1中的查看執行計劃腳本）如下圖

即SQL SERVER會為每一條SQL建立一個執行計劃，并將它緩存起來

3.2 再運行一次SQL: SELECT * FROM Employee,并查看執行計劃

可以看到這個計劃的重用次數為2，即這個計劃被重用了；

3.3 修改SQL：SELECT * FROM Employee（在SELECT后多加一個空格），執行并查看執行計劃

結果又新添加一個執行計劃，即SQL SERVER認為這是兩個不同的SQL語句并分別建立了執行計劃；

4重用執行計劃――使用參數化查詢方法

4.1 未參數化SQL

復制代碼代碼如下:

 
string selectCmdText = string.Format(@"SELECT * FROM Employee WHERE name='{0}'",” 蔣大華1”); 
SQLHelper.ExecuteNonQuery(SQLHelper.DefaulConnectiontString, System.Data.CommandType.Text, selectCmdText, null); 

查看執行計劃：

即當執行一個未參數化SQL時，SQL SERVER需要先將其轉換成一個參數SQL并執行它。一共需要兩執行計劃

然后再執行下面的代碼（查詢的條件變了）

復制代碼代碼如下:

 
string selectCmdText = string.Format(@"SELECT * FROM Employee WHERE name='{0}'",” 蔣大華2”); 
SQLHelper.ExecuteNonQuery(SQLHelper.DefaulConnectiontString, System.Data.CommandType.Text, selectCmdText, null); 

查看執行計劃

此時不需要再準備一個準備的SQL，但還是需要再產生一個執行計劃，并緩存下來；

4.2 參數化SQL

復制代碼代碼如下:

 
SqlParameter[] param = { new SqlParameter("@name", txtEmployeeName.Text.Trim()) }; 
string selectCmdText = string.Format(@"SELECT * FROM Employee WHERE name=@name"); 
SQLHelper.ExecuteNonQuery(SQLHelper.DefaulConnectiontString, System.Data.CommandType.Text, selectCmdText, param); 

輸入參數并執行，然后查看執行計劃：