Jeff Moden (9/21/2012)
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.
I agree wholeheartedly with this. It is not a magic bullet by any means, but smaller (and more relational) queries are very often easier for the optimizer to reason about. The availability of statistics (on the temporary tables used to hold intermediate results) can also be a big factor. Large all-in-one queries are also generally harder for humans to understand and maintain, regardless of how well you lay them out on screen.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi