存储过程与SQL语句的一个故事
2024-07-21 02:11:19
供稿：网友
我的一位朋友说：他从台湾知名技术作家李维先生的一本书中获悉，如果用存储过程封装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提交一条新记录，要这个存储过程干什么呢？
