Re: PL/SQL vs PROC Performance Issue - Oracle 8.1.6

[Quoted] Our application has to process INSERTS, UPDATEs and DELETEs of transactions that may arrive in some random order.

[Quoted] We wont be able to use 'ARRAYs' all the time to group process transactions and issue a commit later on.

[Quoted] Pro*C application and the 'PL/SQL' script were both run on the same server. They are not using SQLNET connection.

I agree that PL/SQL will be faster, since the PL/SQL code will be compiled on the server and executed in memory (No data traffic), whereas with Pro*C program, Update Transaction and COmmit transaction there will be data traffic using BEQ connection.

But, Pro*C running 50 times slower than PL/SQL is something I am not able to digest. On most other servers (we have quite a few servers running Oracle 7.3.4 ... 8.1.7), the ratio is around 2-3. i.e. Pro*C is 2-3 times slower than PL/SQL.

So the Question is
[Quoted] 1) Do we need to recompile the Pro*C program with different optimization switches ?
[Quoted] 2) Tune the database so that Pro*C programs understand to make use of the available CPU power and free memory to get the work done faster .

The test box is 12 Processor box with 40 GB of memory and is 99% idle when these tests are run.

"Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> wrote in message news:3b4c4b30.816444_at_news-server...
> On Tue, 10 Jul 2001 13:47:15 -0400, "uunet" <r_at_b.com> wrote:
>
> Most likely your C program is executing in a client and using Net8 to
> connect to the server and send 1 row at a time. Use arrays and it will
> catch up in speed with PL/SQL.
>
> Also, this is terribly slow:
> >
> > for (empno=1; empno<=num_rec; empno++)
> > {
> > EXEC SQL EXECUTE S USING :empno;
> > EXEC SQL COMMIT;
> > }
>
> COMMIT after every 10 rows and you'll notice a very large performance
> improvement. Goes for PL/SQL too.
>
> Cheers
> Nuno Souto
> nsouto_at_bigpond.net.au.nospam
> http://www.users.bigpond.net.au/the_Den/index.html
Received on Sat Jul 21 2001 - 23:34:20 CEST