Re: PL/SQL vs. (Embedded SQL & 3GL) performance comparison
Date: 29 Sep 93 13:56:01 +1000
Message-ID: <1993Sep29.135601.1_at_cbr.hhcs.gov.au>
In article <28akgf$m4a_at_falin.cs.uow.edu.au>, Rev Dr Phil Herring <phil_herring_at_info-gw.uow.edu.au> writes:
> 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.
^^^^^^
This is the key word here. If the client and server are on the different machines then PL/SQL probably wins simply because of communication line overheads which are being avoided. If both are on the same machine then a 3GL is usually faster. Either way, its easier coding PL/SQL than Pro*C or Pro*COBOL.
> (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.
Yes, a lot of attention should be paid to SQL formulation whether in SQL*Plus or PL/SQL Stored Procedures.
Aren't stored procedures and PL/SQL also limitted as to what they can return without using intermediate result tables? Whereas a 3GL can format and display the entire database (if that's needed).
>
>
> -- Phil.
> -----------------------------------------------------------------------
> Rev Dr Phil Herring, University of Wollongong, Australia
> Copyright (c) 1993 Phil Herring phil_herring_at_info-gw.uow.edu.au
> ------------------------------------------------------------------------
-- Bruce... pihlab_at_cbr.hhcs.gov.au "If you swallow a live frog first thing in the morning ... Nothing worse will happen to either of you for the rest of the day." ******************************************************************* * Bruce Pihlamae -- Database Administration * * Commonwealth Department of Health, Housing & Community Services * * Canberra, Australia (W) 06-289-7056 * ******************************************************************* * These are my own thoughts and opinions, few that I have. * *******************************************************************Received on Wed Sep 29 1993 - 04:56:01 CET