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

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 Wed Jul 25 2001 - 18:26:39 CDT