Re: PL/SQL versus SQL

Ed Bruce <bruce_at_ha.hac.com> wrote:

>Torben Krogh Jeppesen wrote:
>>
>> 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.
>>
 

>This is isn't testing SQL vs. PL/SQL. It is comparing a builtin function
>against PL/SQL.
 

>Try timing the following PL/SQL:
 

>create or replace procedure Try_PL_SQL is
> Results INTEGER;
>begin
> select sum(x*2 + 1 )
> into Results
> from TRY_X;
 

> DBMS_Output.Put_Line( Results );
>end Try_PL_SQL;
 

>I get the following results:
 

>SQL> select count(*) from try_x;
 

> COUNT(*)
>---------------
> 110001
 

>Elapsed: 00:00:00.37
>SQL> execute try_pl_sql;
>Elapsed: 00:00:04.24
>10100220003
 

>PL/SQL procedure successfully completed.
 

>SQL> select sum(x*2 + 1) from try_x;
 

> SUM(X*2+1)
>---------------
> 10100220003
 

>Elapsed: 00:00:05.47
>SQL>
You are right, and if you are executing the same SQL in a PL/SQL program and in the SQL*Plus environment, I also think the PL/SQL procedure would perform better than SQL*Plus.

However, the original question was concerning the possibility of _scanning_ a 36 million row table in PL/SQL. Scanning to me means looping through the rows.

In that perspective I still believe that it is better to do the consolidation by creating temporary tables or whatever is needed using massive SELECTs.

Torben Jeppesen
Computer consultant, PMP Software, Denmark Email: tkjeppesen_at_vip.cybercity.dk Received on Sat Jun 08 1996 - 00:00:00 CEST