Re: ProC vs. Stored Procedure

From: Phil Herring <revdoc_at_uow.edu.au>
Date: 1997/09/24
Message-ID: <60a5qr$c7e$1@wyrm.its.uow.edu.au>#1/1

In article <01bcc82d$be611c10$64646464_at_GROSZKO> M. Thomas Groszko, mgroszko_at_sirus.com writes:
>I have been told that there is no advantage to using a stored procedure vs.
>coding SQL directly in my C++ application. The source says that Oracle will
>only compile the SQL the first time it sees it. Is this true or will I get
>better performance from a stored procedure? Why? What Oracle documentation
>should I review?

It depends. Stored code won't make a difference if you're talking pure SQL, but it might if you would have a substantial procedural component in your processing.

This is because procedural code written in PL/SQL is slower than procedural code compiled to machine code, so in some cases (such as applications that run on the same machine as the RDBMS), it might be faster to code the procedural stuff in C, C++, or whatever.

However, in most client-server applications, server-side code is to be preferred over client-side, especially if you're fetching a lot of data in loops and executing more fetches inside those loops. In such cases, the penalties imposed by network traffic (both in terms of sending large SQL statements to the server, and fetching data back) will usually far exceed the benefits of doing the processing in machine code on the local machine.

Those issues aside, using stored procedures is generally A Good Thing because it makes it easier to reuse database code. This has two results: first, it reduces the amount of work that you have to do. Second, it also reduces the possibility that the same SQL statement will be used several times with slight differences. In such cases, Oracle will see those statements as several different statements and parse each and every one separately, degrading performance. (Even differences in whitespace and case within the statement will cause this to happen.)

Caveat implementor! Test in your environment before making a decision.

Received on Wed Sep 24 1997 - 00:00:00 CDT