Nice thought-provoking article SQLMickey. Here's a thought for you:
"Let’s take a look at a
stored procedure query written four different ways."
Whilst a junior developer might select one of the four without considering the others, an experienced developer will try all four and compare metrics. An informed choice is always better than a guess.
•Running small queries and inserting the results in temp tables to be used later in the stored procedure. One execution plan will be created and executed for each of these.
Sometimes this is more efficient than integrating the table source into the main query, and in any case, the “extra” execution plan won’t disappear if you integrate the table source into the main query – it will become part of a bigger and more complex execution plan, running the risk of an optimiser timeout. As above, an experienced developer will test, and compare metrics.
•Running small queries and inserting a single value in a variable to be used later in the stored procedure. One execution plan will be created and executed for each of these.
I’ll trade an “extra” execution plan for performance every time. The query optimiser doesn’t always get it right and when it doesn’t, temporary storage structures are usually the tool to use to help it along. Divide'n'Conquer, as Jeff says above.