July 6, 2011 at 6:59 am
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.
July 6, 2011 at 4:56 pm
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
Change is inevitable... Change for the better is not.
July 7, 2011 at 11:48 am
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
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy