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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
