Or, you could do what we do in once case (8.1.6 database)....make 1000 procedure calls that populates a package index-by table. After 1000 calls, it internally does another call that does the bulk insert. The first 999 calls take about 1 second.
-----Original Message----- Sent: Tuesday, January 15, 2002 2:00 PM To: Multiple recipients of list ORACLE-L That is correct, but, unfortunately, in most cases scalars are passed as parameters. That option is available only as of 8.1.7 when you can bind a 3GL array or a Java array to PL/SQL array. -----Original Message----- Sent: Tuesday, January 15, 2002 1:46 PM To: Multiple recipients of list ORACLE-L Nothing prohibits from using arrays as parameters, when calling stored procedures. So, there goes the advantage of 'prepared statements' in doing array binds. Igor Neyman, OCP DBA [EMAIL PROTECTED] ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 12:17 PM > So what is the basis for the case by case judgement. I'm not being flip - > I really want to know. > > From the discussion so far it appears that the pros for PL/SQL procedures > are: > > Uniform access method to the database for all applications > Processing done on the more robust server machine > Less impact on the SGA > Ease of maintenance > "Loosely couples" the application to the database in that database > changes only impact the procedures, not the code > > The pros for prepared statements is that you can do array binds (which give > better performance). > > Anything else? I'll be the first to admit that PL/SQL development is my > short suit. > > > > > "Gogala, > Mladen" To: Multiple recipients of list ORACLE-L > <MGogala <[EMAIL PROTECTED]> > @oxhp.com> cc: > Sent by: root Subject: RE: Using procedures instead of > coding update/insert > > 01/15/2002 > 11:40 AM > Please > respond to > 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: > 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: Igor Neyman 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).