For And ForEach Loops

• Bruce W Cassidy

  SSChampion

  Points: 12668

  More actions

  July 6, 2011 at 6:59 am

  #1348900

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

  More actions

  July 6, 2011 at 4:56 pm

  #1349357

  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

  July 7, 2011 at 11:48 am

  #1349903

  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