Home » SQL & PL/SQL » SQL & PL/SQL » Which is better? For loop or Using cursor
Which is better? For loop or Using cursor [message #343101] Tue, 26 August 2008 05:03 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi,

I have a simple task which I can do it in two ways like

1) Using For loop

declare
v_AccNum varchar2(40);
begin
for c1 in (select account_num from account)
loop
v_AccNum := c1.account_num;
dbms_output.put_line(v_AccNum);
--<some business logic on this v_AccNum here>
end loop;
end;

2) Using Cursor
declare
v_AccNum varchar2(40);
cursor c1 is select account_num from account;
begin
open c1;
loop
fetch c1 into v_AccNum;
dbms_output.put_line(v_AccNum);
--<some business logic on this v_AccNum here>
end loop;
close c1;
end;

Can somebody please suggest me which one is better for this task in terms of performance, memory usage, etc. Ofcourse using cursor we can do lot many things like passing arguments, get the count of number of rows, etc but for this type of task which is the better one?

Thanks in advance,
prashas_d.

[Updated on: Tue, 26 August 2008 05:13]

Report message to a moderator

Re: Which is better? For loop or Using cursor [message #343105 is a reply to message #343101] Tue, 26 August 2008 05:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best solution is to do it in a single SQL statement,
Failing, that, do it in a series of SQL statements.
Failing that, do row-by-row processing like you're doing.

Once you're down to row by row processing, go with a
FOR <record> IN <cursor> LOOP
...
END LOOP;
structure. It doesn't matter whether the cursor is defined in the declare, or included in the LOOP statement.

Here's some timings to show why this is the better option:
create table test_0068(col_1  number, col_2 number);

insert into test_0068(col_1,col_2) (select level,0 from dual connect by level <= 100);

declare
  v_time    pls_integer;
  v_var1    number;
  v_var2    number;
  v_iter    pls_integer  := 1000;
  
  cursor c_1 is select col_1,col_2 from test_0068;
begin
  
  v_time := dbms_utility.get_time;
  for i in 1.. v_iter loop
    open c_1;
    loop
      fetch c_1 into v_var1,v_var2;
      exit when c_1%notfound;
      null;
    end loop;
    close c_1;
  
  end loop;
  
  dbms_output.put_line('Method 1 '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  for i in 1.. v_iter loop
    for rec in c_1 loop
      null;
    end loop;
  end loop;
  
  dbms_output.put_line('Method 2 '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  for i in 1.. v_iter loop
    for rec in (select col_1,col_2 from test_0068) loop
      null;
    end loop;
  end loop;
  
  dbms_output.put_line('Method 3 '||to_char(dbms_utility.get_time - v_time));
end;
/

Method 1 253
Method 2 17
Method 3 17

Re: Which is better? For loop or Using cursor [message #343107 is a reply to message #343101] Tue, 26 August 2008 05:21 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Jrowbottom has provided a good reply.



Regards,
Oli

[Updated on: Tue, 26 August 2008 05:22]

Report message to a moderator

Re: Which is better? For loop or Using cursor [message #343108 is a reply to message #343105] Tue, 26 August 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Theorically all methods should be the same but starting with 10g Oracle introduced an implicit internal bulk collect on select in cursor loop.
 42  /
Method 1 164
Method 2 12
Method 3 11

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 10.2.0.4.0

 42  /
Method 1 270
Method 2 258
Method 3 269

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 9.2.0.8.0

Regards
Michel

Re: Which is better? For loop or Using cursor [message #343190 is a reply to message #343101] Tue, 26 August 2008 10:12 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Regarding the two flavours of Cursor FOR loop, I generally prefer the inline one simply because that brings the SQL statement next to the loop processing code, making it easier to understand what's going on when you read the code. On the other hand, it means you can't refer to cursorname%ROWCOUNT (because the cursor, though still there, has no name). It also assumes you won't want to re-use the cursor elsewhere in your code, although in practice you very rarely need to do this.
Re: Which is better? For loop or Using cursor [message #343303 is a reply to message #343108] Tue, 26 August 2008 21:23 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Guys, I find this to be a very fascinating post;something I had not given much thought to - but from what you just taught me,
I have some inspired curiosity.

First my jaw almost dropped off when I saw the performance difference before reading Michel's statement about 10g
doing an implicit bulk collect.

On the topic of straight SQL versus cursors, I had thought Cursors were more efficient.

I've viewed any SQL statement, effectively, as all being a cursor to Oracle where a singleton SELECT carries the overhead of Oracle having to do an "extra fetch" to account for the
TOO_MANY_ROWS exception.

But my main intrigue here is with the 10g implicit Bulk Collection.

Does Oracle 10g, in the implicit bulk collect, keep a read-consistent image of the data?

I thought one of the main processing advantages of the Bulk Collect was that Oracle did not have to maintain a read consistent image; which, could be a double-edged sword
if the source table is being updated concurrently.

Second question to the masters - would their be any difference
(9i or 10g) in how the queries are "hashed" for potential re-use
(skipping hard parsing)?

I had read that the SQL engine was less forgiving in its Ascii hash algorithm than the PL/SQL engine but did not know if within PL/SQL there was a difference in those regards between the implicit and explicit cursors when they are stored.

My two cents on the base topic is that with the implicit FOR C1 in ... approach, the scope of C1 is gone outside of
current block that contains it.

I am not sure about 10g, but I recall an out-of-scope error when I tried displaying data from the implicit record in an outer EXCEPTION block.

Since then, when using the implicit approach I always declare the variable upfront that I will use in the FOR ...

Oh - final question for the wizard, JR Smile - could you explain
what this majic is ? :

Quote:


(select level,0 from dual connect by level <= 100);




As always, thank you all for sharing your vast knowledge!

Regards,
Harry

Re: Which is better? For loop or Using cursor [message #343336 is a reply to message #343303] Wed, 27 August 2008 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does Oracle 10g, in the implicit bulk collect, keep a read-consistent image of the data?

Of course, read consistency and bulk collect are 2 differents with no relation at all. Bulk collect just removes context switching between SQL and PL/SQL engines, just like array fetching in other language removes clinet/server roundtrips.
See discussion starting there http://www.orafaq.com/forum/mv/msg/123671/342548/102589/#msg_342548

Quote:
I thought one of the main processing advantages of the Bulk Collect was that Oracle did not have to maintain a read consistent image; which, could be a double-edged sword
if the source table is being updated concurrently.

And you were wrong. There is no connection between the two.

I don't understnad what is your point in second question but a cursor for loop open the cursor, fetch the cursor and close the cursor (even if you go outside the for loop before fetching all the rows).

Quote:
what this majic is ?

What don't you execute it? It just generates 100 rows (search for row generator topic in General forum.

Regards
Michel
Re: Which is better? For loop or Using cursor [message #343358 is a reply to message #343303] Wed, 27 August 2008 00:54 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
harrysmall3 wrote on Wed, 27 August 2008 03:23
On the topic of straight SQL versus cursors, I had thought Cursors were more efficient.

I've viewed any SQL statement, effectively, as all being a cursor to Oracle where a singleton SELECT carries the overhead of Oracle having to do an "extra fetch" to account for the TOO_MANY_ROWS exception.

No, straight SQL is more efficient, and the "extra fetch" thing is a myth. At least, it might once have had some truth in Forms 3.0 on Oracle 6 around 1990, with issues around client-side PL/SQL making additional network trips to the server. However, all cursors pre-fetch anyway (there was a thread about this on AskTom) and there is no difference.

Good point about the implicitly declared record in the Cursor FOR loop only existing within its loop - worth bearing in mind if there is a possibility that you might want to do something else with that record.
Re: Which is better? For loop or Using cursor [message #343386 is a reply to message #343358] Wed, 27 August 2008 02:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think the 'extra fetch' was still an issue in Release 7.3.4, although I wouldn't swear to it? I certainly remember being told to use cursors to retrieve a single row back then.

@Harry - as Michel says, there is no problem with read consistency and bulk collecting. Bulk Collect just affects the manner in which Oracle retrieves data from the cursor (by making it fetch multiple rows at once), it doesn't affect the underlying processing of the SQL.
Re: Which is better? For loop or Using cursor [message #343442 is a reply to message #343386] Wed, 27 August 2008 05:30 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4696422878211

disproves the extra-fetch theory as far back as 7.1, and Tom adds that it was the same in 7.0, which was the first version of stored PL/SQL (as opposed to client-side Forms applications).

In any case I think the original advice (I had it drummed into me as well at the time) was really to do with limiting network round trips in client-side PL/SQL, and not general advice for PL/SQL in the database. Maybe Forms 3.0 had an array-fetch optimisation for implicit cursors or maybe it didn't, but the point is that the old advice about explicit cursors avoiding a fetch operation

* Only ever applied to client-side PL/SQL
* Was never proved to be true in the first place, to my knowledge, but just repeated a lot
* Applied to a long-gone version of Forms that nobody has any more, so is now unprovable
* Has been conclusively disproved an every version of stored PL/SQL.

[Updated on: Wed, 27 August 2008 05:58]

Report message to a moderator

BULK FETCHING & read consistent image [message #345321 is a reply to message #343442] Wed, 03 September 2008 00:10 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I had been meaning to add to this before the long weekend but much to do as always

Michel - You responded on this topic that:
Quote:


Of course, read consistency and bulk collect are 2 differents with no relation at all. Bulk collect just removes context switching between SQL and PL/SQL engines, just like array fetching in other language removes clinet/server roundtrips.




I went back to my Oreilly's Oracle PL/SQL for DBA's which covers to 10g, by Furestein and Nanda. In the chapter 2 on Bulk Collections it states:

Quote:
(page 105) There is another, less obvious advantage to performing bulk fetches: the data base does not have to maintain a read-consistent view of the data while the records it retrieves are processed


It references further a code example from the previous page on single fetching and adds
Quote:

If the mythical DO_SOMETHING procedure took five seconds to process each of the 100 records retrieved from the ORDERS table, Oracle would have to maintain a read-consistent copy of the records in the result set for more than eight minutes. If the ORDERS table is busy with many other DML operations, then the database's rollback segments will be busy keeping a view of the data in synch for the long operation.


In regards to using Bulk Collections it then states this note:
Quote:

..there is a potential snag resulting from switching to a bulk fetch method: the DO_SOMETHING procedure will have to handle the situations where the orders it wants to process no longer exist becuase they were deleted after the bulk fetch occurred.



There must be some connection. However, I learned from this site that performance results from the database are the truth to all, so I'm not here to cite text in place of hard cold facts received from executing SQL oneself.

And thats what I took to do next - measure this elusive "consistency" image overhead. This all started a little over a year ago when to my dismay, a cursor processing a very large dataset "slowed down " and eventually became unreasonable.
It was slowly over time, only milliseconds per millions of recs -
I finally staggered commit points to get by.

My DBA at the time went over the read consistent image space allocation methods as an explanation. But according to her (she has moved on to take Sr. DBA position at Yahoo) it was not the rollback segments involved, but "something" where she claimed there was an undocumented "bug" in space allocation.

Now completely off kilter, I took to experimenting as in the
comparisson posted on performance times between implicit and explicit cursors.

In my case, I compared an explicit single fetch cursor to an explicit BULK FETCH LIMIT 1 cursor (v 9.2) - over a variety of records. I received identical response times which wasn't what I was hoping for - I was hoping to show the Bulk Fetch accelerating due to lack of the need for that image maintenance (which is why I put the LIMIT 1 in - to keep the pre-fetch capability out of it).

I then decided to add generic processing in between each fetch to add process time per record to see if that helped. It helped alright - just opposite of what I hoped, again - the single fetch cursor outperformed the bulk collect limit 1 cursor.

This was today and I want to re-review the test code to make sure process lines are identical between code sections for each
cursor type before posting.

I am baffled here and my only thought is that some buffering may occur that gives the single fetch cursor a "little pre-fetch capability" that is disengaged with the bulk fetch method.

I will post my results after my review tomorrow but am eager to
hear some feedback if this outcome would be what one of you
would have expected.

Regards,
Harry
Re: BULK FETCHING & read consistent image [message #345325 is a reply to message #345321] Wed, 03 September 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There must be some connection.

Of course for what has been fetched there is no more need to keep a consistent image but this is not directly related to bulk collect just you do it faster so you need the consistent image less time.

Quote:
the single fetch cursor outperformed the bulk collect limit 1 cursor.

This is meaningless, irrelevant and useless: a bulk of 1 is no more a bulk; using a feature for what it is not created is surely a good way to have bad things.

Regards
Michel

[Updated on: Wed, 03 September 2008 01:05]

Report message to a moderator

Re: BULK FETCHING & read consistent image [message #345327 is a reply to message #345325] Wed, 03 September 2008 01:30 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Michel Cadot wrote on Wed, 03 September 2008 07:04
This is meaningless, irrelevant and useless: a bulk of 1 is no more a bulk; using a feature for what it is not created is surely a good way to have bad things.

I think the idea of Harry's LIMIT 1 test was to see if there is any magic in the bulk collect mechanism itself that bypasses read consistency, and of course the test shows there is not. With LIMIT 1 you just have a more complicated version of a single fetch approach, and the additional moving parts affect performance.

Nanda and Feuerstein's point was probably that if you fetch all of the records into an array in one shot at the start of a long-running process, you are less likely to hit ORA-01555 errors than if you fetch one, do some processing, fetch the next, do some more processing etc. If the whole process takes an hour, say, you could still be fetching records 59 minutes after opening the cursor, and since any single query is required to provide read consistency in Oracle that could stretch your undo retention resources and risk failure in a busy system. If you'd fetched the lot into an array in minute 1 then it wouldn't be a problem (apart from the risk of lost updates etc but that is another question). They are not claiming (I hope) that the bulk collect mechanism itself bypasses read consistency. Also of course you mostly don't bulk collect everything in one go but instead fetch the rows in more manageable blocks using a limit clause.
Re: Which is better? For loop or Using cursor [message #345334 is a reply to message #343101] Wed, 03 September 2008 02:16 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Thank you very much William for your concise clarification.
That is exactly what I was trying to see - if the advantage of reducing the read consistent image resources had a noted performance increase (as an advantage of the bulk fetch).

I collected with a limit of 1 as a control to remove the influence of the pre-fetch performance advantage in the time measurement. I think my results do agree with your intrepretation of the text -a bulk fetch performance advantage does not exist because of its reduction in the resources used to maintain the read consistent image; but, an advantage of bulk collecting is that there is a reduction in that resource usage in general.

Best Regards!
Harry
Re: Which is better? For loop or Using cursor [message #345338 is a reply to message #345334] Wed, 03 September 2008 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again the main advantage (and purpose) of bulk collect is to reduce the number of switches between SQL and PL/SQL engines.

Regards
Michel
Re: BULK FETCHING & read consistent image [message #345376 is a reply to message #345327] Wed, 03 September 2008 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Nanda and Feurstine's point about fetching the results into an array and processing off that is of limited relevance - you could only do that in cases where your cursor returned relatively few rows.
If your cursor was to read tens or hundreds of millions of rows (not uncommon) then the performance overheads of storing al that data in memory would be crippling.
Re: Which is better? For loop or Using cursor [message #345405 is a reply to message #343101] Wed, 03 September 2008 07:41 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
1. I think one of the side questions that went unanswered above had to do with the shared pool, and caching execution plans to avoid hard parses. My answer to this question is that nothing discussed in this thread would affect that. It is the sql query text (and related parse information) that is cached, which is independent of the single row or bulk row plsql processing that you do with the results of the query you execute.

2. I would also agree with both William and JRowbottom in their interpretation / guess the meaning of your quote from the Oreilly book (in part based on assumptions I'm making about the author's Oracle knowledge). And agree with them that you will often be using limit anyway, so you will be doing multiple "fetch process cycles", regardless, meaning read consistency will still be maintained for most of the duration.

To me the point of the book authors really shouldn't be considered to relate to performance at all - but instead to fundamental differences in the two methods regarding "when" data is queried vs "when" it is processed. Which is always a valid data integrity issue to keep in mind no matter what you are doing (but I'm not sure I would call it an "advantage").

3. Just because 10g started implicitly bulk fetching, doesn't mean that the bulk fetch size it uses is ideal for your situation and your hardware (I realize that nobody here said it did, I'm just making the point). I don't recall for sure, but I thought that it did 100 rows at a time? It is possible that you would do better at 50 rows or 500 rows. The point of 10g was to make a general case guess of 100 being better than 1.

4. I like the questions raised by harrysmall3 and the test cases you attempted to get answers to your questions. This is an excellent example of how one can go about learning. Even if you aren't able to answer your initial question, you end up learning a lot of other things along the way.

Re: Which is better? For loop or Using cursor [message #345666 is a reply to message #345405] Thu, 04 September 2008 07:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Scot makes a reasonable point about implicit BULK COLLECTs not necessarily being optimal.

But how sub-optimal is it really going to be? In my experience, the real benefits of reducing round-trips is at the low-end.

A BULK COLLECT LIMIT 100 reduces round-trip time by 99%. Any further tuning can AT BEST recover only part of the remaining 1%.

Sure that remaining 1% might be a MINUTE that could be reduced to perhaps a few seconds. But a SQL that spends a minute in round trips fetch 100 rows at a time would have to process many millions (billions?) of rows. The minute of round trips would be minimal compared to the IO of the query, so the minute saved would not make a critical difference.

I wasn't aware of the implicit BULK COLLECT. But considering the relative simplicity and readability of the SQL, I'll be recommending it over BULK COLLECT for almost every occasion.

Now I'm going to have to edit one of my articles on the main site. Thanks a LOT Michel Wink

Ross Leishman
Re: Which is better? For loop or Using cursor [message #345669 is a reply to message #345666] Thu, 04 September 2008 07:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oh yeah, of course the IMPLICIT bulk collect doesn't come with an implicit FORALL writeback, does it. D'oh!

Looks like BULK COLLECT lives to fight another day.
Re: Which is better? For loop or Using cursor [message #345793 is a reply to message #343101] Thu, 04 September 2008 16:53 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Ross could you elaborate a little more on
Quote:

A BULK COLLECT LIMIT 100 reduces round-trip time by 99%. Any further tuning can AT BEST recover only part of the remaining 1%.



Is round-trip referring to (forgive my ignorance here)
the wait time of 'Sql-net message to client' & 'from client' waits?

I'm trying to find appropriate words for questions to replace
typing "Please say more!" Smile

But in general are you saying that there isnt really much gain with a higher fetch LIMIT given the CPU I/O tradeoff?

As always, thank you for your enlightenment!

(Your quote on the FORALL answered a question I had yet to post, which was, can one do a FORALL Insert/Update if they have data stored in a collection of an object type if they did not use BULK COLLECT.)

Best Regards,
Harry
Re: Which is better? For loop or Using cursor [message #345819 is a reply to message #345793] Thu, 04 September 2008 22:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, you can FORALL on a collection that you loaded manually. So technically you could do it with an implicit cursor loop, but it would mean doubling your memory overheads to store the implicit collection and an explicit one.

By round trip time, I mean that it takes a certain amount of time to switch context from PL/SQL to SQL and back. If you do that once for every 100 rows, you save 99% of that time. Granted, there will be some additional overhead to loading those 100 rows into the collection before returning them to PL/SQL, but that should be minimal compared to the context switch.

Ross Leishman
Re: Which is better? For loop or Using cursor [message #345853 is a reply to message #345793] Fri, 05 September 2008 02:04 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
harrysmall3 wrote on Thu, 04 September 2008 22:53
Is round-trip referring to (forgive my ignorance here)
the wait time of 'Sql-net message to client' & 'from client' waits?

Not quite. The SQL*Net message from/to client wait events are for a client-side application connected across a network. When the database is waiting for a message from the client it usually means the client is sitting idle (e.g. at the SQL*Plus prompt), although there are cases where poor application design or connection setup can result in millions of short waits, indicating a possible problem.
Re: Which is better? For loop or Using cursor [message #345990 is a reply to message #343101] Fri, 05 September 2008 08:40 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would agree with Ross that the default of 100 rows is "so much better" than the 9i and previous default of 1 that in most cases you can consider yourself done and spend your time on other matters.
Previous Topic: DYNAMIC SQL - GLOBAL VARIABLES
Next Topic: Procedure with merge
Goto Forum:
  


Current Time: Sun Dec 11 00:30:51 CST 2016

Total time taken to generate the page: 0.08584 seconds