SQL Server 參數化查詢緩慢？兩招解決類型不匹配導致的執行計劃失效

SQL Server 參數化查詢緩慢？兩招解決類型不匹配導致的執行計劃失效

遇到同樣 SQL，參數化版本（@OrderDetailID nvarchar(50)）極慢，直接寫死字面值（'R1502003963580C001'）卻秒回？根因通常是**參數類型與欄位定義不一致**，導致隱含轉換、索引失效、執行計劃無法複用。

現象重現

-- 慢：參數化
declare @OrderDetailID nvarchar(50)
set @OrderDetailID='R1502003963580C001'
select * from BillDetail where OrderDetailID=@OrderDetailID

-- 快：字面值
select * from BillDetail where OrderDetailID='R1502003963580C001'

核心原因

• 資料表欄位 OrderDetailID 為 varchar，但參數宣告為 nvarchar。
• SQL Server 需將欄位值隱含轉換為 nvarchar 再比較，**導致索引掃描變為索引掃描/表掃描**，效能暴跌。
• 且不同長度參數（如 nvarchar(50) vs nvarchar(40)）會產生多個執行計劃，污染 Plan Cache。

解決方案

方案一：修正參數類型與長度（推薦）

declare @OrderDetailID varchar(20)  -- 與欄位定義完全一致
set @OrderDetailID='R1502003963580C001'
select * from BillDetail where OrderDetailID=@OrderDetailID

C# 端同步修正：

new SqlParameter("@name", SqlDbType.VarChar, 20) { Value = name };

方案二：動態 SQL（EXEC）強制參數化為字面值

declare @OrderDetailID varchar(20)
set @OrderDetailID='R1502003963580C001'
exec('select * from BillDetail where OrderDetailID=''' + @OrderDetailID + '''')

此法將參數「內插」為字面值，繞過參數嗅探與類型轉換，**但需防 SQL 注入**，僅建議內部可控場景使用。

最佳實踐總結

1. **參數類型、長度務必與資料表欄位定義完全一致**。
2. 使用 SqlParameter 時**明確指定 SqlDbType 與 Size**。
3. 優先用預存程序或正確參數化，避免動態 SQL 風險。
4. 若無法修正類型，可嘗試 OPTION (RECOMPILE) 或 Plan Guide 干預。