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

核心提示：简介有关如何调优数据库系统和应用程序的可用建议来源有很多，诸如 OLTP 应用程序的 DB2 调优技巧（以前在 IBM® DB2® 开发者园地上发表）之类的文章通过使用事务和数据并行性以及分析查询方案，SQL过程的性能：提示和技巧，给出了从表空间和索引设计到缓冲池的内存分配等方面的建议，这些方面的内容是

简介

有关如何调优数据库系统和应用程序的可用建议来源有很多。诸如 OLTP 应用程序的 DB2 调优技巧（以前在 IBM® DB2® 开发者园地上发表）之类的文章通过使用事务和数据并行性以及分析查询方案，给出了从表空间和索引设计到缓冲池的内存分配等方面的建议。这些方面的内容是性能调优的基础知识。

但是，有关如何组织存储过程自身中的逻辑并着眼于其性能的专门建议却并不多见。本文就提供了这样一种建议。尽管本文着重于介绍 SQL 过程，但是这里所提供的大多数信息同样适用于用其它语言编写的在应用程序中或存储过程中嵌入的 SQL 逻辑。

背景知识和术语

在深入研究详细问题之前，让我们先回顾 DB2 中有关过程化 SQL 的一些基本术语和概念。过程化 SQL 构造（例如标量变量、IF 语句和 WHILE 循环）是在 DB2 Universal Database™ (UDB) V7 发行版中引入 DB2 的。以前的 DB2 发行版支持 C 和 Java™ 作为存储过程的语言。V7 引入了 SQL 存储过程，以及其它许多可以促进 OLTP 应用程序开发的特性（例如临时表、应用程序保存点和标识列）。

当创建 SQL 过程时，DB2 将过程主体中的 SQL 查询与过程逻辑区分开来。为了使性能最优，SQL 查询被静态地编译成包中的节。（对于静态编译的查询而言，节主要是由 DB2 优化器为该查询选择的存取方案构成的。包是节的集合。有关包和节的更多信息，请参阅 DB2 SQL 参考大全，第 1 卷。）另一方面，过程逻辑被编译成 DLL（动态链接库）。

在过程的执行期间，每当控制从过程逻辑流向 SQL 语句时，在 DLL 和 DB2 引擎之间就存在“上下文切换”。（在 DB2 V8 中，SQL 过程是在“不受保护的方式”下运行的，即与 DB2 引擎在相同的寻址空间中。因此我们这里谈及的上下文切换并不是操作系统级别上的完全的上下文切换，而是指 DB2 中层的更换。）减少频繁调用的过程（例如 OLTP 应用程序中的过程）或者处理大量行的过程（例如执行数据清理的过程）中的上下文切换次数，对它们的性能有显著的影响。本文中的几个技巧恰好旨在减少这些上下文切换。

刚开始的时候（DB2 通用数据库 V7 GA），只允许在 SQL 过程中使用 SQL 过程语言（通常称为 SQL PL）。后来（在 DB2 UDB V7.2 中），在 SQL 函数和触发器主体中开始支持该语言的子集。SQL PL 的这个子集即所谓的 内联（inline）SQL PL。“内联”一词突出显示了它与完整语言的重要区别。SQL PL 过程是通过将其单独的 SQL 查询静态地编译成包中的节实现的，而内联 SQL PL 函数就象其名称所展示的，是通过将函数主体内联到使用它的查询中实现的。稍后我们将再看一下内联 SQL PL 及其用法的一些示例。

现在，让我们研究在使用 SQL 过程语言时可用来提高性能的一些具体工作。

在只使用一条语句即可做到时避免使用多条语句

让我们从一个简单的编码技巧开始。如下所示的单个 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 的值为基础，这是原始语句预期的语义。

从多个 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);