Re: Pro*C and stored procedure preformance

nickjost_at_ix.netcom.com (Nick Jost ) wrote:
>Currently I have sql statements in a Pro*C program I am working on.
>One of my co-workers suggested that I use stored procedures to increase
>preformance. My question is, does it?
>
>I figured that the SQL, once the Pro*C was compiled, was in it's
>tightest form hence a function call would only slow things down. Has
>anyone done trials on this showing a net difference one way or the
>other?
>
>Nicholas Jost
>Programmer/Analyst RxNet

This is a very reasonable recommendation if you are doing client/server over a slow network:

1. If you code multiple SQL statements, every one of those will be sent to the server (through the narrow "pipe")
2. If you "cluster" those SQL statements into a PL/SQL block, only the block will be sent to the server.
3. If that PL/SQL block is already stored in the server, only a call to it will be sent.

Try it over a 64Kb line, you'll see what I mean.

To be perfectly honest, well written Pro*C programs (using array processing, deferred parsing) might not benefit much from PL/SQL. But it still makes sense to store into the server code that you would send to it anyway.

Thanks,
Pedro Received on Sun Aug 06 1995 - 00:00:00 CEST