Re: Are stored procedures faster than straight SQL?

tomscott_at_nospam.abac.com 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?
> >
> >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.

However, we get similar performance improvements (5-8x) by caching frequently used cursors. Just rebind the variable data using placeholders and re-execute the cursor. We do this with the OCI library in our C++ transaction processing framework. MAJOR performance improvements, event for dynamic SQL. Stored procedures have their place, but not if you are using multiple database products, such as Oracle, Informix, Sybase, etc.

-Bill Boyle Received on Sat Oct 24 1998 - 22:45:03 CDT