A Case FOR Cursors...

Question

A Case FOR Cursors...

Viewing 6 posts - 211 through 215 (of 215 total)

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

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 1

Hugo Kornelis (9/12/2016)
What I should have written was "a WHILE loop, no matter how well written it is, will never ever beat a (well written) cursor".

Do you have some readily runnable code examples that demonstrate that?

--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)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Jeff Moden (9/14/2016)
Hugo Kornelis (9/12/2016)
What I should have written was "a WHILE loop, no matter how well written it is, will never ever beat a (well written) cursor".
Do you have some readily runnable code examples that demonstrate that?

Sure: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx (**)

In my experience, when people claim that they improved performance by rerplacing a cursor with a WHILE loop and I dig into the details, I have so far always found that they spent a lot of time optimizing the hell out of the WHILE loop, but didn't spend a single second on trying vaious cursor options. Yes, a cursor with no options, which usually defaults to a dynamic cursor, is easy to beat by a WHILE loop - but even easier to beat by a cursor with the correct options.

(**) That's a blog I wrote back in 2007, but it's still relevant; I didn't repeat the peforrmance tests on newer versions of SQL Server but I have not heard any reports on work being done to optimize cursor performance. I'd be surprised if a new test would really change things.

There is one thing that I did miss back when I wrote this. I was surprised to see that the FAST_FORWARD option, in spite of its name, is not the fastest. I laterr found that under some very specific circumstances, it actually is faster than a STATIC cursor - when memory pressure causes (pats of) the temporary table to be removed fom cache, then the extra IO to bring the data back into cache when reading can tip the scale in the favor of FAST_FORWARD (which rerquires a far smaller temporary table so that it is less susceptible to this problem.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 3

Hugo Kornelis (9/15/2016)
Jeff Moden (9/14/2016)
Hugo Kornelis (9/12/2016)
What I should have written was "a WHILE loop, no matter how well written it is, will never ever beat a (well written) cursor".
Do you have some readily runnable code examples that demonstrate that?
Sure: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx (**)
In my experience, when people claim that they improved performance by rerplacing a cursor with a WHILE loop and I dig into the details, I have so far always found that they spent a lot of time optimizing the hell out of the WHILE loop, but didn't spend a single second on trying vaious cursor options. Yes, a cursor with no options, which usually defaults to a dynamic cursor, is easy to beat by a WHILE loop - but even easier to beat by a cursor with the correct options.
(**) That's a blog I wrote back in 2007, but it's still relevant; I didn't repeat the peforrmance tests on newer versions of SQL Server but I have not heard any reports on work being done to optimize cursor performance. I'd be surprised if a new test would really change things.
There is one thing that I did miss back when I wrote this. I was surprised to see that the FAST_FORWARD option, in spite of its name, is not the fastest. I laterr found that under some very specific circumstances, it actually is faster than a STATIC cursor - when memory pressure causes (pats of) the temporary table to be removed fom cache, then the extra IO to bring the data back into cache when reading can tip the scale in the favor of FAST_FORWARD (which rerquires a far smaller temporary table so that it is less susceptible to this problem.

I agree on that. Changing Cursors to Temp Tables and While loops is an incredible waste of time in all the cases I've seen so far. The post that you made about While Loops "never" being able to beat a properly written Cursor did pique my interest, though. Made me wonder if there ever was a situation where that might not be true. I did beat "a" Cursor with a While loop on a "Bin Fill" problem once but I also suck at writing cursors and it may not have been as optimized as it could have been.

Thanks for the link, Hugo. I'll take a look.

--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)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

Took a quick look, Hugo. Looks great and I don't actually have to figure out how to write a Cursor (I think I've done it only 2 or 3 times in 2 decades). Thank you for all the work you did there.

I'm also tickled to see your documentation on the FAST_FORWARD "penalty". I've previously ran across that and believe that no article on Cursors would be complete without it. Thank you for your thoroughness.

I don't, however, see code where you compared it to a Temp Table/While loop to be able to make the claim that a While Loop will never beat a properly written Cursor BUT, I now have some really great examples by someone I trust that has done a heck of a lot more testing on cursors than I would have even considered doing. If I come up with code where a While loop appears to be faster, I'll run the Cursor code past your good eyes to make sure that I've not somehow crippled or otherwise put the Cursor code at a disadvantage.

Thanks again, Hugo.

--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)

David.Poole SSC Guru Points: 75992 More actions · Answer 5

Doesn't a while loop tend towards the nature of a dynamic cursor rather than a static cursor?

LinkedIn Profile

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

David.Poole (9/15/2016)
Doesn't a while loop tend towards the nature of a dynamic cursor rather than a static cursor?

Depends on how you build it. If you repeat the query in each iterration, it's similar to a dynamic cursor. If you put rows in a temp table and then iterate over that table, it's much more like a static cursor.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/