五大提升 SQL 效能實戰技巧 (上)：IDENTITY 取回、內嵌檢視表與避免 LEFT JOIN

應用程式效能瓶頸往往源於資料庫設計與查詢撰寫。本文分享四項經實證有效的 SQL Server 效能優化技巧，無銀彈解方，需配合測試與分析。

1. 從 INSERT 取回 IDENTITY 值

常見需求：執行 INSERT 後立即取得自動產生的識別欄位值。SQL Server 提供 @@IDENTITY 全域變數，但必須在**同一連線**緊接著 INSERT 之後執行：

INSERT INTO Products (ProductName) VALUES ('Chalk');
SELECT @@IDENTITY;

在 ADO 中建議使用 SET NOCOUNT ON 避免多重記錄集干擾：

Set oRs = oCn.Execute("SET NOCOUNT ON; INSERT INTO Products (ProductName) VALUES ('Chalk'); SELECT @@IDENTITY")
lProductID = oRs(0)

替代方案：建立 FOR INSERT 觸發器自動回傳 IDENTITY，讓應用程式呼叫方式一致：

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
SELECT @@IDENTITY
GO

2. 內嵌檢視表取代暫存表

需將查詢結果暫存以供後續聯結時，常用暫存表 (#Temp)，但會造成大量 I/O。改用內嵌檢視表 (Inline View / Derived Table) 直接在 FROM 子句中嵌入子查詢：

-- 暫存表寫法 (較耗資源)
CREATE TABLE #Temp1 (OrderID INT, OrderDate DATETIME);
INSERT INTO #Temp1 SELECT TOP 5 OrderID, OrderDate FROM Orders ORDER BY OrderDate DESC;
SELECT p.ProductName, SUM(od.Quantity) FROM #Temp1 t
JOIN [Order Details] od ON t.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName;
DROP TABLE #Temp1;

-- 內嵌檢視表寫法 (效能更佳、語法更簡潔)
SELECT p.ProductName, SUM(od.Quantity)
FROM (SELECT TOP 5 OrderID, OrderDate FROM Orders ORDER BY OrderDate DESC) t
JOIN [Order Details] od ON t.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName;

3. 避免 LEFT JOIN 與 NULL 值

LEFT JOIN 需比對 NULL 值，資源消耗遠大於 INNER JOIN。優化策略：

• 重寫查詢改用 INNER JOIN。
• 資料庫設計階段避免可為 NULL 的外鍵：例如建立「無分類」預設分類，使外鍵不可為 NULL，即可全程使用 INNER JOIN。
• 若必須用 LEFT JOIN，可先將左表資料載入表值變數，再用右表更新，分兩步驟執行，大幅降低成本。

測試查詢效能時，務必多次執行取平均值，排除快取與鎖定干擾。

4. 靈活運用笛卡兒積 (CROSS JOIN)

CROSS JOIN 常被視為禁忌，但特定情境極具價值：需回傳「所有組合」甚至無資料的組合 (如每月銷售報表，含零銷售月份)。

範例：建立月份表與有交易客戶表，再 CROSS JOIN 產生完整矩陣，最後以實際銷售資料更新。此法可一次產生所有月份-客戶組合，避免 LEFT JOIN 遗漏零值月份。

DECLARE @tblMonths TABLE (sMonth VARCHAR(7));
DECLARE @tblCustomers TABLE (CustomerID CHAR(10), CompanyName VARCHAR(50));
-- 填入 12 個月份與有交易客戶
INSERT @tblFinal SELECT m.sMonth, c.CustomerID, ..., 0
FROM @tblMonths m CROSS JOIN @tblCustomers c;
-- 再以實際銷售資料 UPDATE @tblFinal

注意：若只是為了篩選結果，INNER JOIN 效率遠高於 CROSS JOIN + WHERE。

5. 拾遺補零：其他效能小撇步

• WHERE 優於 HAVING：分組前先在 WHERE 過濾，減少分組資料量。
• DISTINCT 替代簡單 GROUP BY：無彙總函數時，DISTINCT 效率更高。
• 避免不必要的 DISTINCT：若查詢本身唯一，加上 DISTINCT 只會增加開銷。

核心原則：測試、測試、再測試，比較不同寫法效能，找出最適合應用場景的方案。