接獲客戶端IT通知，某個交易查詢約需6秒，調查資料存取物件是簡單的ADO.NET Sqlcommand，簡單的語法加上參數化查詢AddWithValue，
疑，AddWithValue
後續客戶調整後只要0.05秒，再次碰上幾年前埋頭改legacy code提升效能的噩夢:
AddWithValue 參數化查詢時，在DbType的選擇是採.NET資料類型來決定，若查詢參數型別與實際欄位不同時有時可能會造成額外的成本。
哈! 這一次我們好好筆記下來以後列入程式碼交付盤查。
首先建立一個10萬筆，主鍵是VARCHAR(8)的資料表
CREATE TABLE TableSqlDbType
(C1 VARCHAR(8) NOT NULL,C2 CHAR(5) NOT NULL)
--建立主鍵
ALTER TABLE TableSqlDbType
ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED(C1)
--建立資料
DECLARE @I INT = 0;
WHILE @I < 100000
BEGIN
SET @I+=1;
INSERT INTO TableSqlDbType
VALUES (@I, @I);
END
先給正確型別(VARCHAR)查詢
DECLARE @ID VARCHAR(8) = '10000';
DECLARE @START DATETIME = GETDATE();
SELECT * FROM TableSqlDbType
WHERE C1 = @ID
PRINT DATEDIFF(MS,@START,GETDATE());
正確查詢型別(VARCHAR)結果: 費時3毫秒，驚!這麼快..
接下來用不太正確的型別(NVARCHAR)查詢
DECLARE @ID NVARCHAR(8) = '10000';
DECLARE @START DATETIME = GETDATE();
SELECT * FROM TableSqlDbType
WHERE C1 = @ID
PRINT DATEDIFF(MS,@START,GETDATE());
不太正確型別(NVARCHAR)查詢結果:費時420毫秒，驚! 多了百倍。
- 1.由於NVARCHAR的資料優先順序25較VARCHAR(27)優先，因此在述詞多做了無謂的隱含轉換(implicit conversion)。
- 2.即使轉換了，搜尋述詞卻用區間方式的比較＜＞
多了幾個執行計畫步驟，SELECT還出現了警告，但..叢集索引搜尋中CPU成本怎麼看不出來（舉手發問）。
好，下一步來看一下AddWithValue對於幾種常用資料型別的預設，先寫一支小程式
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CN"].ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("", conn))
{
cmd.Parameters.AddWithValue("string", string.Format("Aerosmith") );
cmd.Parameters.AddWithValue("decimal", new decimal(100) );
cmd.Parameters.AddWithValue("int", 0);
cmd.Parameters.AddWithValue("Datetime", new DateTime(1900,1,1) );
Console.WriteLine("C# string SqlDbType is {0}", cmd.Parameters["string"].SqlDbType);
Console.WriteLine("C# decimal SqlDbType is {0}", cmd.Parameters["decimal"].SqlDbType);
Console.WriteLine("C# int SqlDbType is {0}", cmd.Parameters["int"].SqlDbType);
Console.WriteLine("C# DatetimeSqlDbType is {0}", cmd.Parameters["Datetime"].SqlDbType);
}
}
字串預設果然是NVARCHAR，解決的辦法可以將sqlcmd參數化addwithvalue改寫成add：
cmd.Parameters.Add("string", SqlDbType.VarChar);
cmd.Parameters["string"].Value = "Aerosmith";
如果欄位是NVARCHAR，但查詢條件是VARCHAR?
驚! 雖然型別不同，卻沒有出現警告，仔細觀察搜尋字詞:
搜尋索引鍵[1]: 前置詞: [TSQL2012].[dbo].[TableSqlDbType2].C1 = 純量運算子(CONVERT_IMPLICIT(nvarchar(8),[@ID],0))
由於資料類型優先順序的因素，預設會將較低的輸入變數:VARCHAR(27)隱含轉換為NVARCHAR(25)，再來和欄位比較。
小結：如果可以，就將sqlparameter盡量指定好型別再和欄位比較，這樣就不用傷腦筋 Database Engine在背後多做轉換。
參考：