Post reply

Which Rows in Cursor?

  • June 12, 2009 at 12:58 pm

    #206257

    Hi. I didn't realize that if you add a row to the underlying table while a forward only cursor is running off that table, that the new row is in the cursor! That is, the # of rows in the cursor when you began is different than when it finishes. It may seem obvious but it blew us out of our processing yesterday.

    Table Numbers has Column Val with values from 1 to 10000. While running a cursor thru there:

    Insert Numbers Values (20000)

    It's in there. But our testing yesterday seemed to show that if we let it run awhile until it gets to the higher values and then:

    Insert Numbers Values (10) --A low number

    it doesn't get in there. So I'm not sure of the effect of Order by in this situation.

    I guess for the cursor to be immune from underlying table changes SQL would have to put it in memory or tempDB and work from it there. Maybe impractical. How would Update ..... Where Current of work?

    Anyhow, this surprised us.

    Ken

  • June 12, 2009 at 2:20 pm

    #1008890

    You can use a STATIC cursor instead.

    See BOL.

    But I couldn't say that without also advising you to look at the numerous articles on this site about cursors and how to avoid them.

  • June 12, 2009 at 2:24 pm

    #1008892

    Just have a look to MSDN:

    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.

    DYNAMIC

    Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

    Anyway, the best way to handle this would be to get rid cursors ;-). There are some great articles by RBarryYoung which reflect the problems of cursors and how to remove them on this site.

    Flo

  • June 12, 2009 at 2:26 pm

    #1008893

    Tom Brown (6/12/2009)

    You can use a STATIC cursor instead.

    See BOL.

    But I couldn't say that without also advising you to look at the numerous articles on this site about cursors and how to avoid them.

    You beat me... 🙂

  • June 12, 2009 at 2:40 pm

    #1008900

    Florian Reischl (6/12/2009)

    Tom Brown (6/12/2009)

    You can use a STATIC cursor instead.

    See BOL.

    But I couldn't say that without also advising you to look at the numerous articles on this site about cursors and how to avoid them.

    You beat me... 🙂

    I'm quick on the draw tonight 😎 http://www.last.fm/music/Ennio+Morricone/_/A+Fistful+of+Dollars

  • June 12, 2009 at 2:47 pm

    #1008904

    Tom Brown (6/12/2009)

    Florian Reischl (6/12/2009)

    Tom Brown (6/12/2009)

    You can use a STATIC cursor instead.

    See BOL.

    But I couldn't say that without also advising you to look at the numerous articles on this site about cursors and how to avoid them.

    You beat me... 🙂

    I'm quick on the draw tonight 😎 http://www.last.fm/music/Ennio+Morricone/_/A+Fistful+of+Dollars

    Maybe I need more coffee...

    http://www.youtube.com/watch?v=b0Ia-la8rCQ

    :hehe:

  • June 12, 2009 at 9:23 pm

    #1008985

    Ok... problem solved... you think. A better question is, what are you using a cursor for?

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

  • June 15, 2009 at 9:12 am

    #1009712

    Thanks Florian, seems I missed that when looking thru BOL. Will try a static cursor.

    Ken

  • June 15, 2009 at 9:17 am

    #1009718

    Jeff, thought you might reply to this :-D. I've read a number of your posts trying to wean us off cursors.

    I think we're on solid ground here as I need to execute a half dozen SPs using data from each qualifying row in the underlying table as parameters. Seems like a procedural process to me but maybe I'm bias coming from that type of background.

    Ken

  • June 15, 2009 at 9:24 am

    #1009725

    Let me cover you before Jeff starts throwing pork chops... :hehe:

    Cursors are fine (well I prefer WHILE loops) for administrative jobs and this kind of cheat scripts - in my opinion. 😉

    Flo

  • June 15, 2009 at 10:01 am

    #1009745

    Heh... Flo is correct... certain administrative code certainly justifies the use of Cursors and/or While Loops. I'm not so sure that Ken's application requires it because I simply don't know enough about it nor whether the Cursors are, in fact, part of a design or performance problem. So, no high velocity pork... yet. 😉

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

  • June 15, 2009 at 2:47 pm

    #1009921

    Well, we have an Asp.Net page that queues a row into a table for processing whenever a user makes a change to mapping data for a call center. When they do, the actual call center data needs to be re-summarized a number of ways. This procedure is run in background every 15 min. and does this summarization. Not to labor you with too much code but here is our process:

    Declare curRollupDetail Cursor Static For

    Select CallCenterId, ServerId, SiteId, RollupSubmissionTime, EffectiveDate From RollUpHistory Where Status = 0 And RollUpEndTime Is Null

    --Small result set because being run every 15 min

    Open curRollupDetail

    Fetch Next From curRollupDetail Into @CallCenterId, @ServerId, @SiteId, @RollupSubmissionTime, @EffectiveDate

    Set @BeginTimeKey = Convert(Varchar, @EffectiveDate, 112)

    WHILE @@FETCH_STATUS = 0

    BEGIN

    BEGIN TRAN

    BEGIN Try

    Update RollupHistory

    Set RollUpStartTime = GetDate()

    Where CallCenterId = @CallCenterId And RollupSubmissionTime = @RollupSubmissionTime

    --Cursor vars used a lot

    EXEC UpdateAcdFact @ServerId, @SiteId, @EffectiveDate

    EXEC procACDRollup @CallCenterId, @BeginTimekey

    EXEC UpdateAcdFactHH @ServerId, @SiteId, @EffectiveDate

    EXEC procACDRollupHH @CallCenterId, @EffectiveDate

    EXEC UpdateTCD_Fact @ServerId, @SiteId, @EffectiveDate

    EXEC procTCDRollup @CallCenterId, @BeginTimeKey

    Update RollupHistory

    Set Status = 1, RollUpEndTime = GetDate()

    Where CallCenterId = @CallCenterId And RollupSubmissionTime = @RollupSubmissionTime

    END Try

    BEGIN Catch

    Exec usp_GetErrorInfo @ServerId

    Rollback Tran

    Return

    END Catch

    Fetch Next From curRollupDetail Into @CallCenterId, @ServerId, @SiteId, @RollupSubmissionTime, @EffectiveDate

    Commit Tran

    END

  • June 15, 2009 at 3:01 pm

    #1009926

    That may actually be ok... it depends on how many rows you're processing with things like the following...

    EXEC UpdateAcdFact @ServerId, @SiteId, @EffectiveDate

    How many rows does that sproc process for any given iteration of the cursor loop?

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

  • June 15, 2009 at 3:09 pm

    #1009931

    [font="Verdana"]Aside from issues in design for stored procedures that only accept one "row" of data...

    I'd take a copy of what you need to process and put it in a temporary table or table variable (depending on the number of rows you normally process). Then scan through that copy.

    If you were working in SQL Server 2008, you could refactor the stored procedures you are calling to take the entire set in one go by passing the data as a table variable parameter. Actually, you should be able to do that anyway! Just use a global temporary table.

    You don't actually need to use a cursor for that sort of loop, by the way. But however you do it, that sort of looping is inefficient. If all you need to do is bug fix, that's fine -- stay with what you have and make it static. But if you get a chance, refactor the design so the code works with sets. You will solve a lot of performance and scalability issues by doing so.

    Oh, and by the way... your error handling is broken. If you get a rollback, the subsequent commit will fail.

    [/font]

  • June 15, 2009 at 3:25 pm

    #1009934

    Thanks for looking at this. I'll take a look at table vars and error handling. These procs operate on anywhere from several hundred rows to a worst case scenario of 100k+ rows. It can chug along for a few min.

    Ken

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

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