Any suggestions to improve this query. It is taking 20 seconds
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:
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.