Re: PL/SQL vs (Embedded SQL & 3GL) performance comparison.
Date: Tue, 28 Sep 1993 23:36:32 GMT
Message-ID: <CE388z.At5_at_ibmpcug.co.uk>
klm_at_atlanta.dg.com writes in article <1993Sep22.131602.11522_at_beau.atlanta.dg.com
>:
>
> Hello fellow Oracle-ites,
>
> We're in the early stages of a large application development project.
> The primary development tools will be Forms 4.0 and Reports 2.0.
> We expect a good bit of the application to be written in some sort
> of procedural code. We've heard rumors that the performance of
> PL/SQL can be *much* slower than imbedded SQL in a 3GL such as
> COBOL or C. Does anybody have a feel for the performance differences
> between PL/SQL and embedded SQL?
>
>
Like most performance questions the answer is that it (PL/SQL) can be either much slower or much faster than a 3GL program, it all depends on what you are trying to do.
The way PL/SQL works is that 'blocks' of PL/SQL execute directly in the database process as opposed to the application process. This means the overhead of passing data to the application doesn't exist and if you have a 'typical' commercial application that reads lots of data and doesn't do very much work around it it can run faster than 3GL and is much easier and faster to develop. :-)
Trouble is that PL/SQL lets you write procedural stuff and treat it as a real programming language rather than just data access/manipulation.. PL/SQL is interpreted, not designed for this and VERY slow compared to compiled 3GL for executing program logic. Therefore if you have lots of program logic and not much data stuff PL/SQL is not for you if performance is more important than developement time. :-(
N.B. a previous reply commented favourably on a large PL/SQL background job that performed quickly. I'm sure that it did but be VERY carefull doing this, PL/SQL lacks sleep commands so it would execute continuously and suck lots of performance out of the database. Wot we do is to run a small 3GL program that monitors the database every so often and if it detects that any background jobs need running it submits some Pl/SQL to do it. 3GL and PL/SQL are not mutually exclusive, they complement one another and together provide a powerfull solution.
Rob Smith - No Disclaimers.
Received on Wed Sep 29 1993 - 00:36:32 CET