【轉】 參數化查詢

標籤：des   style   blog   ar   color   os   使用   sp   on   

SQL SERVER在執行查語句時會產生查詢計劃，並將查詢計劃緩衝在資料庫中，如果下次執行相同的SQL 語句時，會利用緩衝的執行計畫，而不必重新編譯產生執行計畫。

使用參數化查詢，可以提高查詢計劃的重用率，提高執行效率。這裡以 SQL SERVER 2005為例，分析一下SQL SERVER查詢的參數化。

在 SQL SERVER 2005中，可以使用以下 SQL SERVER語句查看緩衝的執行計畫：

1 SELECT usecounts, cacheobjtype, objtype, text2 3 FROM sys . dm_exec_cached_plans4 5 CROSS APPLY sys . dm_exec_sql_text ( plan_handle)6 7 ORDER BY usecounts DESC ;

 

分析如下查詢代碼的執行：

1 command.CommandText = "select * from t1 where col1 = ‘abc‘";2 command.ExecuteNonQuery();3 command.CommandText = "select * from t1 where col1 = ‘bcd‘";4 command.ExecuteNonQuery(); 

首先用 DBCC FreeProcCache命令清空緩衝，以免其它查詢計劃的緩衝影響分析，然後執行代碼，然後查看緩衝的計劃有兩個：

select * from t1 where col2 = ‘abc‘

select * from t1 where col2 = ‘bcd‘

可以看到 SQL SERVER為兩個查詢分別產生了一個查詢計劃，使用次數為 1，並沒有重用任何查詢計劃。

 

      但如果我們使用參數化查詢方式

1 command.CommandText = "select * from t1 where col1 = @str";2 command.Parameters.Add("@str", "abc");3 command.ExecuteNonQuery();4 command.Parameters[0].Value = "bcd";5 command.ExecuteNonQuery(); 

執行後產生的查詢計劃為

(@strnvarchar(3))Select * From t1 Where col1 = @str

 

SQL SERVER產生了一個參數化的查詢計劃，並且這個查詢計劃的使用次數為 2， SQL SERVER重用了這個查詢計劃。

      當然這樣參數化查詢並不是最好的，尤其是對字串類型的參數化查詢， SQL SERVER會為不同長度的參數產生不同的查詢計劃，如：

command.CommandText = "select * from t1 where col1 = @str";command.Parameters.Add("@str", "abc");command.ExecuteNonQuery();command.Parameters[0].Value = "abcd";command.ExecuteNonQuery(); 

SQL SERVER產生兩個查詢計劃，

(@str nvarchar(4))Select * From t1 Where col1 = @str

(@str nvarchar(3))Select * From t1 Where col1 = @str，

因此使用預存程序是重用查詢計劃的最好方案，因為 SQL SERVER會將整個預存程序產生的查詢計劃緩衝，無論傳入參數是什麼，每次都會重用預存程序的查詢計劃。

【轉】 參數化查詢