Dapper 之字串參數查詢效能地雷

2024-01-17 10:16 PM

同事踩到 Dapper 的雷，我跟著上了一課，筆記備忘。

嚴格來說，NVarChar 參數查 VarChar 欄位效能不佳算是老掉牙問題，我過去沒紥紮實實踩到所以印象不深，但體驗過一次效能差異，以後應該都會記得要確認。

用一個 2,000 萬筆資料的 Oracle 資料表重現問題。

用 Dapper 執行 UPDATE EXAMPLE SET Score = Score + 1 WHERE Id = :id，id 參數分別用匿名型別 new { id } 及 var p = new DynamicParameters(); p.Add("id", id); 傳入，總共測 5 次，兩種寫法的執行速度天差地別，相差 100 倍：
(註：有使用 FixOdpNetDbTypeStringMapping 修正 Unicode 字元寫入問題)

static void Main(string[] args)
{
    FixOdpNetDbTypeStringMapping();
    for (var i = 0; i < 5; i++)
    {
        RunTest();
    }
    Console.ReadLine();
}

static void RunTest()
{
    var sql = "UPDATE EXAMPLE SET Score = Score + 1 WHERE Id = :id";
    var rnd = new Random();
    var rowCount = 20000000;
    using (var cn = new OracleConnection(cs))
    {

        var id = $"{rnd.Next(rowCount):x8}";
        var anonymousType = new { id };
        var sw = new Stopwatch();
        sw.Start();
        var affected = cn.Execute(sql, anonymousType);
        sw.Stop();
        Console.WriteLine($"{id} - {affected} row updated. ({sw.ElapsedMilliseconds} ms)");
        id = $"{rnd.Next(rowCount):x8}";
        var p = new DynamicParameters();
        p.Add("id", id);
        sw.Restart();
        affected = cn.Execute(sql, p);
        sw.Stop();
        Console.WriteLine($"{id} - {affected} row updated. ({sw.ElapsedMilliseconds} ms)");
    }
}

使用昨天說的 SimulateDapperDbParameters() 檢查二者產生的參數型別，發現關鍵差異：

使用匿名型別傳參數時，對映出來的 OracleParameter 型別是 NVarChar2，而 DynamicParameters 則對映到 VarChar2。

這裡藏了一個陷阱，當欄位型別是 VarChar，若用 NVarChar 型別參數進行比對，將因型別不同無法使用索引(從 Index Seek 變成 Index Scan，細節可參考軟體主廚的實測或這篇 Troubleshooting SQL index performance on varchar columns)，當資料筆數很多，有沒有索引便會形成明顯差異。資料筆數愈多，差距愈大。

知道原因就不難解決。針對 WHERE 條件式中參數須使用 VarChar 型別確保查詢效能的情境，Dapper 提供的解法是透過 DbString 類別 IsAnsi 屬性指定將 string 對映成 VarChar (SQL) / VarChar2 (Oracle) 型別。在本案例改成 new { id = new DbString { Value = id, IsAnsi = true, Length = id.Length } } 即可確保 Dapper 用 VarChar2 傳送 id 參數。依我的個性，肯定受不了程式碼冒出一大堆又臭又長的 new DbString { Value = ..., IsAnsi = true... }，我選擇寫個擴充方法簡化成 new { id = id.ToConstVarChar() }：

internal class Program
{
    static void Main(string[] args)
    {
        FixOdpNetDbTypeStringMapping();
        for (var i = 0; i < 5; i++)
        {
            RunTest(i == 4);
        }
        Console.ReadLine();
    }

    static void RunTest(bool printParamTypes)
    {
        var sql = "UPDATE EXAMPLE SET Score = Score + 1 WHERE Id = :id";
        var rnd = new Random();
        var rowCount = 20000000;
        using (var cn = new OracleConnection(cs))
        {
            var id = $"{rnd.Next(rowCount):x8}";
            var anonymousType = new { id = id.ToConstVarChar() };
            var sw = new Stopwatch();
            sw.Start();
            var affected = cn.Execute(sql, anonymousType);
            sw.Stop();
            Console.WriteLine($"{id} - {affected} row updated. ({sw.ElapsedMilliseconds} ms)");
            if (printParamTypes) SimulateDapperDbParameters(sql, cn, anonymousType);
            id = $"{rnd.Next(rowCount):x8}";
            var p = new DynamicParameters();
            p.Add("id", id);
            sw.Restart();
            affected = cn.Execute(sql, p);
            sw.Stop();
            Console.WriteLine($"{id} - {affected} row updated. ({sw.ElapsedMilliseconds} ms)");
            if (printParamTypes) SimulateDapperDbParameters(sql, cn, p);
        }
    }
}

public static class ExtMethods
{
    public static DbString ToConstVarChar(this string str) => 
        new DbString { Value = str, IsAnsi = true, Length = str.Length };
}

改用 DbString 後，參數型別變成 VarChar2，執行速度提升到跟 DynamicParameter 一樣快(註：第一次冷啟動耗時較久不計)，問題排除。