Row-By-Row Processing Without Cursor

  • RBarryYoung

    SSC Guru

    Points: 143327

    Jeff Moden (4/30/2008)


    See the following URL... almost identical situation...

    http://www.sqlservercentral.com/Forums/Topic491969-149-1.aspx#bm492576

    Jeff:

    FYI, the "Post #" links like this do not seem to be working, they just take to the beginning of the Topic. When I use the Post-links on the lower left-hand side of the posts, it gives me something like this: http://www.sqlservercentral.com/Forums/FindPost492576.aspx which seems to work better.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jeff Moden

    SSC Guru

    Points: 993897

    Funny, they work fine for me... they take me right to the post I wanted.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993897

    poornima.s_pdi (5/1/2008)


    Hi Jeff,

    Thanks a lot for ur reply.I learnt more from ur sites.very good snd useful forum...

    Thank you for the feed back, Poornima... are you all set now?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • poornima.s_pdi

    SSCrazy

    Points: 2580

    Hi,

    Your post satisfied my need.Once again 'Thank u' for ur timely help.

  • RBarryYoung

    SSC Guru

    Points: 143327

    Jeff Moden (5/2/2008)


    Funny, they work fine for me... they take me right to the post I wanted.

    I think that it has to do with paging. Anchors, like page#postnumber won't work if the topic spans multiple pages and the target post is not on the first page. Since "Posts per Page" is customizable, it might be paging differently for us.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jack Corbett

    SSC Guru

    Points: 184296

    Jeff Moden (1/2/2008)


    The real point here is that you should not be using any form of RBAR in a trigger... no matter how you do it, calling a RBAR proc from a trigger is an insane thing to do... the proc should be rewritten to handle sets of data instead of the slothful agony of single row processing. 😉

    Jumping in here way late, but there is no doubt that RBAR processing whether a cursor or while loop in a trigger is a very bad idea. You are better off staging the data and processing it in a separate process whether that's Service Broker or another option.

    Something I do not recall seeing mentioned is that you can do a Select Into temp_table from inserted\deleted then access that temp table in a stored procedure called from the trigger where you can do some set-based processing, of course that's only necessary if you want to encapsulate the code so you can re-use it because you can process it the same way in the trigger.


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DragonGod

    Old Hand

    Points: 396

    Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

  • Jeff Moden

    SSC Guru

    Points: 993897

    Roy Oliver (9/12/2009)


    Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

    Why? They are inherently RBAR. "For Each Row".

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • DragonGod

    Old Hand

    Points: 396

    Jeff Moden (9/12/2009)


    Roy Oliver (9/12/2009)


    Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

    Why? They are inherently RBAR. "For Each Row".

    True, yet isn't the performance faster than the SQL Server Cursor?

  • Jeff Moden

    SSC Guru

    Points: 993897

    Roy Oliver (9/14/2009)


    Jeff Moden (9/12/2009)


    Roy Oliver (9/12/2009)


    Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

    Why? They are inherently RBAR. "For Each Row".

    True, yet isn't the performance faster than the SQL Server Cursor?

    I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.

    Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • DragonGod

    Old Hand

    Points: 396

    Jeff Moden (9/14/2009)


    Roy Oliver (9/14/2009)


    Jeff Moden (9/12/2009)


    Roy Oliver (9/12/2009)


    Interesting... it'd be easier if SQL Server implemented triggers in the same way Oracle did.

    Why? They are inherently RBAR. "For Each Row".

    True, yet isn't the performance faster than the SQL Server Cursor?

    I don't know, Roy. Comparing anything in Oracle with SQL Server is pretty hard to do. A better question might be, are they faster than an Oracle Cursor? ... and the answer is "I don't know for sure in Oracle" because I've never tested triggers vs set-based code in Oracle, but I don't believe so. I can say I have tested Cursors vs Set-Based in Oracle... properly written set-based code blows cursors away even in Oracle. It's pretty much a myth that Oracle has been "optimized" for cursors so far as speed is concerned.

    Most RDBMS's work best with Set-Based code... it would be a real shame if they changed the current set-based mechanism built into SQL Server triggers into similar RBAR code as they did in Oracle if for no other reason other than to simply NOT get into the habit of writting RBAR anywhere.

    Yeah, that's a good point.

    Thanks

Viewing 11 posts - 61 through 71 (of 71 total)

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