A story of stored procedures and SQL statements

Stored Procedure | statement
One of my friends said: "He learned from a book by Mr. Levy, a well-known Taiwanese technology writer, that if you encapsulate SQL statements with stored procedures, you will have a significant increase in system efficiency." He has done experiments!!! I believe my friends have done experiments, although they have not seen them.    But I reckon there was something wrong with his experiment, which not only blinded him, but also blinded Mr Levy (if the contents of his writings were not misunderstood), and even more people.    But I have to come up with evidence to convince people. Later, a specific problem was encountered: the client often inserts records into the database. In Java EE, a Entity Bean home's Create method invocation generally does not use a stored procedure.    A friend immediately disagreed (perhaps because he had time to veto the Java EE) and thought it would be nice if EE could change the "insert record" behavior to a call to a stored procedure. We are arguing again (I am only against the views of my friends, but I am not proposing any of my own, because it is not easy to come to the next conclusion). Finally I had to do the experiment, respectively, on the Oracle 10g and PostgreSQL 8.0.1. The experimental contents are as follows:

A, Table script: Create TABLE ztest (Fielda integer primary key, Fieldb varchar (128), FIELDC varchar (128))

B, the client requests that the DBMS execute the INSERT SQL statement: INSERT INTO ztest values (? 1,? 2,? 3); --? 1,?2,?3 will be replaced at run time with a reasonable value

C, client-invoked stored procedure (JDBC callablestatement Call): Oracle: (Invoke method call Up_add (...),) Create or replace procedure Up_add (Fielda Integer, fieldb varchar, FIELDC varchar) isbegin INSERT INTO ztest values (Fielda, FIELDB, FIELDC);

PostgreSQL: (client Invoke Method 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, Environment: PostgreSQL: Database Server and client program "both on the local" and "Run" Oracle: Independent database server (always have someone to type while testing, should have no effect on machine performance)

Test: Add 1024 consecutive records to the test table in different ways (that is, requesting the DBMS to execute the SQL statement and invoking the DBMS logical equivalence stored procedure)

After repeated tests, the results are as follows: In both PostgreSQL, the test time is 21-24 seconds (the test environment of each result is consistent).

Oracle:8 Times SQL Execution Request (MS) 5422 4750 3875 3812 5672 3531 3484 3547 6 time Stored procedure Call (MS) 4578 4500 6297 4219 454 7 5734 (consistent with the test environment for each result) it can be concluded that stored procedures encapsulate simple SQL statements that are efficient and may be lower.

But many friends did come to the conclusion that the stored procedure is indeed faster than SQL. Why? ----because they write a test stored procedure that is not practical and is not comparable to a "one-time client invocation" of the SQL statement. Oracle Pl/sql is described as follows, this method can add 1024 records to the datasheet, even the network communication is saved. No wonder the performance has "thousand-fold difference"!

Create or Replace procedure Up_add () isdeclare n:integer;begin N: = 0;        while (n < 1024) begin inserts into ztest values (n, ' test string B ', ' Test string C ');    N: = n + 1; End;end;

The actual application of such a stored procedure is rare. It is not comparable to submitting a single SQL statement to the client at once: what do you want the stored procedure to do when you only need to submit a new record to the DBMS?