SQL 預存程序效能優化：實用提示與技巧

簡介

關於如何調優資料庫系統與應用程式的建議來源眾多。如〈OLTP 應用程式的 DB2 調優技巧〉等文章，透過事務與資料並行性、分析查詢計畫，給出從表空間、索引設計到緩衝池記憶體分配等方面的建議——這些是效能調優的基礎知識。但針對如何組織預存程序本身的邏輯並著眼其效能的專門建議卻較少見。本文即提供此類建議。雖本文著重 SQL 程序，但大多數資訊同樣適用於用其他語言編寫的應用程式中或預存程序中嵌入的 SQL 邏輯。

背景知識與術語

深入細節前，先回顧 DB2 程序化 SQL 的基本術語與概念。程序化 SQL 建構（標量變數、IF 陳述式、WHILE 迴圈）在 DB2 UDB V7 引入。舊版支援 C 和 Java 作為預存程序語言。V7 引入 SQL 預存程序，以及臨時表、應用程式儲存點、識別欄等促進 OLTP 開發的特性。

建立 SQL 程序時，DB2 將程序主體中的 SQL 查詢與程序邏輯分離。為達最佳效能，SQL 查詢被靜態編譯成套件中的區段（區段主要由 DB2 優化器為該查詢選擇的存取路徑組成；套件是區段的集合）。程序邏輯則編譯成 DLL（動態連結庫）。

執行期間，每當控制權從程序邏輯流向 SQL 陳述式，DLL 與 DB2 引擎間就存在「上下文切換」。（DB2 V8 中 SQL 程序在「非受保護模式」下運行，即與 DB2 引擎在同一尋址空間。此處的上下文切換非作業系統層級的完全切換，而是 DB2 中層的切換。）減少頻繁呼叫的程序（如 OLTP 程序）或處理大量列的程序（如資料清理程序）中的上下文切換次數，對其效能有顯著影響。本文多個技巧旨在減少這些切換。

最初（DB2 UDB V7 GA）僅允許 SQL 程序語言（SQL PL）用於 SQL 程序。後來（V7.2）在 SQL 函數與觸發器主體中支援該語言子集，稱為內聯 SQL PL。「內聯」凸顯其與完整語言的重要差異：SQL PL 程序將 SQL 查詢靜態編譯成套件區段實現；內聯 SQL PL 函數則將函數主體內聯到使用它的查詢中實現。

具體優化技巧

1. 單一陳述式取代多重陳述式

多行 INSERT 序列：

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

可改寫為單一多列 INSERT：

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

執行時間約為原三條語句的三分之一。若程式碼段重複執行（位於迴圈或觸發器中），改進極顯著。

類似地，多條 SET 語句：

SET A = expr1;
SET B = expr2;
SET C = expr3;

可合併為一條 VALUES 語句：

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

若語句間無相依性，語義得以保留。但若有相依性：

SET A = monthly_avg * 12;
SET B = (A / 2) * correction_factor;

改寫為：

VALUES (monthly_avg * 12, (A / 2) * correction_factor) INTO A, B;

不保留原始語義，因 INTO 關鍵字前的運算式以「平行」方式求值，B 的賦值不以 A 的值為基礎。

2. 以 SQL 表達式取代多條 SQL 陳述式

SQL 提供程序式（IF、CASE 陳述式）與函數式（CASE 表達式）兩類條件建構。使用 CASE 表達式編寫的邏輯不僅更緊湊，也更有效。

範例：

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;

IF 條件僅決定插入 tab_comp.Val 的值。利用 CASE 表達式改寫為單一 INSERT：

INSERT INTO tab_comp(Id, Val)
VALUES(Oid,
  CASE
    WHEN (Price <= MaxPrice) THEN Price
    ELSE MaxPrice
  END);

CASE 表達式可用於任何期望標量值的脈絡，特別是賦值號右側：

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

改寫為：

SET ProdName = (CASE
  WHEN (Name IS NOT NULL) THEN Name
  WHEN (NameStr IS NOT NULL) THEN NameStr
  ELSE DefaultName
END);

此例有更好解法：SET ProdName = COALESCE(Name, NameStr, DefaultName);

3. 善用 SQL 的集合式（Set-at-a-time）語意

程序化建構（迴圈、賦值、游標）允許表達純 SQL DML 無法表達的計算。但擁有程序語句時，即使計算實際上可用 SQL DML 表達，轉向程序語句仍有風險。程序計算效能比用 DML 表達同一計算慢數個數量級。

範例程式碼片段：

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;

先應用上節技巧改進迴圈體：

WHILE SQLCODE <> 100 DO
  INSERT INTO tab_sel VALUES (CASE
    WHEN v1 > 20 THEN 20
    ELSE v1
  END, v2);
  FETCH cur1 INTO v1, v2;
END WHILE;

進一步觀察，整個程式碼區塊可寫成帶 SELECT 子句的單一 INSERT：

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

原始表述中，SELECT 每行都有程序層與資料流層的上下文切換。最終表述零切換，優化器有機會對整個計算進行全域優化。若每個 INSERT 針對不同表，則無法這樣簡化：

IF (v1 > 20) THEN
  INSERT INTO tab_default VALUES (20, v2);
ELSE
  INSERT INTO tab_sel VALUES (v1, v2);
END IF;

但仍可利用集合特性：

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 對程序讀取/更新列套用的鎖定數量。層級越高，鎖定越多，並發性越低。可用 DB2_SQLROUTINE_PREPOPTS 登錄變數指定 SQL 程序隔離層級。例如設為 Uncommitted Read (UR)：
  db2set DB2_SQLROUTINE_PREPOPTS="ISOLATION UR"
  （需重啟 DB2 實例生效）。預設為 Cursor Stability (CS)。有時為正確性需 Repeatable Read (RR)，但建立需 RR 的程序後，必須將登錄變數重設為較低層級。
• 單一查詢覆蓋隔離層級：DECLARE cur1 CURSOR FOR SELECT col1 FROM tab_comp WITH UR; 無論登錄變數設定，此查詢以 UR 執行。
• 游標可更新性：可刪除游標需獲取互斥鎖，防止其他應用讀取（除非其隔離層級為 UR），且無法執行列區塊塊。唯有不可刪除游標可行區塊塊。在 SELECT 中指定 FOR READ ONLY 顯式宣告不可刪除，或 FOR UPDATE 宣告可刪除。DB2 據此（及 BLOCKING 選項）決定是否對游標使用列區塊塊。預設對 FOR READ ONLY 游標永遠使用區塊塊，除非指定 BLOCKING NO。若用 BLOCKING ALL，含混游標（未定義 FOR READ ONLY 或 FOR UPDATE）也會使用區塊塊。
  建議：可能時在游標定義用 FOR READ ONLY；含混游標時用 BLOCKING ALL。設定方式同樣用 DB2_SQLROUTINE_PREPOPTS：
  db2set DB2_SQLROUTINE_PREPOPTS="ISOLATION UR BLOCKING ALL"
• 區塊塊對傳回大型結果集的程序特別重要。
• 透過 DB2_SQLROUTINE_PREPOPTS 還可指定其他綁定選項。

5. 無副作用情況下優先使用 SQL 函數

SQL 程序與 SQL 函數實現技術不同。SQL 程序中查詢單獨編譯，每個查詢成套件一個區段，建立時編譯，重建程序或重新綁定套件前不重新編譯。SQL 函數中查詢一起編譯，彷彿函數主體是單一查詢；每當編譯使用 SQL 函數的陳述式時，也編譯 SQL 函數。

SQL 函數中程序陳述式與資料流陳述式在同一層執行，控制權流動時無上下文切換。因此，相同程式碼段作為函數實現時通常比作為程序實現快。但函數限制：不能包含改變資料庫狀態的陳述式（INSERT/UPDATE/DELETE 不允許），僅允許完整 SQL PL 子集（無 CALL、游標、條件處理）。

儘管有限制，大多數 SQL 程序可在無副作用下轉換為 SQL 函數。例如程序：

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);
  ELSE IF 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);
  ELSE IF Vendor = 'Vendor 2' THEN
    SET price = (SELECT Price FROM V2Table WHERE Pid = GetPrice.Pid);
  END IF;
  RETURN price;
END

呼叫差異：程序用 CALL，函數用 VALUES (GetPrice('IBM', 324))。但函數可用於任何允許運算式的脈絡：SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 100; 或 SET price = GetPrice(Vname, Pid);。純從資料庫抽取資料不執行變更時，請考慮用 SQL 函數而非 SQL 程序。

6. 暫存資料使用暫存表

V7 引入暫存表。操作暫存表通常比常規表快：

• 建立不涉及目錄插入項，使用不涉及目錄存取，無目錄爭用。
• 僅建立者應用程式可存取，操作無鎖定問題。
• 指定 NOT LOGGED 選項時，操作不記錄日誌（當然無法回滾）。若預存程序產生大量暫存資料且僅在單一資料庫會話中使用，存入暫存表可顯著改善效能。

SQL 程序中使用暫存表前，表定義在編譯環境必須可用。CLP 腳本範例（以 % 為陳述式終結符）：

CONNECT TO sample %
DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20)) %
CREATE PROCEDURE INSTT(P1 INT, P2 CHAR(20))
BEGIN
  INSERT INTO SESSION.TT VALUES(P1, P2);
END %
CONNECT RESET %

執行 CONNECT RESET 後暫存表不復存在。執行時，應用程式必須確保在執行使用暫存表的首個查詢前表已存在。引用暫存表的任何查詢都會被動態編譯（即使寫成靜態 SQL），編譯後以編譯形式保留在套件快取中，下次執行同查詢時僅當快取找不到時才重新編譯。

若打算建立較大暫存表並執行多個查詢，請考慮定義索引並執行 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 %

原因：DECLARE GLOBAL TEMPORARY TABLE 為可執行陳述式，只能寫在宣告陳述式（如 DECLARE CURSOR）之後。若在外部作用域遊標定義後宣告表，編譯 DECLARE CURSOR 時表在編譯環境不可用，編譯失敗。

7. 保持 DB2 優化器處於知情狀態

建立程序時，其 SQL 查詢編譯成套件區段。DB2 優化器根據表統計資訊（表大小、欄位資料值相對頻率）與編譯時可用索引選擇執行計畫。表經重大變更時，讓 DB2 重新收集統計資訊是好主意。更新統計資訊或建立新索引時，重新綁定與使用該表的 SQL 程序相關聯的套件，使 DB2 建立使用最新統計與索引的計畫。

用 RUNSTATS 更新表統計。重新綁定 SQL 程序套件可用內建程序 REBIND_ROUTINE_PACKAGE（DB2 V8 可用）。例如重新綁定 MYSCHEMA.MYPROC：

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'MYSCHEMA.MYPROC', 'ANY')

'P' 表明套件對應程序，'ANY' 表明 SQL 路徑中任何函數與型別視為函數與型別解析。

結語

本文提供一類可能有助於改進 SQL 程序效能的提示與技巧。一般原則：優先考慮系統效能（硬體與 OS）與資料庫管理器（緩衝池、容器、表空間等）基本議題。DB2 配置顧問對後者很有幫助。確保應用程式關鍵查詢計畫合適。最後利用本文建議研究改進預存程序與應用程式。祝調優工作順利！