Post reply

Cursors Be Gone!

  • December 31, 2008 at 5:15 am

    #919557

    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

    Tom

  • December 31, 2008 at 5:28 am

    #919560

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

    Tom

  • December 31, 2008 at 6:58 am

    #919611

    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)

  • December 31, 2008 at 7:03 am

    #919617

    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

  • December 31, 2008 at 7:21 am

    #919629

    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)

  • December 31, 2008 at 7:23 am

    #919632

    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

  • December 31, 2008 at 7:32 am

    #919640

    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)

  • December 31, 2008 at 7:54 am

    #919650

    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

  • December 31, 2008 at 8:03 am

    #919660

    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

  • December 31, 2008 at 8:31 am

    #919688

    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

  • December 31, 2008 at 9:15 am

    #919712

    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)

  • December 31, 2008 at 3:38 pm

    #919856

    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.

  • January 1, 2009 at 11:28 am

    #919967

    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

  • January 1, 2009 at 12:09 pm

    #919987

    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.

    🙂

  • January 1, 2009 at 12:14 pm

    #919993

    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)

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

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