Performance Improvement for Cursors in Stored Procedures

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    Comments posted to this topic are about the item Performance Improvement for Cursors in Stored Procedures

  • jyouq

    SSC Journeyman

    Points: 98

    Interesting article. But I wasn't too familiar with cursors, having only written 1 to solve an issue, so I had to hit the internet to see where/how to apply the LOCAL, and FAST_FORWARD, options. I did find a link to an article that explained, in additional detail, why this is probably the way to go if cursors must be used.

    http://sqlperformance.com/2012/09/t-sql-queries/cursor-options

  • Yakov Shlafman

    SSCommitted

    Points: 1613

    Briant,

    thanks for the article.

    I think the first snippet of the code should be like this:

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @hDeptTaskInstructions = ' ' + @hDeptTaskInstructions + ' ' + isnull(@hTaskInstructions, ' ')

    FETCH NEXT FROM TaskInstructions_cursor INTO @hTaskInstructions

    END;

    The presented logic has issues. Please double check it.

    The bottom line is - if @hTaskInstructions is null, you should take of it before you append it to @hDeptTaskInstructions.

    Otherwise you would lost all previous values

    Thanks

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    Yakov Shlafman (2/3/2015)


    Briant,

    thanks for the article.

    I think the first snippet of the code should be like this:

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @hDeptTaskInstructions = ' ' + @hDeptTaskInstructions + ' ' + isnull(@hTaskInstructions, ' ')

    FETCH NEXT FROM TaskInstructions_cursor INTO @hTaskInstructions

    END;

    The presented logic has issues. Please double check it.

    The bottom line is - if @hTaskInstructions is null, you should take of it before you append it to @hDeptTaskInstructions.

    Otherwise you would lost all previous values

    Thanks

    Thank you Yakov. I didn't write the original code. This was a real piece of application code from a system I recently inherited. Yes there are logical problems in much of the code (and many bugs). These stored procedures support an ancient app written in VB6 that has even more logical issues (which I have started refactoring for a rewrite into more modern VB.NET and will hopefully totally replace)...

    My goal is to make things better every time I touch the code. I may not leave it perfect, but better than it was originally.

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    jyouq (2/3/2015)


    Interesting article. But I wasn't too familiar with cursors, having only written 1 to solve an issue, so I had to hit the internet to see where/how to apply the LOCAL, and FAST_FORWARD, options. I did find a link to an article that explained, in additional detail, why this is probably the way to go if cursors must be used.

    http://sqlperformance.com/2012/09/t-sql-queries/cursor-options

    The cursor modifiers are part of the cursor definition line. There are many other articles that explain in more detail what the modifiers do. If you are writing your own procedures, I'd recommend avoiding cursors if possible. But if you MUST use them, do whatever you can to speed them up, Local and Fast_Forward are the quick fixes for 90% or more of the cursor based procedures around.

  • Caruncles

    Hall of Fame

    Points: 3285

    Great and timely article. Using your search query I discovered we have 129 procs using cursors. However, I was hoping you would show code examples of the Local and Fast Forward. I'll look it up, but it's often helpful to see examples which are proven to work.

    Thanx!

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    Caruncles (2/3/2015)


    Great and timely article. Using your search query I discovered we have 129 procs using cursors. However, I was hoping you would show code examples of the Local and Fast Forward. I'll look it up, but it's often helpful to see examples which are proven to work.

    Thanx!

    I see that I left the cursor declaration off the code section when I copied the article from word to the online editor.

    DECLARE TaskInstructions_cursor CURSOR Local Fast_Forward FOR

    SELECT JobTaskData.TaskInstructions

    FROM JobTaskData INNER JOIN Task ON JobTaskData.Task_ID = Task.Task_ID

    WHERE (JobTaskData.Quote_ID = @hQuote_ID) AND

    (JobTaskData.VersionNo = @hVersionNo) AND

    (Task.Department_ID = @hDepartment_ID) AND

    (Task.TaskTypeID = 1)

    OPEN TaskInstructions_cursor

    The code example was for a very simple usage of the cursor, but in general you can check the procedure to see if it is using the Cursor in the most common pattern...

    Declare Cursor Cursorname For select whatever

    Open Cusrorname

    Fetch Next from cursorname

    WHILE @@FETCH_STATUS = 0

    begin

    do something with the data

    Fetch next from cursorname

    end

    close cursorname

    deallocate cursorname

    If that's your structure, you can add the Local and FAST_FORWARD keywords to the cursor definition for a quick improvement without changing the rest of the code or its behavior. (As always test on a non-production node first and make sure to save a copy of the original before changing it. There are good discussions about source control elsewhere.)

  • Caruncles

    Hall of Fame

    Points: 3285

    Thanx! Your article and links provider by responders has prompted me to do more research (which I should have already done!).

    Good Job!

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • h.tobisch

    SSCommitted

    Points: 1671

    1. I never use static cursor but cursor variable

    declare @C cursor

    set @C = cursor fast_forward for select * from kd_zp

    would be interesting to see if there is a difference in performance.

    Advantage is, that you do not need to de-allocate them.

    1. best and logically correct way to concatenate results

    select textstring = (select atnummer from lieferant FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')

  • DennisPost

    SSCrazy

    Points: 2691

    Nice article.

    I use cursors reasonably often for administrative task.

    Somewhere along the line I picked up a misconception. I though LOCAL and FAST_FORWARD were defaults.

    LOCAL might be the default if it is configured so in the database options.

    This code will show you what is currently configured for all your databases in the instance.

    SELECT Name

    , is_Cursor_close_on_commit_on

    , is_Cursor_close_on_commit_on_text = CASE is_Cursor_close_on_commit_on WHEN 1 THEN 'True' ELSE 'False' END

    , is_local_cursor_default

    , is_local_cursor_default_text = CASE is_local_cursor_default WHEN 1 THEN 'Local' ELSE 'Global' END

    FROM sys.Databases

    From BOL Declare Cursor

    READ ONLY

    Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

    FORWARD_ONLY

    Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors.

    FAST_FORWARD

    Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

    Cheers

  • j-1064772

    SSCertifiable

    Points: 5316

    This article provides a good INTRODUCTION to a set-based method of eliminating "row-by-agonizing-row" (RBAR) cursor code but it is not the complete story. There is more information to consider BEFORE deploying such code in a production environment.

    The three-part SET method of eliminating the use of cursors has been abundantly discussed in previous articles by people I regard as real experts.

    About two or three years ago different opinions were expressed regarding its use. One side was concerned that theoretically there is no formal guarantee that MS SQL Server will always respect the expected order/sequence of rows. The opposite side consider that in real life, on huge sets, the theoretical problem has never materialized and that dramatic speed improvements have been achieved (several orders of magnitude). Both the main proponent and opponent of this method have consistently demonstrated a level of expertise that is light-years above what I could ever dream of achieving.

    I have no idea if this difference of opinions has ever been settled. Maybe the experts who expressed their opinions would provide a short update of the current situation.

    Another suggestion was made by a friend regarding the use of CLR and dictionaries in C# - he claims this structure is blindingly fast even for a RBAR client-side execution and that it leaves the classic SQL Server stored procedure choking on its fumes.

  • Caruncles

    Hall of Fame

    Points: 3285

    I also read on an MS web site that the default cursor for SQL Server 2008 is set as GLOBAL.

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    h.tobisch (2/3/2015)


    1. I never use static cursor but cursor variable

    declare @C cursor

    set @C = cursor fast_forward for select * from kd_zp

    would be interesting to see if there is a difference in performance.

    Advantage is, that you do not need to de-allocate them.

    1. best and logically correct way to concatenate results

    select textstring = (select atnummer from lieferant FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')

    I've never used cursor variables and can't speak to the performance differences with static cursors. I try to avoid cursors for procedures I've written, but it is an interesting question. Perhaps one that you could write an article about????

    Thank you for an improvement on my concatenation query. I'll test it and may use it to replace my version. Like I said above, my goal is to make things better than I found them.

  • Brian Hibbert

    Ten Centuries

    Points: 1322

    j-1064772 (2/3/2015)


    This article provides a good INTRODUCTION to a set-based method of eliminating "row-by-agonizing-row" (RBAR) cursor code but it is not the complete story. There is more information to consider BEFORE deploying such code in a production environment.

    The three-part SET method of eliminating the use of cursors has been abundantly discussed in previous articles by people I regard as real experts.

    About two or three years ago different opinions were expressed regarding its use. One side was concerned that theoretically there is no formal guarantee that MS SQL Server will always respect the expected order/sequence of rows. The opposite side consider that in real life, on huge sets, the theoretical problem has never materialized and that dramatic speed improvements have been achieved (several orders of magnitude). Both the main proponent and opponent of this method have consistently demonstrated a level of expertise that is light-years above what I could ever dream of achieving.

    I have no idea if this difference of opinions has ever been settled. Maybe the experts who expressed their opinions would provide a short update of the current situation.

    Another suggestion was made by a friend regarding the use of CLR and dictionaries in C# - he claims this structure is blindingly fast even for a RBAR client-side execution and that it leaves the classic SQL Server stored procedure choking on its fumes.

    Yes, it's only an introduction and I can't claim to be an expert on rewriting cursor based procedures into SET based procedures. (Though I suspect I'll become an expert before I finish with this system).

    My goal was to show a real example of code that could be improved, some simple steps that can be taken to improve it and possibly encourage others to make little changes to improve their systems.

    I suspect your friend's CLR code would be faster in some situations, especially when it's tailored to a specific data set. But often what's fastest in one system is not so much in another. Like everything else, it should be tested on a DEV node with representative data before putting it into production. In my case, converting to CLR is more change than I'm prepared to make at this time for these stored procedures. It would be interesting for someone to do comparisons and write up the results though...

  • chris.mckeever

    SSC-Addicted

    Points: 464

    Your final solution isn't the most efficient way to concatenate the strings. The STUFF function combined with FOR XML PATH would be the best solution. It would look something like this:

    SELECT

    STUFF

    ((

    SELECT ' ' + TaskInstructions

    FROM JobTaskData jtd

    JOIN Task t ON t.Task_ID = jtd.Task_ID

    WHERE jtd.Quote_ID = @hQuote_ID

    AND jtd.VersionNo = @hVersionNo

    AND t.Department_ID = @hDepartment_ID

    AND t.TaskTypeID = 1

    FOR XML PATH('')

    ),1,1,'')

    The existence of cursors in a code base is usually a sign that code was written by procedural-language programmers who "can do SQL". They have a tendency to think in linear terms instead of thinking in sets. SQL is far more efficient when dealing with sets rather than individual rows.

    Long experience with an application that uses far too many cursors has taught me that the only time a cursor is the right answer is if an action taken with one record changes what needs done with another record. In any other situation, find a set-based solution. And if you're sure there isn't a set-based solution, you need to look harder. RBAR (row by agonizing row, or "rebar") is to be avoided wherever possible.

Viewing 15 posts - 1 through 15 (of 40 total)

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