存储过程与SQL语句的一个故事

    我的一位朋友说：他从台湾知名技术作家李维先生的一本书中获悉，如果用存储过程封装SQL语句，系统效率将有极大提升。    他做过实验!!! －－我相信朋友做过实验，尽管非亲眼所见。不过我估计他的实验有问题，那样的实验不但蒙蔽了他，也蒙蔽了李维先生(如果他的著作中的内容没有被误会)，甚至更多的人。    然而我必须拿出证据，方能使人信服。    后来遇到一个具体的问题：客户端经常要向数据库插入记录。在J2EE中，一个 Entity Bean Home 的 create 方法调用中，一般就没用存储过程。朋友立马在观点上持反对意见( 可能是因为他暂时有来得及否决J2EE )，认为要是J2EE能够将“插入记录”诸如此类动作改为对存储过程的调用就好了。    我们因此再次发生争论(我仅是反对朋友的看法，但也没提出任何我自己的看法，因为要下一个结论是很不容易的)。最后我不得已而做了实验，分别在 Oracle 10g 和 postgreSQL 8.0.1 上。实验内容如下：

A、建表脚本：create table ztest( fieldA integer primary key, fieldB varchar(128), fieldC varchar(128) )

B、客户端请求 DBMS 执行的 insert SQL语句:insert into ztest values( ?1, ?2, ?3 ); -- ?1,?2,?3 将在运行时以合理的值替代之

C、客户端调用的存储过程（JDBC CallableStatement 调用）：Oracle:(调用方式 call up_add(...)，)create or replace procedure up_add( fieldA integer, fieldB varchar, fieldC varchar ) isbegin    insert into ztest values( fieldA, fieldB, fieldC);end;

postgreSQL:(客户端调用方式 select uf_add(...) )CREATE OR REPLACE FUNCTION uf_add (integer, varchar, varchar) RETURNS void AS@#begin    insert into ztest values($1,$2,$3);    return;end;@#LANGUAGE @#plpgsql@# VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

D、环境：postgreSQL:数据库服务器与客户端程序“都在本机”并“同时运行”Oracle: 独立数据库服务器(测试时始终有人在慢慢打字，应该对机器性能无影响）

测试：通过不同方式( 即 请求DBMS执行SQL语句 和 调用DBMS逻辑等价的存储过程)向测试表中连续加入 1024 记录

经多次反复测试，得结果如下postgreSQL:　两种方式下，测试时间均为 21- 24 seconds 之间 (每个结果的测试环境一致)

Oracle: 8次 SQL 执行请求分别用时(ms) 5422 4750 3875 3812 5672 3531 3484 3547              6次 存储过程调用分别用时(ms) 4578 4500 6297 4219 4547 5734(每个结果的测试环境一致)由此可知，存储过程封装简单的 SQL 语句，效率相当，且可能更低。

但很多朋友的确得出结论：存储过程的确比SQL快。为什么？----    因为他们测试时写了一个不具实际意义，同时也与SQL语句的“一次客户端调用”不具可比性的测试用存储过程。Oracle PL/SQL 描述方式如下,该方法一次调用就可以向数据表添加 1024 条记录，连网络通讯都省了。怪不得性能有“千倍差异”!

create or replace procedure up_add( ) isdeclare     n:integer;begin    n := 0;    while( n < 1024)    begin        insert into ztest values( n, @#测试字符串B@#, @#测试字符串C@#);        n := n + 1;    end;end;

类似这样的存储过程的实际应用是少有的。它与客户端一次提交单条 SQL 语句没有可比性：当一次只需要向DBMS提交一条新记录，要这个存储过程干什么呢？

原文转自：http://www.ltesting.net