Cursors Be Gone!

Question

Cursors Be Gone!

Viewing 15 posts - 61 through 75 (of 272 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Just add "FAST_FORWARD" and "STATIC" to the cursor definition and the cursor method will run as fast as your non-cursor method (and for large datasets it will probably be faster).

SP_MSforEachdb declares a Global Updateable Scroll cursor hCForEach and calls sp_MSforeach_worker to process it in a while fetch-status loop, and is going to be a lot slower than sensible use of a properly declared cursor in the example given, so the suggestion that it would "work as well" is pure nonsense.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 2

In my previous post please substitute "or" fro "and" in "FAST_FORWARD and STATIC". Tom.

Tom

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

Tom.Thomson (12/31/2008)
Just add "FAST_FORWARD" and "STATIC" to the cursor definition and the cursor method will run as fast as your non-cursor method (and for large datasets it will probably be faster).
SP_MSforEachdb declares a Global Updateable Scroll cursor hCForEach and calls sp_MSforeach_worker to process it in a while fetch-status loop, and is going to be a lot slower than sensible use of a properly declared cursor in the example given, so the suggestion that it would "work as well" is pure nonsense.
Tom

Bravo! Finally... someone that knows how bad MSforEachdb and MSforEachTable actually are! And, if you get into the habit of declaring cursors as LOCAL, you also don't have to worry about closing and deallocating them... they automatically drop with the same scope as a temp table.

Of course, that's a habit I'll never develop if you know what I mean 😉

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

Jeff Moden (12/31/2008)
Tom.Thomson (12/31/2008)
Just add "FAST_FORWARD" and "STATIC" to the cursor definition and the cursor method will run as fast as your non-cursor method (and for large datasets it will probably be faster).
SP_MSforEachdb declares a Global Updateable Scroll cursor hCForEach and calls sp_MSforeach_worker to process it in a while fetch-status loop, and is going to be a lot slower than sensible use of a properly declared cursor in the example given, so the suggestion that it would "work as well" is pure nonsense.
Tom
Bravo! Finally... someone that knows how bad MSforEachdb and MSforEachTable actually are! And, if you get into the habit of declaring cursors as LOCAL, you also don't have to worry about closing and deallocating them... they automatically drop with the same scope as a temp table.
Of course, that's a habit I'll never develop if you know what I mean 😉

Yeah, completely true, but however inefficient msforeachdb is as a cursor, when you're running consistency checks against all the databases on a server, does the extra 45ms (or whatever it works out to) per iteration on the cursor really matter?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Grant Fritchey (12/31/2008)
Yeah, completely true, but however inefficient msforeachdb is as a cursor, when you're running consistency checks against all the databases on a server, does the extra 45ms (or whatever it works out to) per iteration on the cursor really matter?

Probably not. But, what does matter is some newbie thinking that (s)he can use it in production code for something more, ummm.... instrusive, if they see it used in other code... especially if they find out that the big bad DBA who keeps spouting the "set based" mantra wrote code that inherently uses a cursor. 😉 Sure, it's explainable... as are most forms of "damage control". :w00t:

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Jeff Moden (12/31/2008)
Grant Fritchey (12/31/2008)
Yeah, completely true, but however inefficient msforeachdb is as a cursor, when you're running consistency checks against all the databases on a server, does the extra 45ms (or whatever it works out to) per iteration on the cursor really matter?
Probably not. But, what does matter is some newbie thinking that (s)he can use it in production code for something more, ummm.... instrusive, if they see it used in other code... especially if they find out that the big bad DBA who keeps spouting the "set based" mantra wrote code that inherently uses a cursor. 😉 Sure, it's explainable... as are most forms of "damage control". :w00t:

And that's why we get paid the big bucks... Speaking of which, I need to talk to my boss about that pay check thing...

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Grant Fritchey (12/31/2008)
Speaking of which, I need to talk to my boss about that pay check thing...

BAAA-HAAA! Does (s)he know that you write code that uses a cursor (p_MsForEachDB) in his/her production database? 😀

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 8

Are you kidding? I write cursors for everything so that I can come back later and "tune" the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Is adding STATIC to a FAST_FORWARD cursor declaration unnecessary, redundant or worse, harmful?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 10

I was just reading a blog post by Hugo Kornelis. He said, based on his tests, STATIC was the way to go, that it outperformed FASTFORWARD cursors.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Ok... now I'm getting really afraid for you guys... :hehe: you're talking about this like you use cursors and you're listening to the guy who wants to remove the FROM clause from the UPDATE statement... 😉

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

npfitzner SSC Journeyman Points: 85 More actions · Answer 12

Grant Fritchey (12/31/2008)
I was just reading a blog post by Hugo Kornelis. He said, based on his tests, STATIC was the way to go, that it outperformed FASTFORWARD cursors.

But again - what sort of tests is he running? 🙂 Some may suit STATIC better than FASTFORWARD - at least, that's what this whole thread has been about so far: depending on what you're doing, it takes diff'rent strokes to move the data, yes it does 😉

Where is this post - I'd genuinely like to read it.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Jeff Moden (12/31/2008)
Ok... now I'm getting really afraid for you guys... :hehe: you're talking about this like you use cursors and you're listening to the guy who wants to remove the FROM clause from the UPDATE statement... 😉

He wants to remove the From clause from the Update statement? Seriously? That's complete lunacy, would damage the language enough to leave it close to useless for many of the things I do with it. I find it very difficult to beliieve that any sane person with even a small understanding of what SQL is about could advocate such a thing.

Tom

npfitzner SSC Journeyman Points: 85 More actions · Answer 14

Tom.Thomson (1/1/2009)
He wants to remove the From clause from the Update statement? Seriously?

No - I think that's what the 😉 was for.

🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 15

Tom.Thomson (1/1/2009)
Jeff Moden (12/31/2008)
Ok... now I'm getting really afraid for you guys... :hehe: you're talking about this like you use cursors and you're listening to the guy who wants to remove the FROM clause from the UPDATE statement... 😉
He wants to remove the From clause from the Update statement? Seriously? That's complete lunacy, would damage the language enough to leave it close to useless for many of the things I do with it. I find it very difficult to beliieve that any sane person with even a small understanding of what SQL is about could advocate such a thing.

Yeah... visit his blog, sometime. I'd give it to you except I deleted it to keep my machine from throwing up. 😀

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