Re: PL/SQL versus SQL

nkatwala_at_aol.com (NKatwala) wrote:

>Everybody,
 

>Does anyone have any performance benchmarks comparing a PL/SQL program
>versus a SQL program? Can someone point us to some white papers or
>performance discussions between PL/SQL and SQL performance? Or some book?
 

>If I am scanning a 36 million row table to create a consolidated 2.2
>million row table, will a properly constructed PL/SQL program give me the
>same performance as a SQL program? How much difference will be there in
>performance?
 

>The temporary space is not a problem.
 

>- Niraj Katwala

PL/SQL is NOT compiled code but SQL is. In SQL you pay a startup "fee" for getting the SQL-statement parsed and optimized. The PL/SQL statement will not be optimized, and I suspect it to be interpreted at the text level all the time. You can try it out by writing af function like this:

FUNCTION DOUBLE (X IN NUMBER) RETURN NUMBER IS BEGIN
    RETURN 2*X ;
END ; Then you create at table like this

CREATE TABLE TRY_X
( X NUMBER) ; and fill it with, say 10000 rows.

Try timing SELECT SUM(DOUBLE(X)+1) FROM TRY_X ; and SELECT SUM(X*2 + 1) FROM TRY_X;

and you will see a significant difference (1:10) against PL/SQL.

Try and describe your consolidation problem. I have some efficient SQL in my toolbox.

Torben Jeppesen.

Computer consultant, PMP Software, Denmark Email: tkjeppesen_at_vip.cybercity.dk Received on Thu May 30 1996 - 00:00:00 CEST