Re: PL/SQL versus SQL
Date: 1996/06/08
Message-ID: <4pcmhq$4ib_at_vip.cybercity.dk>#1/1
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