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
Received on Fri Sep 26 1997 - 00:00:00 CDT
