用過 Dapper 的朋友應該對它是愛不釋手，最近在一個對效能敏感的系統上 tune SQL 查詢語句時，發現到 SQL 參數型別的不同及使不使用 SQL 參數，對執行計劃的選擇影響甚大，相同的查詢條件及結果，只因改了參數的型別，執行計劃就跟著改變，查詢成本也跟著拉高。
我們將查詢語句及參數丟給 Dapper 之後，事實上它背後是用 sp_executesql 這個 Stored Procedure 來執行查詢語句，那麼我們丟給 Dapper 的參數在送給 sp_executesql 之前，Dapper 勢必需要替這些參數選擇一個 SQL Data Type，那 Dapper 怎麼知道要選哪一個？ 答案是「猜」，從 C# 的資料型別去猜，好猜的大都能猜中，像是 int、long、bool、...等，難猜的像是 string、double、...等，難猜的 Dapper 索性就不猜了，會給它一個固定的 SQL Data Type，像是 string 就會是 nvarchar(4000)，如果想要知道 Dapper 最終會替參數選擇哪一個 SQL Data Type，可以用 XEvent 分析工具或 SQL Server Profiler（可能在 Microsoft SQL Server 的未來版本中移除）來查看 Dapper 產生的 SQL 語法。
參數型別影響執行計劃
但是使用固定的 SQL Data Type 卻會影響執行計劃及查詢成本，底下是一個真實案例，在某一個資料表中，依時間升冪排序，要抓出 Id 為 "2330" 在 2019-08-07 第一筆資料的時間值，可以看到依照 Dapper 產生的語法所得到的執行計劃及估計的子樹成本。
那麼我將 @Id nvarchar(4000) 改成 @Id varchar(20) 與條件欄位的型別相同，可以看到不只執行計劃的運算子節點變少了，估計的子樹成本也降低了 60%。
怎麼改善這個狀況？
Dapper 提供了一個 DbString 的類別來幫助我們改善這個情況，將字串參數改用 DbString 來包裝就可以讓我們指定 SQL Data Type，使用方式如下：
這樣 @Id 參數的 SQL Data Type 就會是 varchar(20)，DbString 藉由 IsAnsi、IsFixedLength 這兩個屬性可以組合出 varchar、nvarchar、char、nchar 四種 SQL Data Type，組合方式請參考下表說明：
IsAnsi | |||
| false | true | ||
IsFixedLength | false | nvarchar | varchar |
| true | nchar | char | |
Length 屬性預設值是 4000，-1 代表 Max。 | |||
為了讓 DbString 更方便使用，我們可以撰寫 string 的擴充方法。
public static class DapperExtension
{
/// <summary>
/// Length of the string is default 4000
/// </summary>
public static DbString ToVarchar(this string me)
{
return new DbString { Value = me, IsAnsi = true, IsFixedLength = false };
}
/// <summary>
/// Length of the string -1 for max
/// </summary>
public static DbString ToVarchar(this string me, int length)
{
return new DbString { Value = me, Length = length, IsAnsi = true, IsFixedLength = false };
}
/// <summary>
/// Length of the string is default 4000
/// </summary>
public static DbString ToChar(this string me)
{
return new DbString { Value = me, IsAnsi = true, IsFixedLength = true };
}
/// <summary>
/// Length of the string -1 for max
/// </summary>
public static DbString ToChar(this string me, int length)
{
return new DbString { Value = me, Length = length, IsAnsi = true, IsFixedLength = true };
}
/// <summary>
/// Length of the string is default 4000
/// </summary>
public static DbString ToNVarchar(this string me)
{
return new DbString { Value = me, IsAnsi = false, IsFixedLength = false };
}
/// <summary>
/// Length of the string -1 for max
/// </summary>
public static DbString ToNVarchar(this string me, int length)
{
return new DbString { Value = me, Length = length, IsAnsi = false, IsFixedLength = false };
}
/// <summary>
/// Length of the string is default 4000
/// </summary>
public static DbString ToNChar(this string me)
{
return new DbString { Value = me, IsAnsi = false, IsFixedLength = true };
}
/// <summary>
/// Length of the string -1 for max
/// </summary>
public static DbString ToNChar(this string me, int length)
{
return new DbString { Value = me, Length = length, IsAnsi = false, IsFixedLength = true };
}
}
Literal replacements
這算是同場加映，屬於相同的議題，沒想到參數化也會影響執行計劃，底下是一個真實案例，要在某一個資料表中，撈出 Id 為 313 的前 100 筆資料，有針對 Id 欄位建非叢集索引，得到的執行計劃如下，看起來有打到索引，似乎是我們要的。
但是我們若將 @Id 直接置換成一個常數值，執行計劃就變了，估計的子樹成本也跟著降低了。
使用參數化反而得到更糟的執行計劃及查詢成本，為此 Dapper 提供了 Literal replacements，直接將參數的值放置在查詢語法中，以利取得較好的執行計劃及過濾掉不合適的索引，特別是在參數值都是固定那幾個的時候，但是僅支援數值型別及布林型別，使用方法如下：
以上針對使用 Dapper 時，我們可以怎麼樣來調整，以避免得到較差的執行計劃做一個記錄，希望對各位朋友有幫助。
