Home Forums SQL Server 2008 SQL Server Newbies How Do You Keep The "Pieces" Of A Complex Statement "Organized" RE: How Do You Keep The "Pieces" Of A Complex Statement "Organized"

  • I not only use formatting to help organize things, I also include comments for every subquery. I also make sure that every column returned by every query and subquery have proper table aliases and that the aliases actually mean something especially when self-joins are present.

    Depending on the performance of the code, I may also break the code up into smaller and tighter pieces and store the interim results in a TempTable or two so I don't have to work with such large data sets. Sometimes, the performmance improvement is quite dramatic. I recently took such an "all-in-one" query (35 tables joined including some self joins) that virtually crippled the server it was running on for 45 minutes and divided it up in such a manner. The code now runs so fast that it usually doesn't even show up on the Task Manager performance chart.

    Getting back on topic, the splitting up of the code also allows for more pertinent comments for each section of the code.

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