SQL 過程的性能: 提示和技巧

值得注意的是，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);

使用 SQL 的一次處理一個集合語義

諸如循環、賦值和游標之類的過程化構造允許我們表達那些只使用 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;

首先，通過應用上一節討論的轉換可以改進循環體：

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
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 語句針對的都是不同的表，那麼這種引人注目的簡化是不可能的，如下所示。

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_default VALUES (20, v2);
　ELSE
　　INSERT INTO tab_sel VALUES (v1, v2);
　END IF;
　FETCH cur1 INTO v1, v2;
END WHILE;

但是，這裡也可以利用 SQL 的一次處理一個集合（set-at-a-time）特性：

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);

在研究改進現有過程邏輯的性能時，為消除游標循環而花費的任何時間都可能是值得的。

改進游標性能

如果存儲過程中的邏輯確實需要游標，那麼要使性能最優，請牢記下面這些內容。

首先，請確保不使用高於您所需的隔離級別。隔離級別決定了 DB2 對過程讀取或更新的行應用的鎖定的數量。隔離級別越高，DB2 將執行的鎖定越多，因此為同一資源而競爭的應用程序之間的並發就越少。例如，使用可重復讀（Repeatable Read，RR）隔離級別的過程將形成對其讀取的任何行的共享鎖，而使用游標穩定性（Cursor Stability，CS）的過程只會鎖定任何可更新游標的當前行。可以使用 DB2_SQLROUTINE_PREPOPTS 注冊表變量來指定 SQL 過程的隔離級別。例如，要將 SQL 過程的隔離級別設置為未提交的讀（Uncommitted Read）（最低的級別，用於訪問只讀數據的過程），請使用下面這條命令：

db2set　DB2_SQLROUTINE_PREPOPTS="ISOLATION UR"

注：要使該設置生效，必須重新啟動 db2 實例。

DB2 中缺省的隔離級別是游標穩定性。但是，當然了，為了保持應用程序的正確性，有時需要使用可重復讀。還需記住一件重要的事情，一旦創建了需要可重復讀的過程，必須將 DB2_SQLROUTINE_PREPOPTS 重新設置回較低的隔離級別。

有關隔離級別還值得一提的是，DB2 允許我們在單獨的查詢中覆蓋缺省的隔離級別，如下所示：

DECLARE cur1 CURSOR FOR SELECT col1 FROM tab_comp WITH UR;

上面的查詢將以隔離級別 UR 進行執行，而不管 DB2_SQLROUTINE_PREPOPTS 中指定的隔離級別。

在嘗試改進游標性能時需要牢記的一個相關問題是游標的可更新能力。如果游標涉及的行是可以使用 INSERT 或 DELETE 語句中的 WHERE CURRENT OF 子句進行更新或刪除，那麼它就是 可刪除的。當游標可刪除時，DB2 必須獲取行上的 互斥鎖（與 共享鎖相對），並且不能執行行分塊。行上的互斥鎖甚至可以防止其它應用程序讀取該行（在互斥鎖被釋放之前，這些應用程序必須等待，除非它們的隔離級別是 UR），而行分塊通過在一個操作中檢索行塊，從而減少了用於游標的數據庫管理器開銷。

只有不可刪除的游標才可以進行行分塊。這就是為什麼讓 DB2 了解將如何使用游標是很重要的原因。通過在 SELECT 語句中指定 FOR READ ONLY 子句，可以將游標顯式地聲明為不可刪除，或者通過在 SELECT 語句中使用 FOR UPDATE 子句將其聲明為可刪除。根據該信息（並且還根據下面描述的 BLOCKING 選項），DB2 將確定是否將行分塊用於給定的游標。

缺省情況下，對於那些使用 FOR READ ONLY 子句定義的游標，DB2 將始終使用行分塊，除非指定了 BLOCKING NO 綁定選項。另一方面，如果使用了 BLOCKING ALL 綁定選項，那麼對於含混游標（既不是定義成 FOR READ ONLY 也不是定義成 FOR UPDATE 的游標），DB2 將使用行分塊。

簡而言之：如果可能，則在游標定義中使用 FOR READ ONLY 子句；如果您的過程包含含混游標，那麼請使用 BLOCKING ALL 綁定選項。要設置 BLOCKING 綁定選項的值，我們還可以使用 DB2_SQLROUTINE_PREPOPTS 注冊表變量。例如，要將 SQL 過程的隔離級別設置為未提交的讀，並將行分塊設置為 BLOCKING ALL，請使用下面這條命令：

db2set　DB2_SQLROUTINE_PREPOPTS="ISOLATION UR BLOCKING ALL"

對於返回大型結果集的過程而言，分塊特別重要。

通過使用 DB2_SQLROUTINE_PREPOPTS 注冊表，還可以為存儲過程指定其它綁定選項。

在無副作用的情況下，請使用 SQL 函數

正如我們在簡介中提及的，SQL 過程和 SQL 函數是使用不同技術實現的。SQL 過程中的查詢是單獨編譯的，每個查詢都成為包中的一個節。編譯是在過程創建時進行的，直到重新創建過程或者直到重新綁定其相關的包時才重新編譯這些查詢。

另一方面，SQL 函數中的查詢是一起編譯的，就好象函數體是一個查詢一樣。每當編譯一條使用 SQL 函數的語句時，也會對 SQL 函數進行編譯。

與 SQL 過程中所發生的情況不同，SQL 函數中的過程語句與數據流語句是在同一個層中執行的。因此，每當控制從過程語句流向數據流語句或相反時，並不發生上下文切換。

因為存在這些區別，所以當給定的過程代碼段作為函數實現時的執行速度通常比作為過程實現時要快。但是，當然了，有一個小問題。函數只能包含那些不會改變數據庫狀態的語句（例如 INSERT、UPDATE 或 DELETE 語句是不允許的）。並且只允許完整 SQL PL 語言的子集出現在 SQL 函數中（不能是 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