Solving the “Running Totals” Problem in SQL Server 2005 with Partitioned Tables

  • Lynn Pettis

    SSC Guru

    Points: 442360

    I didn't test Jeff's code against just one partition, but from what I saw of the data when run against the whole table, I'd have to say that in that case the results may be what you would be looking for. This would be a good test for someone to try.

    It may be a while before I can, as I have some other things going on right now outside of work that are taking up quite a bit of my free time.

  • Paul DB

    SSC Eights!

    Points: 840

    Wooh ... this is some article. Great job! 😎

    Paul DB

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Paul DB (1/28/2009)


    Wooh ... this is some article. Great job! 😎

    Thank you. :blush:

  • RBarryYoung

    SSC Guru

    Points: 143327

    You know, I've been so busy the last month or so, I missed this when it first came out. This is an impressive piece of work, Lynn, congratulations. I am sure that I will be referring to it in the future.

    [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]

  • Lynn Pettis

    SSC Guru

    Points: 442360

    RBarryYoung (2/21/2009)


    You know, I've been so busy the last month or so, I missed this when it first came out. This is an impressive piece of work, Lynn, congratulations. I am sure that I will be referring to it in the future.

    Thank you. :blush:

    I do have to give credit to Jeff for the groundwork he laid in his article, and to Gail for the idea based on her comment on Jeff's work about the "quirky update" not working with partitioned tables. That was my incentive to find a viable solution.

  • jcrawf02

    SSC-Insane

    Points: 24198

    Grant Fritchey (1/27/2009)


    Uh... Wow... Over-achieve much?

    Ha! This coming from a guy who wrote 180 pages on execution plans.

    And Lynn, I concur with all of the above who congratulated your article, very nice writing and explanation of the process.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Johan Bijnens

    SSC Guru

    Points: 134303

    As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.

    First findings .... It no longer works .... SQL2008 ( sp1 ) is getting smarter.

    The more things we try to fool it, it just ignores them.

    They must have a huge pile of fools, to anticipate all the foolish things we try 😀 (forcing a sets processing in a certain order.)

    I'm still trying to figure out why ..... or why not.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • Lynn Pettis

    SSC Guru

    Points: 442360

    ALZDBA (4/27/2009)


    As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.

    First findings .... It no longer works .... SQL2008 ( sp1 ) is getting smarter.

    The more things we try to fool it, it just ignores them.

    They must have a huge pile of fools, to anticipate all the foolish things we try 😀 (forcing a sets processing in a certain order.)

    I'm still trying to figure out why ..... or why not.

    Okay, I really need to get SQL Server 2008 Developers Edition. Have you tested the ORDER BY on the partitioned table itself?

  • Jeff Moden

    SSC Guru

    Points: 997170

    Actually, it only looked like the ORDER BY method worked in my article. It wasn't the ORDER BY that made it work. Of course, I'm just a day or two from submitting the article for republishing and I don't want to give away too much thunder. 🙂

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • RBarryYoung

    SSC Guru

    Points: 143327

    Lynn Pettis (4/27/2009)


    ALZDBA (4/27/2009)


    As you suggested the "Order by" testing with Jeffs solution, I also tested your marvellous solution for partitioned objects.

    First findings .... It no longer works .... SQL2008 ( sp1 ) is getting smarter.

    The more things we try to fool it, it just ignores them.

    They must have a huge pile of fools, to anticipate all the foolish things we try 😀 (forcing a sets processing in a certain order.)

    I'm still trying to figure out why ..... or why not.

    Okay, I really need to get SQL Server 2008 Developers Edition. Have you tested the ORDER BY on the partitioned table itself?

    Lynn, try here. $45 is pretty good, and because I'm an Amazon Prime customer, I get the 2-day shipping free.

    [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]

  • WayneS

    SSC Guru

    Points: 95392

    Lynn,

    I can't believe that I missed this article when it came out. This is a fantastic article that I've put into my briefcase.

    Fantastic job.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 11 posts - 31 through 41 (of 41 total)

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