Re: Are stored procedures faster than straight SQL?

Jerry Gitomer wrote:
>
> Hi Tom,
>
> I was under the impression that once a dynamic sql query was parsed it
> was held in the SGA and was not parsed again unless the SGA was flushed or,
> based on some type of least recently used algorithm, the query was flushed.
> If that is the case the only difference in performance would be on the first
> execution of the query.
>
> This raises another issue -- is the time savings significant enough to
> matter?
>
> regards
>
> Jerry
>
> tomscott_at_nospam.abac.com wrote in message
> <3623695d.89139016_at_news1.abac.com>...
> >> 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?
> >>
> >>Much appreciated,
> >>Sean Dolan
> >
> >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.

All SQL statement cursors are saved in the SQL utilizing an LRU algorithm, dependent upon a number of Oracle startup parameters such as cursor_space_for_time (true/false), etc. However, if the statement is utilizing literal data and not placeholders, then the chances of the cursor being reused are small. For example:

	select * from foo where colx = 'abc';
	select * from foo where colx = 'xyz';

Each generates a separate and distinct cursor. However,

        select * from foo where colx = :colx;

combined with binding 'abc' to the :colx placeholder and execute, then bind 'xyz' to the :colx placeholder and execute, will utilize only one cursor, avoiding the reparsing required. Note, if you application is persistent in that it stays around between calls to these functions, then you can cache the parsed cursor in your code, which will force the database to keep the cursor around since it has not been closed. This is an aggressive method, however. You will need to configure your database to allow as many cursors as you will ever have cached or opened by all applications. Use of this technique along with multi-row fetches/inserts has allowed us to enjoy an approximate 8x improvement in database performance and resourse (CPU) utilization in our large-scale distributed manufacturing execution system, FACTORYworks, over more normal cursor, fetch, insert methods.

Bill Boyle
Principal Software Engineer
FASTech Integration, Inc. (now a subsidiary of Brooks Automation) Received on Sat Oct 24 1998 - 22:55:33 CDT