RE: Using procedures instead of coding update/insert

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
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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 <ORACLE-L_at_fatcity.com>
> @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: 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gogala, Mladen
> INET: MGogala_at_oxhp.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).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: tday6_at_csc.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.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).