形容人很好的词语:提高DB2数据库SQL存储过程性能学习笔记

     高性能的SQL过程是数据库开发人员所追求的,我将不断把学到的,或在实际开发中用到的一些提高SQL过程性能的技巧整理出来,温故而知新.

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 INTO tab_comp VALUES (item1, price1, qty1),
                        (item2, price2, qty2),
                        (item3, price3, qty3);

执行这个多行 INSERT 语句所需时间大约是执行原来三条语句的三分之一。孤立地看，这一改进看起来似乎是微乎其微的，但是，如果这一代码段是重复执行的（例如该代码段位于循环体或触发器体中），那么改进是非常显著的。

类似地，如下所示的 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 表达式编写的逻辑不但比使用 CASE 或 IF 语句编写的逻辑更紧凑，而且更有效。

请考虑下面的 SQL PL 代码片段：

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 的一次处理一个集合语义

诸如循环、赋值和游标之类的过程化构造允许我们表达那些只使用 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);

在研究改进现有过程逻辑的性能时，为消除游标循环而花费的任何时间都可能是值得的。

4，改进游标性能

如果存储过程中的逻辑确实需要游标，那么要使性能最优，请牢记下面这些内容。

首先，请确保不使用高于您所需的隔离级别。隔离级别决定了 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 注册表，还可以为存储过程指定其它绑定选项。请参阅 Application Development Guide: Building and Running Applications 中有关“Setting Up the SQL Procedures Environment”方面的内容以获得更多信息。此外，有关隔离级别、锁定和分块的完整说明已超出了本文范围。请参阅 DB2 Administration Guide: Performance 和 SQL Reference 中有关 DECLARE CURSOR 的条目，以获取完整的说明。

5，在无副作用的情况下，请使用 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

等同于下面的函数：

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 语句从命令行调用函数：

VALUES (GetPrice('IBM', 324))

另一方面，与过程不同的是，您可以在允许表达式的任何上下文中调用函数：

SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 100;

SET price =   GetPrice(Vname, Pid);

因此，正如本节标题所展示的，当您只是从数据库抽取数据而不执行任何更改时，请考虑使用 SQL 函数而不是使用 SQL 过程。

6，使用用于临时数据的临时表

在 V7 中，DB2 引入了临时表。对临时表的操作通常比对常规表的操作快。让我们看一些原因：

首先，临时表的创建不会涉及向目录中插入项，并且临时表的使用也不会涉及对目录的访问；因此，不会有目录争用问题。
因为临时表只能由创建它们的应用程序访问，因此在其操作中不会涉及锁定问题。
如果指定了 NOT LOGGED 选项，则不对临时表上的操作记录日志（当然，这样就不可能回滚更改）。因此，如果您的存储过程生成了大量临时数据，并只打算在数据库的一个会话中使用它们，那么请将这些数据存储进临时表，这样可以显著地改进性能。
在对 SQL 过程中的临时表进行任何应用之前，表定义在编译环境中必须是可用的。例如，在下面的 CLP 脚本（该脚本使用“%”作为语句的终结符）中，表定义的唯一目的就是能够创建 SQL 过程：

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。跟其它任何动态查询一样，在编译该查询之后，它将以已编译的形式保留在包高速缓存中。在下一次执行相同的查询时，仅当无法在高速缓存发现它时，DB2 才重新编译它。

如果您打算创建相对较大的临时表，并对这些表运行几个查询，请考虑定义索引并对它们运行 runstats（显然后者是填充了表后进行的）。下一节将介绍更多这方面的内容。

有关在 SQL 过程中使用临时表的最后一个说明是：如果需要根据在同一个过程中创建的临时表返回结果集，那么必须在嵌套的复合语句中定义结果集，如下面的示例所示：

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 路径中的任何函数和类型都被当作函数和类型解析。（请参阅 REBIND 命令的 Command Reference 条目，以获取更多详细信息。）