• Brian-444611 (11/15/2012)


    you kinda sound like my DBEs 🙂

    but no seriously this advanced search thing has an endless number of parameters and generates 100+ line queries that are just ugly. it's like "lets do a 30 table view" situation

    I'm with you. Queries shouldn't be so sensitive to stats and they shouldn't be "all-in-one" queries that join 30 tables. The use of multiple smaller queries that hold much smaller result sets (maybe even just one depending on the data) in thoughtfully created Temp Tables can greatly reduce resource requirements and cause performance to skyrocket.

    Take a look at the execution plan for one of those queries. If you see huge rows with counts much larger than any of the tables, you probably have a query with some many-to-many joins that could well benefit from "Divide'n'Conquer". A dead giveaway will be if they used DISTINCT (or possibly, GROUP BY) to overcome the problem of duplicated rows in the result set (although you could still have the problem without this particular smell).

    Then, rewrite the query by dividing it up and see if it doesn't perform better while using less of Temp DB in the process. If you can pull it off, use it as a teaching tool to get the devs to write code a bit more thoughtfully. Speaking of "thoughfully", don't scream at the devs about it. They're doing the best they can with what they know. Be a mentor about it teach them a better way. If you can't make the code any better, give them an additional 40GB of Temp DB because there's no room to talk unless you can set the example.

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