SQL过程的性能：提示和技巧

核心提示：使用 SQL 的一次处理一个集合语义诸如循环、赋值和游标之类的过程化构造允许我们表达那些只使用 SQL DML 语句是不可能表达的计算，但是，SQL过程的性能：提示和技巧(2)，当我们拥有一些可以随意使用的过程语句时，即使我们手头的计算实际上仅使用 SQL DML 语句就可表达，除非它们的隔离级别是 UR），而行分块通

使用 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），而行分块通过在一个操作中检索行块，从而减少了用于游标的数据库管理器开销。