Thanks for the article, and for getting us thinking about code performance and readability.
I was struck by something you said:
By doing everything in a single statement, we eliminate the two performance complaints mentioned above.
Often I encounter the opposite: It seems that complex queries that I write often see a performance benefit from being broken down into smaller pieces. Readability sometimes improves as well, but other times suffers as a result.
For example, today I was working on a query that joined a number of tables, and then joined 7 derived table. It was readable, and seemed like it should have performed fairly well. But performance turned out to be unacceptable.
I rewrote this query to create a temporary table, insert from the joined tables, and then perform 7 separate updates (doing the work in the derived tables in separate queries instead of in one big joined query). Performance increased dramatically: say from 30-40 seconds down to <5 seconds. This pattern of materializing resultsets early is one that I begrudgingly am forced to do more often than I would like.
"Premature optimization is the root of all evil"...and is particularly hard on readability. Yet sometimes optimization is needed, and sacrificing elegance and readability is a necessary cost.
On the other hand, some might find a bunch of separate simple updates to a temp table to be more readable than a big query with lots of derived tables. (To. me. it. seems. too. choppy. to. be. optimally. readable.)
I guess my points are that 1) a single query is not necessarily better than multiple queries, and 2) to a certain extent, readability is in the eye of the beholder.