PL/SQL开发中动态SQL的使用之四

使用绑定提高动态 SQL 性能


由于几个方面的原因，开发人员通常会选择不在 PL/SQL 中使用动态 SQL。一个方面的原因是这样做有风险，因为 SQL 语句到运行时期才解析，所以捕获简单语法错误的难度会更大。此外，很多动态 SQL 的尝试都会带来性能问题，简单编写动态 SQL 的复杂性对动态 SQL 也有负面的影响。

这里给出一个简单的测试，我们使用两个方法将某段范围内的数字插入到一个表中，然后对两种方法进行了对比：

drop table mytest;
create table mytest(i number);

prompt 1) using pure insert
set timing on;
begin
    for i in 1..10000 loop
        insert into mytest values(i);
    end loop;
end;
/
show errors;
set timing off;

truncate table mytest;

prompt 2) using execute immediate
set timing on;
begin
    for i in 1..10000 loop
        execute immediate 'insert into mytest values('||i||')';
    end loop;
end;
/
show errors;
set timing off;


在我的测试中，第二个 PL/SQL 块的运行时间相当于第一个 PL/SQL 块运行时间的六倍。然而，这不是一个公平的比较，因为它违反了动态 SQL 的一个黄金规则：在能够绑定时永远不要串联（concatenation）。固定串联意味着需要解析、分解和执行10000个独立的 SQL 语句。第一个例子中的原生 PL/SQL 插入语句实际上生成一个递归 SQL 语句——'insert into mytest values(:1)'——然后在每次迭代时绑定具体值。

下面是动态 SQL 的一个稍好的对比：

truncate table mytest;

prompt 3) using execute immediate with bind
set timing on;
begin
    for i in 1..10000 loop
        execute immediate 'insert into mytest values(:i)' using in i;
    end loop;
end;
/
show errors;
set timing off;

这段代码的运行时间是第一个块的运行时间的两倍。其速度是第二个 PL/SQL 块运行速度的三倍。然而，动态 SQL 依然占据劣势。execute immediate依然会进行解析，但是它使用 SQL 共享来分解语句。

理想的情况是 PL/SQL 允许我们在 OPEN 过程中创建游标，然后再有一些新的命令允许我们 EXECUTE <cursor> USING……即使在 Oracle 10g 中也没有这样的命令。我已经观察原生动态 SQL 很长时间了，我依然发现在这些情况下它没有DBMS_SQL 强大。

PL/SQL 用户指南警告说DBMS_SQL 的性能没有EXECUTE IMMEDIATE 的性能好，但是让我们自己试一下看看：

truncate table mytest;

prompt 4) using dbms_sql
set timing on;
declare
    c   integer;
    r   integer;
begin
    c := dbms_sql.open_cursor;
    for i in 1..10000 loop
        dbms_sql.parse(c,'insert into mytest values('||i||')',dbms_sql.native);
        r := dbms_sql.execute(c);
    end loop;
    dbms_sql.close_cursor(c);
end;
/
show errors;
set timing off;

truncate table mytest;

prompt 5) using dbms_sql with bind
set timing on;
declare
    c   integer;
    r   integer;
begin
    c := dbms_sql.open_cursor;
    dbms_sql.parse(c,'insert into mytest values(:i)',dbms_sql.native);
    for i in 1..10000 loop
        dbms_sql.bind_variable(c,':i',i);
        r := dbms_sql.execute(c);
    end loop;
    dbms_sql.close_cursor(c);
end;
/
show errors;
set timing off;


第四个例子的问题与第二个例子一样——它使用了串联。这也说明 PL/SQL 手册在这种情况下是对的。第四个例子的运行时间是第一个例子运行时间的七倍。DBMS_SQL 确实比EXECUTE IMMEDIATE 慢，但是EXECUTE IMMEDIATE 依然没有DBMS_SQL 灵活。

第五个例子是一个惊喜——它的完成时间与第一个例子几乎完全相同。它的执行过程几乎与第一个例子完全相同，它的编码难度明显比较大并且更容易出现错误，但是对于很多任务来说，动态 SQL 可能很必要。

更进一步地对比，等效的 Java 存储过程（总是动态的），它的运行时间与原始的 PL/SQL 以及带绑定的DBMS_SQL 几乎相同：

import java.sql.*;

public class dynsql
{
    public static void ins() throws SQLException
    {
        Connection conn
            = DriverManager.getConnection("jdbc:default:connection:");
        PreparedStatement pstmt
            = conn.prepareStatement("insert into mytest values(?)");
        for (int i=0;i<10000;i++)
        {
            pstmt.setInt(1,i);
            pstmt.executeUpdate();
        }
        pstmt.close();
    }
}

set timing off;
create or replace procedure dynsql_ins as language java name 'dynsql.ins()';
/
show errors;

truncate table mytest;
set timing on;
exec dynsql_ins;
set timing off;