| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using procedures instead of coding update/insert
Kevin, you're missing the point: the first statement was that prepared SQL is much faster then stored PL/SQL procedures. In my opinion, one cannot make such a general statement.
-----Original Message-----
Sent: Tuesday, January 15, 2002 3:41 PM
To: Multiple recipients of list ORACLE-L
Actually, we have many procedures that use the FORALL syntax. And, yes I am the guy that tested the difference between the Java code sending a bulk insert v.s., using PL/SQL FORALL. And Yes, there was a significant (10-25%) improvement when using PL/SQL. The code was as close as we could get it.
I'll agree that the code isn't simple (it was a b*&%#) or concise but we were after performance.
-----Original Message-----
Sent: Tuesday, January 15, 2002 2:05 PM
To: Multiple recipients of list ORACLE-L
They are possible, all right, with forall statement
and/or with passing an array as a parameter. How many
of your procedures do actually use that feature? Are you sure
that the guy who has tested the difference between prepared
SQL and PL/SQL procedure has done the necessary coding in order
to compare apples to apples.
Second, do not confuse array binds with passing an array.
To pass an array, you have to define a type, define parameter
of that type and pass the array as an object type. Not very intuitive
or simple.
-----Original Message-----
Sent: Tuesday, January 15, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
You must be using Oracle 8.0 or earlier. Bulk/array binds are possible in PL/SQL (stored procedures) starting with Oracle 8i
-----Original Message-----
Sent: Tuesday, January 15, 2002 11:40 AM
To: Multiple recipients of list ORACLE-L
The reason for that is the fact that with prepared statements you can do array binds while that isn't possible with the stored procedures. I don't like this kind of comparisons. I judge on case by case basis.
-----Original Message-----
Sent: Tuesday, January 15, 2002 10:55 AM
To: Multiple recipients of list ORACLE-L
We have done some preliminary testing and found prepared statements about 20% faster than stored procedures. We inserted 200,000 records at a time (28-50 columns in a table ) using stored procedures and then used prepared statements for the same dataset. Both of them were called from java using JDBC thin drivers and interestingly found prepared statements faster. Similarly, deletes were also about 15%-20% faster using prepared statements. For some reasons, updates to the same tables gave almost identical performance.
Rakesh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rakesh Gupta
INET: GuptaR_at_telecomsys.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 15 2002 - 15:58:19 CST
 |
 |