Re: PL/SQL vs. (Embedded SQL & 3GL) performance comparison

In article <749011792snx_at_kbigate.stgt.sub.org> Willy Klotz, willyk_at_kbigate.stgt.sub.org writes:
>With a 3GL program, you _compile_ your program (and you preprocess the
>SQL statements). In PL/SQL, there is no compiler; the SQL-statements
>are processed every time such a script is run.

(The following remarks apply to the current release of Oracle 7.)

There is a PL/SQL compiler; it generates P-code for stored procedures and functions. These will only be recompiled when changes to dependent objects make it necessary. The only procedures that are compiled whenever they're called are anonymous blocks in application programs, and database triggers. These will be compiled immediately before each invocation.

All SQL statements are parsed and optimised once; anyone who executes the same SQL statement subsequently will use the execution plan from the previous execution.

To return to the original question: in some cases, using PL/SQL will be faster than a 3GL because the resulting code will execute in the server, with far fewer accesses to the RDBMS required of your application. (The will be particularly likely if your applications are using SQL*Net, and if you're using packages and stored procedures.) However, too many people use PL/SQL as an excuse to avoid writing a complex (but often faster) SQL statement. Avoid cursors if you can, treat it as a way of moving code out of your application into the RDBMS, and it will *probably* work well for you. YMMV.

• Phil.

  ---

  Rev Dr Phil Herring, University of Wollongong, Australia Copyright (c) 1993 Phil Herring phil_herring_at_info-gw.uow.edu.au

  ---