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

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

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

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
1) Do we need to recompile the Pro*C program with different optimization switches ?
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 - 16:34:20 CDT