For And ForEach Loops

Question

For And ForEach Loops

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

It's not necessarily a good thing that Oracle makes it so easy to write cursor-based logic. It does do some smart things with the foreach turning the logic into bulk (rather than row-by-row), but I still find it better to come up with a set based approach from the get-go.

One of my own horror stories was taking a daily load that was taking 26 hours to run (I'm sure you see the problem) and getting it to run in well under an hour (15-20 minutes depending on volume). The original code was doing a cursor-based lookup from a remote server with a table on the local server. I replaced it with fetching the remote table to a local copy of the table, and then a join. From memory, that was between two Oracle 8i databases.

When I absolutely have to use a cursor in SQL Server, I use the following code structure (for the earlier example);

DECLARE p CURSOR FOR (SELECT * FROM People);

OPEN p;

WHILE (1=1)

BEGIN

FETCH NEXT FROM p INTO ...;

IF @@FETCH_STATUS <> 0 BREAK;

---Some code

END

CLOSE p;

DEALLOCATE p;

Usually I don't even need all of that as I can just do a select statement returning one row within the loop:

DECLARE @PersonId int = 0;

WHILE (1=1) BEGIN

SELECT TOP (1)

@PersonId = PersonId,

...

FROM People

WHERE PersonId > @PersonId

ORDER BY PersonId;

IF @@ROWCOUNT = 0 BREAK;

---Some code

END;

From a syntactical point of view, Oracle would still save a little but not much. On the other hand, I find there are things in SQL Server that are much easier syntactically than in Oracle.

Really, I can't stress enough that if you can use a set-based approach to your code, you should, in both Oracle and SQL Server.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

To go along with Bruce's post, I can't stress enough that a whole lot more CAN be done using set-based code than a lot of folks think. There are actually very, very few things that don't have a high performance, set-based answer. Don't give up too quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Simple answer: no, there is no FOR/NEXT looping construct in TSQL. Use a cursor if you do not want to pursue a set-based solution. Be sure to declare the cursor FORWARD_ONLY READ_ONLY LOCAL STATIC (fastest per hugo kornelis testing) or declare it FAST_FORWARD (usually as fast) to be 'optimal' with the cursor operations.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service