SQL 預存程序性能優化：核心技巧與最佳實踐

背景與術語

DB2 UDB V7 引入 SQL 預存程序，將 SQL 查詢靜態編譯為包中的節，邏輯編譯為 DLL。執行時在 DLL 與 DB2 引擎間發生「上下文切換」。減少頻繁調用或大量處理行的過程中的切換次數，對性能影響顯著。V7.2 進一步支持內聯 SQL PL，函數主體直接內聯到調用查詢中，避免切換開銷。

核心優化原則

1. 合併多條語句為單一語句

多行 INSERT 可合併：

INSERT INTO tab_comp VALUES (item1, price1, qty1),
 (item2, price2, qty2),
 (item3, price3, qty3);

執行時間約為三條單獨語句的 1/3。若在循環或觸發器中重複執行，提升顯著。

無關聯的 SET 語句可合併為 VALUES：

VALUES expr1, expr2, expr3 INTO A, B, C;

注意：表達式並行求值，若後賴前結果（如 SET B = (A/2)*factor）則不可合併。

2. 用 CASE 表達式替代過程控制語句

CASE 表達式屬函數型條件構造，可在任何標量值上下文使用，避免過程層與數據流層切換。

示例 1：INSERT 中的條件賦值

-- 原 IF 邏輯
IF (Price <= MaxPrice) THEN
 INSERT INTO tab_comp(Id, Val) VALUES(Oid, Price);
ELSE
 INSERT INTO tab_comp(Id, Val) VALUES(Oid, MaxPrice);
END IF;

-- 改為單一 INSERT + CASE
INSERT INTO tab_comp(Id, Val)
 VALUES(Oid,
  CASE
   WHEN (Price <= MaxPrice) THEN Price
   ELSE MaxPrice
  END);

示例 2：變量賦值鏈

-- 原 IF-ELSEIF 鏈
IF (Name IS NOT NULL) THEN SET ProdName = Name;
ELSEIF (NameStr IS NOT NULL) THEN SET ProdName = NameStr;
ELSE SET ProdName = DefaultName;
END IF;

-- 改為 CASE 表達式
SET ProdName = (CASE
 WHEN (Name IS NOT NULL) THEN Name
 WHEN (NameStr IS NOT NULL) THEN NameStr
 ELSE DefaultName
 END);

-- 更佳：COALESCE
SET ProdName = COALESCE(Name, NameStr, DefaultName);

3. 採用集合操作替代游標循環

游標循環每行都涉及上下文切換，性能差數個數量級。若邏輯可用純 SQL 表達，應改寫。

原游標循環：

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
 IF (v1 > 20) THEN
  INSERT INTO tab_sel VALUES (20, v2);
 ELSE
  INSERT INTO tab_sel VALUES (v1, v2);
 END IF;
 FETCH cur1 INTO v1, v2;
END WHILE;

優化 1：循環體內用 CASE（仍有切換）

INSERT INTO tab_sel VALUES (CASE WHEN v1 > 20 THEN 20 ELSE v1 END, v2);

優化 2：完全集合化（零切換，優化器全局優化）

INSERT INTO tab_sel
SELECT CASE WHEN col1 > 20 THEN 20 ELSE col1 END, col2
FROM tab_comp;

若插入目標表不同，可拆分為兩條 INSERT-SELECT：

INSERT INTO tab_sel SELECT col1, col2 FROM tab_comp WHERE col1 <= 20;
INSERT INTO tab_default SELECT col1, col2 FROM tab_comp WHERE col1 > 20;

4. 優化游標性能

• 隔離級別：越高鎖越多，並發越低。用 DB2_SQLROUTINE_PREPOPTS 設置，如 db2set DB2_SQLROUTINE_PREPOPTS="ISOLATION UR"（需重啟實例）。單查詢可用 WITH UR 覆蓋。
• 只讀游標：加 FOR READ ONLY 允許行分塊，減少開銷。含混游標用 BLOCKING ALL 綁定選項：db2set DB2_SQLROUTINE_PREPOPTS="ISOLATION UR BLOCKING ALL"。大結果集尤為重要。

5. 無副作用邏輯優先用 SQL 函數

SQL 函數查詢與邏輯同層編譯執行，無上下文切換。限制：不允許修改數據、CALL、游標、條件處理。但大多數只讀過程可轉換。

過程轉函數示例：

-- 過程
CREATE PROCEDURE GetPrice (IN Vendor CHAR(20), IN Pid INT, OUT price DECIMAL(10,3))
LANGUAGE SQL
BEGIN
 IF Vendor = 'Vendor 1' THEN
  SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid);
 ELSEIF Vendor = 'Vendor 2' THEN
  SET price = (SELECT Price FROM V2Table WHERE Pid = GetPrice.Pid);
 END IF;
END

-- 函數
CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)
RETURNS DECIMAL(10,3)
LANGUAGE SQL
BEGIN
 DECLARE price DECIMAL(10,3);
 IF Vendor = 'Vendor 1' THEN
  SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid);
 ELSEIF Vendor = 'Vendor 2' THEN
  SET price = (SELECT Price FROM V2Table WHERE Pid = GetPrice.Pid);
 END IF;
 RETURN price;
END

調用差異：過程用 CALL，函數用 VALUES GetPrice('IBM', 324) 或嵌入 SQL 表達式中。

6. 善用臨時表存放中間數據

優勢：無目錄爭用、無鎖開銷、可 NOT LOGGED 避免日誌開銷。編譯時需表定義存在，運行時首次使用前必須創建。引用臨時表的查詢屬動態編譯，編譯後緩存在包緩存。大臨時表建議建索引並 RUNSTATS。

若需返回基於同過程創建的臨時表的結果集，須在嵌套復合語句中聲明游標：

CREATE PROCEDURE INSTT2(P1 INT, P2 CHAR(20))
BEGIN
 DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20));
 INSERT INTO SESSION.TT VALUES(P1, P2);
 BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM SESSION.TT;
 END;
END