Re: benchmarking, which statement is faster

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 26 Jul 2001 06:37:51 -0700
Message-ID: <9jp6ff01657_at_drn.newsguy.com>


In article <3B5F55AF.40F275AA_at_attws.com>, "Daniel says...
>
>Thomas Kyte wrote:
>
>>
>> Everyone is free to disagree, doesn't make them right. Even if they have a
>> quorum.
>>
>> however, if you read:
>>
>>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1017644816229
>>
>> It conclusively shows that
>>
>> insert into select * from
>>
>> beats (kills, outperforms, etc)
>>
>> loop
>> fetch
>> insert
>> end
>>
>>It makes sense. Why in the heck would you even consider pulling the records back
>> to PLSQL only to send them back to the database.
>>
>> If you read:
>>
>>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1544606261686
>>
>> it conclusively shows that SELECT ... INTO from t is faster, better, cheaper,
>> easier, more readable then
>>
>> open
>> fetch
>> close
>>
>>Until and unless you can post an example of any case where explicit cursors beat
>> an implicit cursor.... I am suspicious of your conclusion.
>>
>> What you believe to be true is fine.
>>
>> What is truly true and real -- thats a different matter all together.
>>
>> What is the basis in reality and fact for your conclusion? Where is the test
>> case, the sample, the empirical proof?
>>
>> I myself would take the developer that demands on using explicit cursors,
>>educate them as to the power and beauty of the implicit cursor -- and only upon
>> finding them ignoring fact remove them.
>>
 

>Then by all means please explain to me the beauty of the second query using an
>implicit cursor over the former with an explicit cursor.
>
>DECLARE
>
>CURSOR my_cur is
> SELECT user
> FROM dual;
>
>my_rec my_cur%ROWTYPE;
>
>i INTEGER;
>x VARCHAR2(30);
>
>BEGIN
> OPEN my_cur;
> FOR i in 1..1000000
> LOOP
> FETCH my_cur INTO my_rec;
> x := my_rec.user || 'A';
> END LOOP;
>END;
>/
>
>Elapsed: 00:00:03.04
>========================================
>DECLARE
>
>i INTEGER;
>x VARCHAR2(30);
>
>BEGIN
> FOR i in 1..1000000
> LOOP
> SELECT user
> INTO x
> FROM dual;
> x := x || 'A';
> END LOOP;
>END;
>/
>
>Elapsed: 00:01:94.35
>
>I hope to learn something.
>
>Of course I'd also like to learn what Elapsed: 00:01:94.35 means too.
>
>Daniel A. Morgan
>

you are comparing apples with toaster ovens (working code with a select into against buggy code with an explicit cursor)

Every fetch after the first is getting NO DATA FOUND. So, they are wholly and totally different. You are never re-executing the query. You are never getting any data. An explicit cursor in PLSQL must be explicity closed in order to "restart" it. The implicit select into -- well that does all of the work for you.

You must, you have to, compare:

for i in 1 .. 1000000
loop

   open my_cur;
   fetch my_cur into my_rec;
   close my_cur;
end loop;

to

for i in 1 .. 1000000
loop

   select user into x from dual;
end loop;

PLSQL realizes the cursor is empty and doesn't do anything. The fetch inside your loop is a huge NO-OP. It doesn't even do a fetch for real (you would get fetch out of sequence if it did).

The link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1544606261686

has quite a few empirical examples showing the proper method of comparison.

(also, why are you declaring i -- loop variables are defined for you)...

Here is your invalid block of code (that doesn't do any fetches) compared to select into, compared to the proper explicit piece of code (well, its ALMOST proper, to really be a select into it should be:

  loop

     open c1
     fetch c1
     if (not c1%found ) then raise no_data_found; end if;
     fetch c1
     if ( c1%found ) then raise too_many_rows; end if;
     close c1;

  end loop;

but that would take even longer)

ops$tkyte_at_ORA8I.WORLD> DECLARE

  2     CURSOR my_cur is
  3     SELECT user
  4     FROM dual Q1;
  5     my_rec   my_cur%ROWTYPE;
  6     i   INTEGER;
  7     x   VARCHAR2(30);
  8  BEGIN
  9     OPEN my_cur;
 10     FOR i in 1..1000000
 11     LOOP
 12        FETCH my_cur INTO my_rec;
 13        x := my_rec.user || 'A';
 14     END LOOP;

 15 END;
 16 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.10

ops$tkyte_at_ORA8I.WORLD> DECLARE
  2 i INTEGER;
  3 x VARCHAR2(30);
  4 BEGIN

  5     FOR i in 1..1000000
  6     LOOP
  7        SELECT user
  8        INTO x
  9        FROM dual Q2;
 10        x := x || 'A';
 11     END LOOP;

 12 END;
 13 /

PL/SQL procedure successfully completed.

Elapsed: 00:02:49.56

ops$tkyte_at_ORA8I.WORLD> DECLARE

  2     CURSOR my_cur is
  3     SELECT user
  4     FROM dual Q3;
  5     my_rec   my_cur%ROWTYPE;
  6     i   INTEGER;
  7     x   VARCHAR2(30);
  8  BEGIN
  9     FOR i in 1..1000000
 10     LOOP
 11        OPEN my_cur;
 12        FETCH my_cur INTO my_rec;
 13        CLOSE my_cur;
 14        x := my_rec.user || 'A';
 15     END LOOP;

 16 END;
 17 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:32.91

There again, nominally showing the select into being more efficient not only codewise but in performance as well.

Just to show you the difference, I also ran:

ops$tkyte_at_ORA8I.WORLD> alter session set sql_trace=true; Session altered.

ops$tkyte_at_ORA8I.WORLD> DECLARE

  2     CURSOR my_cur is
  3     SELECT user
  4     FROM dual Q1;
  5     my_rec   my_cur%ROWTYPE;
  6     i   INTEGER;
  7     x   VARCHAR2(30);
  8  BEGIN
  9     OPEN my_cur;
 10     FOR i in 1..100
 11     LOOP
 12        FETCH my_cur INTO my_rec;
 13        x := my_rec.user || 'A';
 14     END LOOP;

 15 END;
 16 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

ops$tkyte_at_ORA8I.WORLD> DECLARE

  2     CURSOR my_cur is
  3     SELECT user
  4     FROM dual Q3;
  5     my_rec   my_cur%ROWTYPE;
  6     i   INTEGER;
  7     x   VARCHAR2(30);
  8  BEGIN
  9     FOR i in 1..100
 10     LOOP
 11        OPEN my_cur;
 12        FETCH my_cur INTO my_rec;
 13        CLOSE my_cur;
 14        x := my_rec.user || 'A';
 15     END LOOP;

 16 END;
 17 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06  

The TKPROF report conclusively shows:

SELECT USER
FROM
 DUAL Q1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          1          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          1          4           1

SELECT USER
FROM
 DUAL Q3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.01       0.00          0          0          0           0
Fetch      100      0.01       0.00          0        100        400         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.02       0.00          0        100        400         100

that the first query (Q1) was fetched from 2 times (out of 100 iterations). PLSQL optimized away the calls to the database engine since it knew the cursor was exhausted (and must be closed before any fetch would return any data)

that second query (Q3) on the other hand -- it fetched 100 times.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Thu Jul 26 2001 - 15:37:51 CEST

Original text of this message