Re: Are stored procedures faster than straight SQL?

From: Jeremiah Wilton <jeremiah_at_wolfenet.com>
Date: Tue, 13 Oct 1998 10:11:09 -0700
Message-ID: <Pine.OSF.3.95.981013100947.22528H-100000@gonzo.wolfenet.com>

> Someone wrote:
> >
> > I am "but a newbie" to the world of Oracle and am wondering if Stored
> > Procedures are faster than sending a straight line SQL command to
> > query an Oracle 7.3.4 database and why?

On Tue, 13 Oct 1998 tomscott_at_nospam.abac.com wrote:
>
> In general, yes. A stored procedure is already compiled and parsed,
> whereas a dynamic query needs to go through that process. However, for
> a small, simple query, that might only take milliseconds. The
> difference really comes into play when, for example, you have to call
> the query over and over again from within a loop.

If you are executing the same SQL statement quite often, it should already be resident within the shared pool, and therefore should not have to be parsed again. That brings up an interesting question: If you use SQL efficiently, using bind variables where appropriate, and reuse SQL statements, is it still more efficient to use stored procedures and packages? If so, why?

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Tue Oct 13 1998 - 12:11:09 CDT