Query execution time

Arvind 2016-05-04 05:32:37

The query attached is throwing following error and couldn't execute.
"Msg 8623, Level 16, State 1, Line 1 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query".
We have enabled trace flag 8780 to increase the query optimizer timeout setting and the query is executing now but taking long time.I checked the actual plan and see that estimates are way too high for this query even though the stats are updated for all tables in the query.I see high cost incurred on the sort operator.Can we bring down the execution time of this query?

SQLkiwi 2016-05-10 11:19:19
As the error message said, the problem is the large number of tables or partitions in the query. Fundamentally, the plan contains very many repeated (or very similar) operations.

There are 13 identical subtrees feeding into the Concatenation operator. Each of those 13 identical subtrees contains multiple trees of identical joins, which in turn (!) contain multiple subtrees of repeated 7-ways joins. There is another subtree of 30-odd joins that I didn't bother to count exactly. And remember, all of this is going on under each of the 13 inputs to the Concatenation operator.

This is a quite typical result when features like Common Table Expressions and Views are used inappropriately. Joining views to views to views … and so on … is one of the easiest ways to produce stupidly large and needlessly inefficient execution plans.

The definitions for the views weren't supplied; one can only imagine what they might contain.

The problems you are facing have nothing to do with cardinality estimates or the estimated cost of the sort. The problem is simply one of basic query design. Though the top-level query looks nothing too out of the ordinary, as an exercise, you should try replacing all the view references with the definitions of the views, recursively, until the query references no views at all. This is exactly what the query optimizer has to do before it even starts looking for a physical execution plan. If you have the patience to do that, the root of the problem should become stunningly obvious.

The final plan contains 1,429 Hash Match operations (mostly joins), 152 Nested Loops Joins, and a Merge Join. It's no wonder the optimizer took over 6 minutes to find any sort of execution plan at all. I simply cannot believe anyone would open that plan and conclude that the main problem is a Sort of 2,234 rows at the end.

You will need to revisit your design and avoid writing queries that join complex or nested views a huge number of times. If you are also using Common Table Expressions in the same way, or referencing the same CTE multiple times in the same query, you will need to stop doing that as well.

The best advice I can offer you at this point is to avoid the view nesting. Then do the very thing the error message said: simplify the query. Break it up into humanly-comprehensible sized parts. Use temporary tables sensibly to materialize small, narrow intermediate result sets, and avoid performing the same work hundreds or thousands of times.