September 10, 2016 at 11:39 am
dbishop (9/10/2016)
I too try to avoid them at all cost, and generally will opt for, as you stated, a well written WHILE loop.
Why? In my experience, a (well written) cursor will never ever beat a WHILE loop, no matter how well written it is.
Read "avoid cursors" as "avoid iteration". Then if you do need iteration after all, use a cursor (with the correct options).
September 10, 2016 at 10:27 pm
A note about WHILE loops versus cursors. If you are using a WHILE loop with some sort of logic to increment the next row to be read, then you are going to traverse an index to get your next row for each iteration of the loop. Cursors can avoid that overhead and just go to the next row and the next. So if you feel you MUST do row by row processing, cursors should be more efficient than while loops.
That said, we still have WHILE loops in play where the amount of time to traverse the index to get to the row is inconsequential in the total processing time. For example, where each iteration of the loop launches a set based query that updates ten thousand rows, the time spent in the loop is a tiny fraction of the overall runtime. The time spent changing, QA'ing and pushing the code to production would never be justified by the time saved.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 12, 2016 at 6:45 am
peter.row (6/1/2015)
Added a cursor just the other week when a recursive CTE used in a cross apply was causing a query to take over the .NET default 30 second timeout - 34-40 seconds in some cases.I removed the cross apply and used the same recursive CTE in a cursor on the same data set and boom it took only 2-3 seconds to populate a temp table that I inner joined to the original query.
I've run across similar problems where cursors actually made things better.
September 12, 2016 at 6:54 am
Added a cursor just the other week when a recursive CTE used in a cross apply was causing a query to take over the .NET default 30 second timeout - 34-40 seconds in some cases.
I removed the cross apply and used the same recursive CTE in a cursor on the same data set and boom it took only 2-3 seconds to populate a temp table that I inner joined to the original query.
Would you be able to post the before and after query? I'd like to try to understand this better.
September 12, 2016 at 7:17 am
Hugo Kornelis (9/10/2016)
dbishop (9/10/2016)
I too try to avoid them at all cost, and generally will opt for, as you stated, a well written WHILE loop.Why? In my experience, a (well written) cursor will never ever beat a WHILE loop, no matter how well written it is.
Read "avoid cursors" as "avoid iteration". Then if you do need iteration after all, use a cursor (with the correct options).
I think you either misread my post or mis-typed yours. I stated I opt for a well written WHILE loop over a cursor.
September 12, 2016 at 8:04 am
dbishop (9/12/2016)
Hugo Kornelis (9/10/2016)
dbishop (9/10/2016)
I too try to avoid them at all cost, and generally will opt for, as you stated, a well written WHILE loop.Why? In my experience, a (well written) cursor will never ever beat a WHILE loop, no matter how well written it is.
Read "avoid cursors" as "avoid iteration". Then if you do need iteration after all, use a cursor (with the correct options).
I think you either misread my post or mis-typed yours. I stated I opt for a well written WHILE loop over a cursor.
Yes. And I asked you why, because in my experience a (well-written) cursor will always be faster than any WHILE. So you are opting for a solution that takes (a bit) more code *and* that is slower.
So I was wondering why you opt for a WHILE loop over a cursor.
September 12, 2016 at 8:08 am
Hugo Kornelis (9/12/2016)
That isn't actually what you *said*, though? You said that a (well written) cursor will never, ever beat a WHILE loop, which implies the WHILE is faster?
September 12, 2016 at 8:17 am
That isn't actually what you *said*, though? You said that a (well written) cursor will never, ever beat a WHILE loop, which implies the WHILE is faster?
We all need to remember that for many posters, English is not their first language. While it may not be incorrect to point out inconsistencies, we should be careful how we do this.
September 12, 2016 at 8:57 am
I think it fair to say that the exotic cases where GOTO absolutely cannot be avoided are truly rare and maybe even apocryphal. However, there may be a few cases where a GOTO makes your code simpler and perhaps even more efficient. I have been coding for a very long time and the few times when I thought a GOTO was a good idea I took that as a red flag indicating the design of the logic needed rethinking. Your mileage may vary.
That being said, any rule-of-thumb is a shortcut and when they don't seem to be helpful in achieving your goal one ought not to follow them blindly. Ditto for frameworks, design patterns, Agile and the like. If they help you get the job done correctly and well then go ahead. If not then following them slavishly is foolish.
And does the fact that MS uses cursors tell us anything? I read this way: if an informed professional concludes that a cursor makes sense in a given situation then cursor away guilt free.
September 12, 2016 at 9:08 am
paul.knibbs (9/12/2016)
Hugo Kornelis (9/12/2016)
That isn't actually what you *said*, though? You said that a (well written) cursor will never, ever beat a WHILE loop, which implies the WHILE is faster?
*Woops*
Typical case of PEBKAC. (Or in this case, PEIMB - problem exists in my brain)
After reading dbishop's last reply I double checked my post and **still** managed to overlook the logic error. It was only after reading your explanation that I started to slap my forehead. (Which reminids me that this forum really needs a *facepalm* smiley)
Oh, and Ron: You are right in general, and in my specific case as well because I am indeed not a native English speaker, but in this case the problem really was me not seeing how I had accidentally misplaced the two terrms.
So yes, I mistyped my original reply. What I should have written was "a WHILE loop, no matter how well written it is, will never ever beat a (well written) cursor".
Sorry for the confusion.
September 12, 2016 at 9:37 am
Oh, and Ron: You are right in general, and in my specific case as well because I am indeed not a native English speaker, but in this case the problem really was me not seeing how I had accidentally misplaced the two terrms.
I knew that. We had discussed once that you were from Holland. I still wanted to be circumspect as your English is excellent. Viel besser als mein deutsch. Als Fremdsprecher selbst weiss ich ganz genau, wie schwierig es sein kann, solche Sachen richtig und genau zum Ausdruck machen. But my experiences living in Germany as a foreign speaker have taught me a lot about dealing with foreign speaker to English.
September 12, 2016 at 2:23 pm
James Stephens (6/2/2015)
-I've got plenty of code I look at and am embarrassed that I actually wrote that--Jim
Actually, if you don't have any code that you can look back at and realize it could have been done much better, it probably means that you're not learning anything.
September 12, 2016 at 2:29 pm
[p]if you don't have any code that you can look back at and realize it could have been done much better, it probably means that you're not learning anything. [/p]
That's going in my text file of useful quotes.
September 12, 2016 at 5:33 pm
Gary Harding (6/3/2015)
Lenochka (6/1/2015)
Thank you for writing this. I used to ask a cursor question when interviewing candidates and I got the same answer every time “you shouldn’t use cursors, they are bad”. I found it to be extremely frustrating. Developers use that as an excuse to never learn how to write cursors or when to use them. I have seen people write very un-maintainable, convoluted code (that might not always work correctly), or resort to manual processing in places where a cursor is an easy, clean an elegant solution.Cursors don't kill performance.
People (using cursors inappropriately) kill performance.
Good one.
September 12, 2016 at 5:57 pm
RonKyle (9/12/2016)
[p]if you don't have any code that you can look back at and realize it could have been done much better, it probably means that you're not learning anything. [/p]That's going in my text file of useful quotes.
Wow. You just made my day.
Viewing 15 posts - 196 through 210 (of 215 total)
You must be logged in to reply to this topic. Login to reply