Re: benchmarking, which statement is faster

From: Daniel A. Morgan <Daniel.Morgan_at_attws.com>
Date: Wed, 25 Jul 2001 16:26:39 -0700
Message-ID: <3B5F55AF.40F275AA_at_attws.com>


Thomas Kyte wrote:

> In article <3B5F1DB7.42F4A196_at_attws.com>, "Daniel says...
> >
> >Thomas Kyte wrote:
> >
> >> In article <3B5DD1ED.F0C996D2_at_attws.com>, "Daniel says...
> >> >
> >> >DS wrote:
> >> >
> >> >> hi all,
> >> >>
> >> >> has anybody benchmarked or has any idea which of the
> >> >> following statements will execute faster?
> >> >>
> >> >
> >> >Explicit cursors are preferable to implicit cursors. If not the first
> >> >time they execute, thereafter. In addition they also solve space
> >> >problems with temp and rollback by allowing incremental commits.
> >> >
> >>
> >> explicit cursors in PLSQL rot.
> >>
> >> They are slower.
> >> They are harder to code.
> >> There is NO performace benefit to them the first time, the second time or any
> >> time.
> >>
> >>If an insert into is to be a single transaction (as they almost always are), you
> >> need to have sufficient rollback configured. Incremental commits are a time
> >> waster and introduce bugs (how many people make their "incremental" things
> >>"restartable" -- they always seem to forget that. When they blow up, they are
> >> left with a mess)
> >>
> >> Temp space -- well, you would need EXACTLY the same amount for an explicit
> >> cursor as you would an implicit -- there would be no difference there (if we
> >> gotta sort one of them, we gotta sort the other).
> >>
> >> INSER INTO SELECT * FROM
> >>
> >> Over
> >>
> >> a cursor
> >>
> >> wins anytime.....
> >>
> >> see
> >>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1017644816229
> >> for more details
> >>
> >> >Daniel A. Morgan
> >> >
> >>
> >> --
> >> 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
> >
> >Beg to disagree here Tom. The day I find a developer at any company where I
> >consult
> >using anything other than explicit for pulling records in a loop they will not
> >longer be on the project. And the other DBAs in my group agree.
> >
> >Daniel A. Morgan
> >

>

> 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.
>

> --
> 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

[Quoted] [Quoted] 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 Received on Thu Jul 26 2001 - 01:26:39 CEST

Original text of this message