Re: ProC vs. Stored Procedure

Dan Clamage wrote:
>
> Gerard H. Pille <ghp_at_skynet.be> wrote in article
> <01bcc861$4e8bd0e0$df1beec3_at_pcghp>...
> > Coding in your application will be more performant, Oracle won't have to
> > look it up.
> > (ghp_at_santens.be; ghp_at_skynet.be)
> > M. Thomas Groszko <mgroszko_at_sirus.com> schreef in artikel
> > <01bcc82d$be611c10$64646464_at_GROSZKO>...
> > > 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?
> I've run performance tests between running an embedded SQL in a COBOL
> module vs. an embedded PL/SQL block in COBOL that calls a stored procedure
> (actually in a package). Incidentally, the stored procedure executed
> dyunamic SQL using DBMS_SQL.
> The first run of the embedded PL/SQL took a little longer than the embedded
> SQL version. But on subsequent runs, the embedded PL/SQL was significantly
> faster (because the compiled package was now in memory). Over 100-800
> iterations, the embedded PL/SQL ran 600 - 800% *faster* than the embedded
> SQL. Using dynamic SQL, I was able to squeak out additional performance by
> tracking and reusing the dynamic cursors (which are different than regular
> cursors). Additionally, the embedded PL/SQL block resulted in a much
> smaller COBOL module with better error handling (since the exceptions are
> handled server-side), and the stored procedure is shareable (reentrant)
> among users -- critical with a couple hundred users online. Also, the
> stored procedure is more maintainable than the embedded SQL version; I can
> tune it and have a stored procedure that *any* application can make use of,
> whether it's Delphi, Developer/2000, Pro*C, VB, etc.
> I'm a strong advocate of using stored procedures with 3GL applications, as
> you can see.
>
> - Dan Clamage dclamage_at_idcomm.com

I agree with Dan, if your business/application processes can be supported with stored procedures, you have essentially a more portable system than is usually achieved by imbedding the database/RI/process logic in your external programmatic environment. In my opinion this is a great benefit.

Cheers,
TRG Received on Fri Sep 26 1997 - 00:00:00 CDT