存儲過程與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提交一條新記錄，要這個存儲過程干什么呢？
