SQL 參數化查詢性能陷阱：字符串參數導致執行計劃回退與隱式轉換深度解析

同樣邏輯的 SQL，將常量改為變量後執行時間從 1 秒飆升至 7 秒。這不是玄學，而是參數嗅探與基數估算機制在特定條件下的典型副作用。本文剖析根因，提供多種規避方案。

現場還原：兩條 SQL 的唯一差異

常量版（1 秒）

SELECT ACC_SEGMENT
FROM dbo.mis_gl_balance
WHERE LEFT(ACC_SEGMENT, 22) IN (
    SELECT '3810.105301.' + mis_code
    FROM finance_budget_account
    WHERE budget_type = '電路租費'
);

變量版（7 秒）

DECLARE @mis_company_code CHAR(4) = '3810';
SELECT ACC_SEGMENT
FROM dbo.mis_gl_balance
WHERE LEFT(ACC_SEGMENT, 22) IN (
    SELECT @mis_company_code + '.105301.' + mis_code
    FROM finance_budget_account
    WHERE budget_type = '電路租費'
);

核心根因：優化器「看不見」變量值

常量編譯時期可見

• 解析/編譯階段，優化器**知道** `'3810.105301.'` 前綴；
• 結合統計信息，精確估算 `LEFT(ACC_SEGMENT,22)` 過濾後的行數；
• 選擇**高效計劃**：Index Seek + Nested Loops / Hash Match（小結果集驅動大表）。

變量編譯時期不可見

• 編譯時 `@mis_company_code` 尚未賦值（或優化器不嗅探局部變量）；
• 優化器**無法估算**拼接後字符串的選擇性，退回**密度向量/平均分佈假設**；
• 誤判結果集巨大，選擇**全表掃描 / Hash Match（大表構建哈希）** 導致 7 秒。

深度機制：隱式轉換與 SARGable 破壞

`LEFT(ACC_SEGMENT, 22)` 導致列上函數操作，**索引不可用**（Non-SARGable）。但常量版通過子查詢常量折疊，優化器將 `IN` 列表轉為常量集合，配合統計信息仍能選擇較優計劃。變量版喪失常量折疊能力，雙重打擊：列函數 + 基數誤估。

五種規避方案（按推薦度排序）

方案 1：改寫為 JOIN + 覆蓋索引（最佳）

-- 創建覆蓋索引
CREATE INDEX IX_mis_gl_balance_ACC_SEGMENT_22 
ON dbo.mis_gl_balance (LEFT(ACC_SEGMENT, 22)) INCLUDE (ACC_SEGMENT);

-- 改寫查詢
SELECT b.ACC_SEGMENT
FROM dbo.mis_gl_balance b
JOIN finance_budget_account a 
  ON LEFT(b.ACC_SEGMENT, 22) = '3810.105301.' + a.mis_code
WHERE a.budget_type = '電路租費';

優勢：消除 `IN` 子查詢、利用索引、常量參與 JOIN 條件優化器可見。

方案 2：使用 `OPTION (RECOMPILE)` 強制重編譯

DECLARE @mis_company_code CHAR(4) = '3810';
SELECT ACC_SEGMENT
FROM dbo.mis_gl_balance
WHERE LEFT(ACC_SEGMENT, 22) IN (
    SELECT @mis_company_code + '.105301.' + mis_code
    FROM finance_budget_account
    WHERE budget_type = '電路租費'
) OPTION (RECOMPILE);

優勢：運行時嗅探變量值，生成最優計劃。代價：每次編譯 CPU 開銷，高頻調用慎用。

方案 3：臨時表/表變量物化中間結果

DECLARE @mis_company_code CHAR(4) = '3810';
DECLARE @codes TABLE (code CHAR(22) PRIMARY KEY);
INSERT @codes 
SELECT @mis_company_code + '.105301.' + mis_code
FROM finance_budget_account
WHERE budget_type = '電路租費';

SELECT b.ACC_SEGMENT
FROM dbo.mis_gl_balance b
JOIN @codes c ON LEFT(b.ACC_SEGMENT, 22) = c.code;

優勢：物化後統計信息準確，計劃穩定。適用：中間結果集 < 10 萬行。

方案 4：動態 SQL（謹慎使用）

DECLARE @sql NVARCHAR(MAX) = N'
SELECT ACC_SEGMENT
FROM dbo.mis_gl_balance
WHERE LEFT(ACC_SEGMENT, 22) IN (
    SELECT ''' + @mis_company_code + '''.105301.' + mis_code + '
    FROM finance_budget_account
    WHERE budget_type = N''電路租費''
)';
EXEC sp_executesql @sql;

風險：SQL 注入、計劃緩存污染、權限提升。僅作為最後手段。

方案 5：計算列 + 索引（結構優化）

ALTER TABLE dbo.mis_gl_balance 
ADD ACC_SEGMENT_22 AS LEFT(ACC_SEGMENT, 22) PERSISTED;
CREATE INDEX IX_ACC_SEGMENT_22 ON dbo.mis_gl_balance (ACC_SEGMENT_22);

優勢：將函數計算移至寫入時，查詢直接 SARGable，彻底解決列函數問題。

性能對比驗證

總結

「常量快、變量慢」本質是優化器信息不對稱。解決路徑核心只有兩條：讓優化器看見值（RECOMPILE/動態 SQL/常量內聯）或讓優化器不依賴值也能選對計劃（JOIN 改寫/計算列/索引覆蓋/統計信息優化）。生產環境首選**結構優化（索引/計算列）+ 寫法優化（JOIN）**，輔以 `RECOMPILE` 戰術補丁，構建穩健性能基線。