翼前緣渦流

先說一點結論，有 PK 的單欄 ID 查詢基本還是最快的，但這次踩到型別的雷

Table 和資料建置

CREATE TABLE [dbo].[TEST_ID](
	[ID] char(8) NOT NULL,
    [ID_STR] nvarchar(8) NOT NULL,
    [ID_ASCII] char(8) NOT NULL
    PRIMARY KEY ([ID] ASC)
) 
GO
CREATE NONCLUSTERED INDEX [NIDX_TEST_ID_ID_STR] ON [dbo].[TEST_ID]
(
	[ID_STR] ASC
)
GO
CREATE NONCLUSTERED INDEX [NIDX_TEST_ID_ASCII] ON [dbo].[TEST_ID]
(
	[ID_ASCII] ASC
)
GO

void FeedTable()
{
    var cols = new[] { "ID", "ID_STR", "ID_ASCII" };
    var dt = new DataTable();
    foreach (var colName in cols)
    {
        dt.Columns.Add(colName, typeof(string));

    }

    // 大約 10 秒，資料 44 MB 索引 55 MB
    for (int i = 0; i < 100_0000; i++)
    {
        var id = $"{i:00000000}";
        var row = dt.NewRow();
        foreach (var colName in cols)
        {
            row[colName] = id;
        }
        dt.Rows.Add(row);
    }

    using (var conn = new SqlConnection(_connString))
    {
        conn.Open();
        var blk = new SqlBulkCopy(conn);
        blk.BulkCopyTimeout = 1000;
        blk.DestinationTableName = "TEST_ID";
        var mappings = blk.ColumnMappings;
        for (int i = 0; i < cols.Length; i++)
        {
            mappings.Add(new SqlBulkCopyColumnMapping(i, cols[i]));
        }
        blk.BatchSize = 1000;
        blk.WriteToServer(dt);
        blk.Close();
        conn.Close();
    }
}

ADO 查詢範例-重現問題

Main()
{
    var rdn = new Random();
    var ids = Enumerable.Range(1, 100).Select(i => $"{rdn.Next(1,99999999):00000000}").ToList();

    // Methond JIT init
    QueryTest("SELECT GETDATE()", new[] {""}.ToList());
    
    // QUERY TEST NONRAL
    Console.WriteLine("開始測試 ID 欄位查詢");
    var sw = Stopwatch.StartNew();
    QueryTest("SELECT * FROM [TEST_ID] WHERE ID = @id", ids);
    sw.Stop();
    Console.WriteLine($"查詢 ID 費時 {sw.ElapsedMilliseconds} ms");

    // 
    Console.WriteLine("開始測試 ID_STR 欄位查詢");
    sw.Restart();
    QueryTest("SELECT * FROM [TEST_ID] WHERE ID_STR = @id", ids);
    sw.Stop();
    Console.WriteLine($"查詢 ID_STR 費時 {sw.ElapsedMilliseconds} ms");

    // 
    Console.WriteLine("開始測試 ID_ASCII 欄位查詢");
    sw.Restart();
    QueryTest("SELECT * FROM [TEST_ID] WHERE ID_ASCII = @id", ids);
    sw.Stop();
    Console.WriteLine($"查詢 ID_ASCII 費時 {sw.ElapsedMilliseconds} ms");
}

void QueryTest(string sql, List<string>ids)
{
    foreach (var id in ids)
    {
        using (var conn = new SqlConnection(_connString))
        using (var cmd = new SqlCommand(sql, conn))
        {
           cmd.Parameters.AddWithValue("@id", id);
           var da = new SqlDataAdapter(cmd);
           var dt = new DataTable();
           da.Fill(dt);
           da.Dispose();
        }
    }
}

同樣 100 筆查詢計時

查詢主索引欄位 ID 耗時 7xxx ms，查詢非叢集索引欄位 ID_STR 耗時 20ms

照經驗理來說 ID 是 PK 查起來應該是超快的，但卻比 ID_STR 慢 30 倍不止

SQL 查詢效能測試，搭配快取清除語法

查看 SQL 效能查詢語法

SELECT TOP(100) qs.execution_count AS [Execution Count],
(qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time (ms)],
qs.creation_time AS [Creation Time]
,t.text AS [Complete Query Text]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE t.dbid = DB_ID() 
ORDER BY [Execution Count] DESC

這次用 ID 當條件查詢很容易被 SQL Server 快取，導致無法重現效能問題，需要清除快取

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

我從上面的 SQL 效能查詢語法，發現 string 型別總是被轉成 nvarchar，想辦法將 nvarchar 改成 varchar 後，ID 和 ID_STR 的查詢時間就非常相近了

結論

ADO 用 AddWithValue() 方法加入 parameter 時 string 都會被自動轉成 nvarchar，從速度上來看 SQL 在比對 char 欄位時會在每次比對都把 nvarchar 轉成 char，從而增加運算成本

當遇到查詢 char、nchar 欄位時，可以改用 Add() 方法來明確指定型別如

//cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar));

另外也有狀況是 ADO 查詢已經被包裝過了，底層已是 AddWithValue() 不好修改，這時也可以調整 SQL 語法用 CAST 或 CONVERT 來優化效能如

--SELECT * FROM [TEST_ID] WHERE ID = @id
SELECT * FROM [TEST_ID] WHERE ID = CAST(@id AS char)--CAST 在查詢過程總共只做了一次轉型

像這次問題就是調整 SQL 語法來優化效能

評估用 AddWIthValue 查 char 欄位還是很快的，平常單筆查詢根本無感，又能讓程式碼簡短

只有在同次作業裡送出足夠多筆查詢才會開始有感，遇到需要再優化就好