One of my friends said: He learned from a book from Mr. Li Wei, a well-known technical writer in Taiwan, if you encapsulate SQL statements with stored procedures, system efficiency will have great improvements. He did experiment!!! - I believe that friends have done experiments, although they see it. However, I estimate that there is a problem with his experiment. The same experiment not only blinds him, but also blinds Mr. Li Wei (if his content is not misunderstood), even more people. However, I have to take evidence to convince people. Later encountered a specific problem: the client often inserts records to the database. In J2EE, a CREATE method call for an Entity Bean Home is generally useless. My friend immediately held an objection to the opinion (may be because he temporarily had a rejection J2EE), and it was considered that J2EE can change "insertion record" such as the call to the stored procedure. We have happened again (I just opposed my friends, but I didn't make any opinion, because the next conclusion is not easy). Finally, I didn't have experiments, respectively on Oracle 10g and PostgreSQL 8.0.1. The experimental content is as follows:
A, build table script: Create Table ZTest (Fielda Integer Primary Key, Fieldb Varchar (128), Fieldc Varchar (128))
B, the client requests the INSERT SQL statement executed by the client: INSERT INTO ZTEST VALUES (? 1,? 2,? 3); -? 1,? 2, 3 will replace it at reasonable value at runtime
C, the client calls (JDBC CallableStatement Call): Oracle: (Calling Way Call up_add (...),) Create or Replace Procedure Up_Add (Fielda Integer, Fieldb Varchar, Fieldc Varchar) Isbegin Insert Intest Values (Fielda , FIELDB, FIELDC); END;
PostgreSQL: (Client calling 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" "Oracle: Independent Database Server (Someone always typing slowly when testing, should have no effect on machine performance)
Test: Different ways (ie, request DBMS executes SQL statement and calling DBMS logic equivalent) to join 1024 records in a test table
After repeated testing, the results were as follows PostgreSQL: two ways, the test time is between 21- 24 Seconds (consistent with the test environment of each result)
Oracle: 8 SQL Execution Request Duration (MS) 5422 4750 3875 3812 5672 3531 3484 3547 6 Time Storage Procedure Calls Supile (MS) 4578 4500 6297 4219 4547 5734 (MIS) This can be seen, stored The process package is simple SQL statement, the efficiency is quite, and it may be lower.
But many friends have concluded that the stored procedure is indeed faster than SQL. why? ---- Because they have written a non-practical significance while testing, it is also a test stored procedure with the "primary client call" of the SQL statement. The Oracle PL / SQL description method is as follows, and the method can add 1024 records to the data sheet once, and even the network communication is saved. We can have "thousands of differences"! Create or replace procedure up_add () isDeclare N: integer; begin N: = 0; While (n <1024) Begin Insert INTO ZTEST VALUES (N, 'Test String B', 'Test String C '); N: = N 1; End;
Actual application similar to such a stored procedure is less. It is not comparable to the client, submitting a single SQL statement: When you only need to submit a new record to DBMS, what is the storage process?
