Any suggestions to improve this query. It is taking 20 seconds

Mamado 2015-03-30 08:19:39

SQLkiwi 2015-03-30 17:16:39
Nesting CTEs can be a convenient way to break up a complex query, but you need to avoid referencing a CTE more than once, because it will generally be freshly computed for each reference (rather than reused, as you might have been expecting). This is one of the current limitations of CTEs that means CTEs are generally inferior to using real temporary tables (sensibly).

The other downside of CTEs is that the optimizer gets no extra information about row counts (cardinality) or data distribution (statistics) as it can with temporary tables. Materializing relatively small intermediate result sets is one of the most important techniques to apply when tuning complex queries.

As an example of the first point (reusing CTEs) the following plan fragment is fully evaluated at least twice in the submitted query analysis file:

Repeated fragment

This isn't the end of the world because this batch-mode plan fragment is relatively efficient, but there's no point doing the same work over and over again. Identify the CTE or SQL logic that corresponds to this part of the plan (probably "mi_data") and materialize the 529 rows into a #temporary table.

The other problematic areas of this plan for the most part relate to an incorrect plan choice based on incorrect cardinality estimation. For example, in one area, the optimizer chose row-mode nested loops because 1 row was expected, whereas almost 10 million were encountered at run time.

I'm not suggesting you materialize every mis-estimation point in this plan in a #temporary table, just that you need to incrementally improve the query using this technique where necessary and beneficial. You will want to focus on improving join choices and eliminating the sort spill, for example.

Removing the duplicated work, and improving the performance-affecting cardinality mis-estimations should produce a very meaningful improvement, if the technique is applied with skill and care. The final advantage of breaking the query up that I want to mention is that it makes identifying which part of the query is taking the longest, helping you focus your tuning effort.

Mamado 2015-03-31 10:02:42
Thanks. The issue with #temp tables is that it will take more time to write to tempdb disks and the query needs to be executed as one batch so at the end i am increasing the execution time.
Aaron Bertrand 2015-03-31 11:22:57
I'm not sure you can accurately predict that the I/O required to materialize 529 rows to disk will make your overall runtime longer – it is likely that it will more than offset the amount of time currently spent working around the fact that those rows are not materialized.
SQLkiwi 2015-03-31 11:54:42
@Mamado I can only you suggest you try it. You may be surprised by the benefits, even if you don't yet fully understand why. Small #temporary tables may be built and maintained entirely in buffer pool memory, resulting in zero physical I/O (they are not persistent or recoverable objects).
Mamado 2015-03-31 12:44:04
@SQLkiwi i have done that and got a slight improvement (-3 seconds). I will continue with this technique if possible as you suggested but at the end mi_data is the one causing most of the wasted time as it has to be scanned many times and considering its size it is not a good idea to materialize it.
SQLkiwi 2015-03-31 13:01:04
@Mamano You're supposed to materializing the 529-row intermediate result as shown in the graphic in my answer. You say "considering its size it is not a good idea to materialize it". Those two are not compatible – what are you doing?! 🙂

Anyway, this is all going beyond the scope of this site. The answer is the answer; if you need help applying it, you may need to engage some external help or training.

Mamado 2015-03-31 13:48:04
@SQLwiki I forgot to tell you that the 529 intermediate result is coming from "calc_mi" and not "mi_data" as you mentioned. mi_data equivalent results is more than 18M records and calc_mi is not referenced as much as mi_data.

Thanks for your contribution anyway.

SQLkiwi 2015-03-31 14:13:50
You're welcome.