Post reply

The Numbers Table

  • November 28, 2008 at 4:15 pm

    #905001

    jacroberts (11/28/2008)

    My original actual query was a lot different and maybe I've over simplified it for want of a simple example.

    I agree and the change you made seems to have also changed the basic logic of what they had done. It's entirely possible that they had formed an inadvertant triangular or cross join that produced millions of "internal" rows and the change you made, somehow resolved that.

    Going back to the code and data you posted in response to my questions... it's obvious to me that you used the same code I posted and that's what I wanted to know so we can compare apples to apples. I actually expected the RBAR and the set based code to work within milliseconds of each other because it was my understanding that the TVF would calculate just a single time because it's in the FROM clause as a derived table. That, notwithstanding, let's review what we know...

    1. We just proved that the performance problem had nothing to do with the size of the date table they were using.

    2. We just proved that a set based solution was never the problem and caused no performance problem. Improperly written code that looked set based may have been the problem.

    3. We are (or, at least I am) speculating that your change to the RBAR function was more a symptomatic fix of their problem because you may have resolved an accidental non-linear join and that a similar fix using proper set based methods probably would have also fixed the problem.

    4. I have to go back through Peter's tests and see what's up with those, but I suspect we'll find what has happened on all other machines... either the RBAR performs horribly slower, or it approximately ties within some miniscule fraction of a second owing only to the extremely low row count of 31. The "tie" is more what I would have expected because I was under the impression that TVF's used as derived tables would only be resolved once.

    5. We still don't know why the "tie" occurs on jacRoberts machine, but doesn't on some other machines. This is particularly confusing for me because jacRoberts is using the "Standard Edition" and I'm using the supposedly more robust "Developer's Edition" which is actually the "Enterprise Edition" with nothing more different than "licensing".

    While it would still be interesting why jacRobert's machine seems to have correctly resolved the TVF as a single result set and why mine did not (remember... IDENTICAL CODE was used on both), it's actually become further proof that RBAR should never be used. On some machines, it'll work just fine for this very limited requirement of only 31 rows... on others, the RBAR code takes a relatively huge amount more. Further, if some poor developer, caught in the squeeze of producing something on time, finds the RBAR code and uses it in an inappropriate manner rather than for generating only 31 rows on a very repeatable basis like a split for a large batch file or multiple long winded splits on parameters from a GUI, (s)he'll be in deep Kimchie when it comes to performance and scalability.

    So, the bottom line is, jacRoberts was right about his fix... despite it being RBAR, it worked for him just fine because it correctly resolved only 1 time on his machine and for only 31 days. We proved in a small million row example that proper set based code has the same effect on those small number of rows. I'd use the method that worked with some good performance on both machines... that means I'd recommend not using the RBAR solution... ever.

    jacRoberts and Peter... thanks for hanging in there on this very long thread.

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

  • December 1, 2008 at 12:17 pm

    #905842

    Reading through this thread after the long w/e, I think people might be taking certain absolute conclusions about the way SS05 operates, specifically regards the optimizer (or lack thereof in some cases).

    Some of you might be interested to revisit this old threadlet, discussing TVFs and RBAR, starting with a post I made: http://www.sqlservercentral.com/Forums/FindPost456536.aspx

    The End.

Viewing 2 posts - 106 through 106 (of 106 total)

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