• Reginald J Ray Jr (1/16/2008)


    Derived tables are cool as far as being able to write elegant code and reduce the quantity of code. I started using them for these reasons.

    I have been removing them since I realized that they are performance dogs for decent-sized tables. Table variables or temporary tables are much better from a performance aspect.

    Have you tested them fully, or just gone by Estimated Cost? Table variables have a problem with the estimated cost in execution plans, and often give a lower number than what they would have if the server knew how many rows they would have before-hand (table variables don't have statistics in them, so they are estimated in cost as if they were 1 row).

    The reason I ask is because in a large number of cases, using Profiler and other tracking and testing tools, I've found that derived tables are sometimes faster and less resource intensive than table variables or temp tables, and sometimes are slower and more resource intensive. Your statement implies that they are always worse. Actual performance seems to depend mostly on indexing and the number of joins being used.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon