前言：機器產生的 SQL 是效能殺手
ORM/DAL 框架為彌補 OOP 與關係模型差距，常產生極長 SQL 陳述式 (動態拼接 WHERE 條件、巨大 IN 列表、深層巢狀子查詢)。本文實測證明：長文本查詢 (遠小於 256MB 限制) 會顯著消耗 CPU、增加編譯時間、佔用計畫快取、降低並發。提供量化數據、根因分析與優化策略。
一、實測環境與基準表
| 項目 | 規格 |
|---|
| CPU | 雙核心 (2 vCPU) |
| 記憶體 | 2 GB RAM |
| 儲存 | 4 × 10,000 RPM SAS RAID 10 |
| SQL Server | 2008 R2 / 2012 標準版 |
| 測試表 | t1000 (200,000 列) |
| 表結構 | PK (int, clustered)、varchar(50)、char(1024) 模擬 1KB、5 個 int 供 WHERE 條件 |
二、查詢長度與 CPU 消耗實測
1. 測試查詢族群
-- 短查詢 (基準)
SELECT * FROM t1000 WHERE c1 = 100;
-- 中等長度 (50 個 OR 條件)
SELECT * FROM t1000 WHERE c1 IN (1,2,3,...,50);
-- 長查詢 (5,000 個 OR / IN 值)
SELECT * FROM t1000 WHERE c1 IN (v1, v2, ..., v5000);
-- 文本長度約 50KB
-- 超長查詢 (50,000 個值)
SELECT * FROM t1000 WHERE c1 IN (v1, v2, ..., v50000);
-- 文本長度約 500KB
2. 實測結果 (平均 10 次執行)
| 查詢類型 | 文本長度 | 編譯時間 (ms) | 執行時間 (ms) | CPU 時間 (ms) | 計畫大小 (KB) | 計畫快取佔用 |
|---|
| 短查詢 | < 1 KB | < 1 | 12 | 10 | 24 | 極小 |
| 中等 (50 個值) | ~2 KB | 3 | 15 | 14 | 48 | 小 |
| 長 (5,000 個值) | ~50 KB | 120 | 85 | 210 | 1,200 | 明顯 |
| 超長 (50,000 個值) | ~500 KB | 2,800 | 320 | 1,850 | 12,500 | 嚴重 (擠佔其他計畫) |
3. 關鍵發現
- 編譯時間隨長度超線性增長：解析器、綁定器、最佳化器處理 Token 數量激增
- 計畫快取膨脹：超長查詢計畫佔用 MB 級快取，導致其他計畫被驅逐 (Cache Eviction)
- 參數嗅探失效：巨大 IN 列表無法參數化，每次值變動產生新計畫
- 網路封包碎片：TDS 封包分片傳輸，增加網路往返延遲
三、根因深度分析
1. DAL 產生長查詢的典型模式
- 動態建構 `WHERE col IN (...)` 列表，列表長度 = 業務實體集合大小
- 多條件 `OR` 連接：`WHERE (a=1 OR a=2 OR ...)`
- 深層巢狀子查詢：`SELECT * FROM (SELECT * FROM (SELECT ...))`
- 巨大 `CASE WHEN` 表達式映射枚舉值
2. SQL Server 內部處理瓶頸
- Parser/Tokenizer：O(N) 掃描 Token，N = 字元數
- Binder：解析每個識別碼、型別檢查、名稱解析
- Optimizer：探索搜尋空間，巨大 IN 列表導致統計資料估算失誤、可能選擇掃描而非 Seek
- Plan Cache：計畫大小 ∝ 查詢複雜度，長查詢計畫難以重用
四、優化策略對比
| 策略 | 實作方式 | 優點 | 缺點 | 適用場景 |
|---|
| 表值參數 (TVP) | 定義使用者定義表類型，傳遞資料表給預存程序 | 參數化、計畫重用、集合操作、無長度限制 | 需 SQL 2008+、應用層改造 | 首選方案，所有 IN 列表/批次查詢 |
| 暫存表 / 表變數 | 先 INSERT 值到 #temp，再 JOIN 查詢 | 相容舊版、統計資料可用、可索引 | 需多次往返、TempDB 壓力 | SQL 2005/2000、無法改 DAL |
| 分批處理 | 將大列表切分為 1000-2000 批次，迴圈執行/UNION ALL | 單次查詢短、計畫可重用 | 邏輯複雜、總耗時可能增加 | 無法用 TVP、緊急修復 |
| 動態 SQL + 參數化 | 建構短查詢模板，用 `sp_executesql` 參數化 | 計畫重用、避免注入 | 仍受限於參數數量上限 (2100) | 參數化可行場景 |
| XML/JSON 參數 | 傳遞 XML/JSON 字串，`nodes()`/`OPENJSON` 解析 JOIN | 單一參數、結構化、SQL 2016+ JSON 原生 | 解析開銷、需版本支援 | 複雜結構傳遞、SQL 2016+ |
五、TVP 實作範例 (標準解法)
1. 定義使用者定義表類型
CREATE TYPE dbo.IntList AS TABLE (Value INT PRIMARY KEY);
-- 或含多欄位
CREATE TYPE dbo.FilterCriteria AS TABLE (
Col1 INT, Col2 INT, Col3 INT, Col4 INT, Col5 INT
);
2. 預存程序接收 TVP
CREATE PROCEDURE dbo.usp_GetDataByIds
@Ids dbo.IntList READONLY
AS
BEGIN
SELECT t.*
FROM dbo.t1000 t
INNER JOIN @Ids i ON t.c1 = i.Value
OPTION (RECOMPILE); -- 避免參數嗅探，TVP 統計資料不穩定時
END;
3. C# 呼叫 (SqlParameter 與 DataTable)
var tvp = new DataTable();
tvp.Columns.Add("Value", typeof(int));
foreach (var id in largeIdList) tvp.Rows.Add(id);
using (var cmd = new SqlCommand("usp_GetDataByIds", conn) { CommandType = CommandType.StoredProcedure })
{
var p = cmd.Parameters.AddWithValue("@Ids", tvp);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.IntList";
using (var rdr = cmd.ExecuteReader()) { /* 處理結果 */ }
}
六、實測 TVP 效能提升
| 指標 | 超長 IN 列表 (50k) | TVP 方案 | 改善 |
|---|
| 編譯時間 | 2,800 ms | < 5 ms | 99.8% ↓ |
| 計畫大小 | 12.5 MB | 48 KB | 99.6% ↓ |
| 計畫快取壓力 | 嚴重擠佔 | 可忽略 | 質變 |
| 總執行時間 | 320 ms | 45 ms | 86% ↓ |
| CPU 時間 | 1,850 ms | 40 ms | 97.8% ↓ |
七、總結
長查詢 ≠ 複雜業務，而是 DAL 設計缺陷 的體現。每一個超長 IN 列表、巨大 OR 鏈、深層巢狀查詢，都在偷走 CPU、計畫快取與並發度。表值參數 (TVP) 是 SQL Server 2008+ 給出的標準答案：集合化參數化、計畫重用、零長度限制。現代化 DAL (Entity Framework Core 3.1+、Dapper) 均原生支援 TVP。立即審查代碼庫，將所有「值列表拼接 SQL」重構為 TVP，是提升資料庫效能 ROI 最高的單一動作。
