改进 SQL 过程的性能

创建 SQL 过程时，过程体中的 SQL 查询将与过程逻辑进行分离。为使性能达到最佳，SQL 查询以静态方式编译为包中的若干段。对于静态编译的查询，节主要由优化器为该查询选择的访问方案组成。

SQL PL 和内联 SQL PL 的编译方式概述

在讨论如何改进 SQL 过程的性能之前，应先讨论如何在执行 CREATE PROCEDURE 语句时编译这些过程。

创建 SQL 过程时，过程体中的 SQL 查询将与过程逻辑进行分离。为使性能达到最佳，SQL 查询以静态方式编译为包中的若干段。对于静态编译的查询，节主要由优化器为该查询选择的访问方案组成。包是若干段的集合。有关程序包和段的更多信息，请参阅 “SQL 参考”。过程逻辑编译为动态链接的库。

执行过程期间，每次控制权从过程逻辑流至 SQL 语句时，DLL 与数据库引擎之间都会执行上下文切换。SQL 过程在不设防方式下运行，这意味着它们与数据库引擎在同一地址空间中运行。因此，我们在此处提到的上下文切换并非操作系统级别的完整上下文切换，而是数据库引擎内的层切换。减少频繁调用的过程（如 OLTP 应用程序中的过程）或处理大量行的过程（如执行数据清理的过程）中的上下文切换数目对它们的性能有很大影响。

包含 SQL PL 的 SQL 过程是通过以静态方式将其 SQL 查询编译成包中的段来实现的，而内联 SQL PL 函数是通过将函数体直接插入到使用它的查询中来实现的。SQL 函数中的查询是一起编译的，就像函数体是单个查询一样。每次编译使用了该函数的语句时，都会进行此编译。与 SQL 过程中进行的操作不同，SQL 函数中的过程语句与数据流语句在同一层中执行。因此，每次控制权从过程流至数据流语句（或相反）时，不会进行上下文切换。

如果对逻辑没有副作用，请改为使用 SQL 函数

因为过程中的 SQL PL 与函数中的内联 SQL PL 在编译上存在差别，所以在仅查询 SQL 数据而不修改数据（即，对数据库内部或外部的数据没有副作用）时，假定过程代码块在函数中执行的速度比在过程中执行的速度快是合理的。

仅当 SQL 函数支持您需要执行的所有语句时，才能这样做。SQL 函数不能包含修改数据库的 SQL 语句。而且，只有一部分 SQL PL 在函数的内联 SQL PL 中可用。例如，不能在 SQL 函数中执行 CALL 语句、声明游标或返回结果集。

以下是 SQL 过程的示例，其中包含可用于转换至 SQL 函数以使性能达到最佳的 SQL PL：


  CREATE PROCEDURE GetPrice (IN Vendor CHAR&(20&),
                             IN Pid INT, OUT price DECIMAL(10,3))
  LANGUAGE SQL
  BEGIN
    IF Vendor eq; ssq;Vendor 1ssq;
      THEN SET price eq; (SELECT ProdPrice 
                              FROM V1Table 
                              WHERE Id = Pid);
    ELSE IF Vendor eq; ssq;Vendor 2ssq;
      THEN SET price eq; (SELECT Price FROM V2Table
                              WHERE Pid eq; GetPrice.Pid);
    END IF;
  END

以下是重写的 SQL 函数：

  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

请记住，调用函数与调用过程不同。要调用函数，请使用 VALUES 语句，或在表达式有效的位置（例如，在 SELECT 或 SET 语句中）调用函数。下列任何一项都是调用新函数的有效方式：

  VALUES (GetPrice('IBM', 324))
  
  SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 10
  
  SET price = GetPrice(Vname, Pid)

如果 SQL PL 过程中只需要使用一个语句，那么应尽量避免在其中使用多个语句。

尽管通常从理念上讲应该编写简洁的 SQL，但实际操作时很容易忘记这一点。例如以下 SQL 语句：

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

多行插入所需时间大概是执行三个原始语句所需时间的 1/3。孤立地看，此改进可能显得微不足道，但如果代码片段重复执行（例如在循环或触发器体中），那么此改进会很重要。

同样，类似如下的 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 或 IF 语句编辑的逻辑相比，使用 CASE 表达式编写的逻辑更加紧凑和高效。

考虑以下 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 表达式。特别是可在赋值语句的右端使用 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 的一次性设置语义

过程构造（如循环、赋值和游标）允许我们表达仅使用 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;

开始时可通过应用上一节“将多个 SQL 语句减少为单个 SQL 表达式”中讨论的变换来改进循环体：

  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 的一次设置特点：

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

查看并改进现有过程逻辑的性能时，消除游标循环所花的时间会有所回报。

及时通知优化器

创建过程时，其 SQL 查询编译为包中的若干段。除依据其他信息外，优化器还会根据表统计信息（例如，表大小或列中数据值的相对频率）以及编译查询时可用的索引来为查询选择执行方案。表进行重大更改时，最好再次收集这些表的统计信息。更新统计信息或创建新索引时，最好重新绑定与使用表的 SQL 过程相关联的包，以创建使用最新统计信息和索引的方案。

可使用 RUNSTATS 命令来更新表统计信息。要重新绑定与 SQL 过程相关联的包，可使用 REBIND_ROUTINE_PACKAGE 内置过程。例如，可使用以下命令来重新绑定过程 MYSCHEMA.MYPROC 的包：

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

其中 P 指示该包对应于过程，ANY 指示会考虑对 SQL 路径中的所有函数和类型进行函数和类型解析。请参阅 REBIND 命令的命令参考条目来了解更多详细信息。

使用数组

可使用数组在应用程序与存储过程之间高效传递数据集合，以及在 SQL 过程中存储和操作瞬态数据集合而不必使用关系表。针对 SQL 过程中可用的数组的操作程序允许高效存储和检索数据。用于创建中等大小数组的应用程序性能比创建大型数组（规模为几兆字节）的应用程序性能好很多，原因是整个数组存储在主存储器中。