THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

When To Break Down Complex Queries

 

Some days ago my SolidQ colleague Luca  pointed me to the followin SQLCAT article

When To Break Down Complex Queries
http://blogs.msdn.com/b/sqlcat/archive/2013/09/09/when-to-break-down-complex-queries.aspx

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:

https://connect.microsoft.com/SQLServer/feedback/details/533766/make-noexpand-hint-usable-also-with-cte

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!

Published Thursday, September 19, 2013 5:13 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

Our guiding principles should be cause and effect, not rules to be blindly followed. For example there is no fundamental reason to avoid temp tables over CTE. That said, I do like the cleaner style of CTE over that of CREATE, insert, use, DROP temp table. So back to cause and effect, our objective is to balance the overhead of creating a temp table versus the implicit expansion with a CTE.

There are two reasons for having a temp table. One is if the CTE expression is used repeatedly, both requiring repeated evaluation and also the extra cost of query optimization. The other is to get a good execution plan. If the row estimate of the CTE could is wrong, and this could happen for perfectly valid reasons, such that there are serious consequences (not necessarily just the magnitude of the mis-estimate) the execution plan could also be horribly wrong. So here the true value of having a temp table is the opportunity to get true statistics, if more than 6 rows.

As I said earlier, I do like the clean syntax of the CTE, so if by no expand, you mean implicit temp table, that would be useful for this reasons you stated.

On the matter of the SQL CAT article of when to breakdown complex queries, there is an ISV application called Relativity used in the legal industry for document discovery that just seems to gravitate to expressions that the SQL Server query optimizer cannot handle. I talk about here

http://www.qdpma.com/CBO/Relativity.html

September 19, 2013 1:17 PM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement