Some days ago my SolidQ colleague Luca pointed me to the followin SQLCAT article
When To Break Down Complex Queries
that, as part of the reorganization of SQL CAt website, has emerged again from the depth of Internet.
The article is very interesting and also allows me to underline again the importance of having a way to tell the optimizer when it should *not* expand subqueries into the main query. In this way the usage of #temp tables could be avoided in many cases, allowing for the usage of views or inline UDF instead of stored procedures.
I opened the connect item back in 2010, so it’s time to bring more attention to it in order to have it in the product ASAP, and this article give me the option to do that.
Please vote for it:
Now that data volumes are increasing every day, more and more I find myself fighting with bad query plans due to bad cardinality estimation. When you are joining several big tables, you can be sure you’ll have to break your query in several pieces, to be sure to have good performance. Last time I had to do this was….the day before yesterday!
I think it’s really time for an extended NOEXPAND hint. If you’re with me, vote vote and vote!