善用暫存表優化巢狀 SQL 查詢效能：實戰案例分析

巢狀子查詢 (如 IN (SELECT ...)) 在大資料量下常導致效能災難。本文透過實戰案例展示暫存表如何將執行時間從分鐘級降至秒級。

問題查詢

SELECT KeyId, COUNT(1) AS Num
FROM Table1
WHERE CreateDate >= '2007-09-21'
  AND KeyId IN (SELECT KeyId FROM Table2 WHERE Id = 1611)
GROUP BY KeyId;

症狀：Table1 每列掃描都執行一次子查詢，隨 Table1 增長呈線性惡化。

嘗試 1：改寫為 JOIN (效果有限)

SELECT a.KeyId, COUNT(1) AS Num
FROM Table1 a
JOIN (SELECT KeyId FROM Table2 WHERE Id = 1611) b ON a.KeyId = b.KeyId
WHERE a.CreateDate >= '2007-09-21'
GROUP BY a.KeyId;

優化器有時仍選擇巢狀迴圈，且衍生表無統計資訊，效能不穩定。

解決方案：暫存表具體化中間結果

' 1. 將子查詢結果具體化至暫存表 (僅掃描 Table2 一次)
SELECT KeyId INTO #TmpKeys FROM Table2 WHERE Id = 1611;

' 2. 主查詢 JOIN 暫存表 (有統計資訊、可建立索引)
SELECT a.KeyId, COUNT(1) AS Num
FROM Table1 a
JOIN #TmpKeys b ON a.KeyId = b.KeyId
WHERE a.CreateDate >= '2007-09-21'
GROUP BY a.KeyId;

' 3. 清理
DROP TABLE #TmpKeys;

為何暫存表有效？

進階優化技巧

' 明確建立索引 (大量資料時顯著提升)
CREATE CLUSTERED INDEX IX_TmpKeys_KeyId ON #TmpKeys(KeyId);

' 使用 TABLE 變數替代 (小資料量、避免重編譯)
DECLARE @TmpKeys TABLE (KeyId INT PRIMARY KEY);
INSERT @TmpKeys SELECT KeyId FROM Table2 WHERE Id = 1611;
-- 但 TABLE 變數無統計資訊，優化器假設 1 列，大資料量慎用

適用場景判斷

• ✅ 子查詢結果集較小 (萬列內)、主表極大
• ✅ 子查詢條件固定、可預先計算
• ✅ 原查詢出現 Table Spool、Lazy Spool、估算列數嚴重偏離
• ❌ 子查詢包含外部參數關聯 (相關子查詢) → 改用 APPLY / JOIN
• ❌ 極高並發、極短壽命查詢 → TABLE 變數或 CTE 可能更輕量

效能對比 (實測環境)

暫存表不是銀彈，但在「大表驅動小表、中間結果可複用」場景下，是改寫複雜查詢的利器。