Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Solving the Running Total and Ordinal Rank Problems (Rewritten) RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

  • November 10, 2009 at 9:22 am

    #1077345

    Alexander Kuznetsov-291390 (11/10/2009)

    These are incredibly smart techniques, but after playing with such approaches I decided that I don't want to put it into production - they are just a little bit too complex and they do not look robust to me. In my book this is one of those cases where denormalization allows for much faster and significantly simpler solutions:

    BTW, Adam Machanic recommends CLR cursors in such cases...

    First, thanks for taking the time to stop by and post your thoughts, Alexander. I really appreciate it.

    I'm not sure why you find the "Quirky Update" a bit too complex nor why you don't think they look robust, but that's OK. Your good method will nicely support ongoing running balance updates on insertion of new data, albeit, in a RBAR manner. I'll add some performance testing using your good method to my list of things to do.

    To be sure, your method is very clever... I like it. I just think it will be a bit slow because of it's RBAR nature.

    And, yes, I know Adam recommends using a cursor for this... so do a lot of other good folks. If you don't trust the "Quirky Update" you can do as I suggested in the article... use verification code to verify it worked correctly or do like Adam and others suggest.... use a cursor and tolerate the performance and resource usage hit on large batches. 😉

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