Re: benchmarking, which statement is faster
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.
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
> --
> Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/
> 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