Re: PL/SQL versus SQL

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> Received on Thu Jun 06 1996 - 00:00:00 CEST