Re: Poor stored procedure performance using varchar2 parameters

Also in OCI you can pass an array of input values. Lets say you want to call the stored proc 5,000 times. You could call it 50 times with an array interface of 100 elements each time. That would save on network traffic and be more efficient.
Also you should use bind variables and just rebind and oexec. Much faster on both ends, server and workstation. Also uses a lot less CPU on the server.
Jim
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3BBB9302.15E3_at_yahoo.com...
> Bozo wrote:
> >
> > Does anyone have an explanation for this:
> >
> > Our application generates A LOT of data and can continuously call a
 stored
> > procedure to insert data into our Oracle 8.1.5 database (Win2000) as
 fast as
> > the database can handle it. It can make around 500 stored procedure
 calls
> > per second when using SQL Server 2000, but we only get around 100 calls
 per
> > second using Oracle, even if we turn off commits.
> >
> > Our code to execute the stored procedure uses OCI and essentially makes
 3
> > calls:
> >
> > oparse() to parse a PL/SQL block that looks like:
> > BEGIN ProcName(1,'ABC',:ret1,:ret2); END;
> > oexec() to execute the parsed statement
> > ocom() to commit (we've disabled this and it doesn't help performance)
> >
> > After some research, we determined the real bottleneck is not the
 oparse()
> > or oexec() call, but whether the stored procedure uses varchar2
 parameters.
> > We changed our stored procedure to do practically nothing and it looks
 like:
> >
> > CREATE PROCEDURE ProcName
> > (param1 number,
> > param2 varchar2,
> > param3 OUT number,
> > param4 OUT number
> > )
> > IS
> > begin
> > update table set keycolumn=param1, textcolumn='FIXEDTEXT'
> > where keycolumn=param1;
> >
> > param3 := 1;
> > param4 := 0;
> > end;
> >
> > With this change (and removing commit calls to ocom()), we still don't
 get
> > more than 150-200 S/P calls per second.
> >
> > HOWEVER.... if we remove the 'param2 varchar' declaration (or change it
 to a
> > 'param2 number'), then performance increases up to around 750-900 S/P
 calls
> > per second!! We're not even USING the varchar2 parameter, yet the
> > declaration for it seems to drastically impact performance.
> >
> > We're not using packages or user-defined datatypes, just tables and
 stored
> > procedures, Oracle 8.1.5 for NT, and OCI. We haven't tried bulk loads
 using
> > arrays, but that's next.
> >
> > Any explanation for this, or recommendations to increase performance? I
> > can't believe that SQL Server can outperform Oracle by a 3:1 margin!
> >
> > Thanks,
> > Doug Piercy (songbird_at_pacifier.com)

>

>

>

>