July 1, 2011 at 7:10 am
Ares these loops exists in TSQL 2k8 R2 language?
Is there a way to loop thru a query result without using cursor , fetch and while?
In Oracle we can do:
For( row in (select * from People)) Loop
End Loop;
Is there a similar way in SQL Server?
July 1, 2011 at 7:37 am
filipeesch (7/1/2011)
Ares these loops exists in TSQL 2k8 R2 language?Is there a way to loop thru a query result without using cursor , fetch and while?
In Oracle we can do:
For( row in (select * from People)) Loop
End Loop;
Is there a similar way in SQL Server?
There are, of course, the rare exceptions, but such loops should generally be avoided... even in Oracle.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2011 at 7:42 am
If exists, how can I do that?
July 1, 2011 at 8:10 am
if you are required to use loops, look up either the CURSOR keyword or the WHILE keyword. Advice is, Loops are fine for administrative operations... please don't use them for general coding...
July 1, 2011 at 8:34 am
I'm using a cursor and a while. But, I want to know if there is a simplifyed way to do this iretactions, like Oracle For.
July 1, 2011 at 9:14 am
I don't see what you're showing as being any simpler than what a cursor looks like in T-SQL. The question is, what exactly are you trying to do within the loop. If you are doing simple selects, inserts, updates, updates or deletes, standard SQL syntax handles that with the "loops" built in. Like Jeff said, cursors or while loops usually aren't needed and are avoided for performance reasons.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 1, 2011 at 9:33 am
I'm not doing simple updates. Its a logic that a simples SQL command dos not resolve.
In oracle ican do a foreach expression, like this:
FOR( row IN (SELECT * FROM People)) LOOP
--Some code
END LOOP;
In SQL Server todo this I have to declare a cursor, open it, do a fetch, a while, fetch it again, close it and deallocate it, like this:
DECLARE p CURSOR FOR (SELECT * FROM People);
OPEN p;
FETCH NEXT FROM p INTO ...;
WHILE @@FETCH_STATUS = 0
BEGIN
---Some code
FETCH NEXT FROM p INTO ...;
END
CLOSE p;
DEALLOCATE p;
I think the Oracle solution is cleaner. What the simplest way to do this loop in SQL Server? This is the question.
July 1, 2011 at 9:38 am
filipeesch (7/1/2011)
I'm not doing simple updates. Its a logic that a simples SQL command dos not resolve.
Are you sure about that? Can you post the code?
July 1, 2011 at 9:40 am
The point that Dixie and Jeff are trying to make is that loops and cursors can almost always be avoided and replaced with a MUCH MUCH MUCH faster set based solution. You have to shift your mindset from what you want to do to a record to what you want to do to a column. If you want specific help there are lots of people willing and able to help, especially when it comes to removing cursors. Given the skeleton psuedocode you provided I am about 140% certain it can be done without a while loop or a cursor.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2011 at 9:46 am
Unfortunally not. Plase, I only want to know if there is a "for each" loop, if there is not, I will use WHILE and a CURSOR.
July 1, 2011 at 9:54 am
Sean Lange (7/1/2011)
The point that Dixie and Jeff are trying to make is that loops and cursors can almost always be avoided and replaced with a MUCH MUCH MUCH faster set based solution. You have to shift your mindset from what you want to do to a record to what you want to do to a column. If you want specific help there are lots of people willing and able to help, especially when it comes to removing cursors. Given the skeleton psuedocode you provided I am about 140% certain it can be done without a while loop or a cursor.
Well, I'll try to think another way, without cursors. Thanks.
July 1, 2011 at 10:12 am
Feel free to post back if you get into a bind. There are a zillion things you can do that will truly shock you for a set based solution. Often times when you think it can't be done short of a cursor there are ways you probably have never even dreamed of that will work. Hang out on these forums and you will learn a ton of great and efficient coding methods from people far smarter than me. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2011 at 5:16 pm
Quick war story: Someone once asked me to look at a cursor-based query they wrote to split a single large table into three separate tables. Thinking it would be faster to only go through the input file once, they used a cursor to read a row from the large existing table and then do a single INSERT into each of the the three new tables. The job was taking hours to run. We replaced it with three separate INSERT INTO... SELECT FROM queries (no cursors, no while loops) and the new job ran in just a few minutes. That's the kind of performance difference we're talking about.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 1, 2011 at 9:27 pm
The Dixie Flatline (7/1/2011)
Quick war story: Someone once asked me to look at a cursor-based query they wrote to split a single large table into three separate tables. Thinking it would be faster to only go through the input file once, they used a cursor to read a row from the large existing table and then do a single INSERT into each of the the three new tables. The job was taking hours to run. We replaced it with three separate INSERT INTO... SELECT FROM queries (no cursors, no while loops) and the new job ran in just a few minutes. That's the kind of performance difference we're talking about.
I have one, as well. I actually won a cash award from the company I was working at for fixing a major performance problem. To make a much longer story shorter, we had to do daily deduplication of Call Detail Records (CDR's) on 3 databases (1 for each of 3 consecutive months for a daily bill-cycle) and it was taking 45 minutes. Each daily database contained over 4 million CDR's. Since we operated on an "Accrual" basis, we also had to do a "Month End" deduplication for all 28 daily bill-cycles for 3 months. That would normally take 10 to 24 hours to fail and that was only for 2 months because it just took too bloody long. The code was written in C# and it looped like crazy behind the scenes.
I rewrote both the daily runs and the month end runs. The first time they ran one of the daily runs, they immediately reported a "failure". When I asked what the failure was, they said "We know it couldn't have run correctly because it only took 11 seconds." At my prompting, they discovered that it had, indeed, executed and it did it correctly. The Daily DeDuplication runs had gone down from 45 minutes to 11 seconds.
The "Month End" runs also dropped in duration from 10 - 24 hours down to 30-45 minutes and I included all 3 months instead of just the 2. They also haven't failed since I put the code into production almost 3 years ago. To put things in perspective, the DeDuplication occured on 50% more data across 84 databases with an average of 4 million rows each in 30-45 minutes total duration. That's the power of set-based programming instead of using RBAR.
I'm not convinced that we've convinced the OP to avoid loops. It would be nice if these true war stories acted as a deterent to using loops but, judging from the responses on this thread, the OP seems hell bent on using them.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2011 at 9:36 pm
filipeesch (7/1/2011)
I'm not doing simple updates. Its a logic that a simples SQL command dos not resolve.In oracle ican do a foreach expression, like this:
FOR( row IN (SELECT * FROM People)) LOOP
--Some code
END LOOP;
In SQL Server todo this I have to declare a cursor, open it, do a fetch, a while, fetch it again, close it and deallocate it, like this:
DECLARE p CURSOR FOR (SELECT * FROM People);
OPEN p;
FETCH NEXT FROM p INTO ...;
WHILE @@FETCH_STATUS = 0
BEGIN
---Some code
FETCH NEXT FROM p INTO ...;
END
CLOSE p;
DEALLOCATE p;
I think the Oracle solution is cleaner. What the simplest way to do this loop in SQL Server? This is the question.
Yes, the Oracle solution is cleaner, from a certain point of view. I have been working Oracle for about 11 months now, and I have found that set-based solutions do perform better than cursor (or loop based) solutions.
You say you can't post your actual code, but how about something that closely represents the problem you are attempting to solve. I wouldn't be surprised if there is in fact a set-based solution that will blow the doors off your cursor-based solution.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply