New answers tagged cte
Looks like the issue in your code is multiple access to the CTEs and all the complicated logic you have in there. You should try to do everyting in a single pass with UNPIVOT/PIVOT. I did something similar here: http://spaghettidba.com/2011/10/13/concatenating-multiple-columns-across-rows/ In my example you will find the concatenation of sentences in ...
If you believe that FOR XML PATH is the culprit that slows down the view, you can use a different approach to concatenate rows into a single column. This SQL Fiddle demonstrate how to use a recursive CTE for concatenation. Just insert cte1, cte2, cte2 and cte4 between your thing1 and thing2, and then just use the Invoice, Invdate, InvTot columns from cte4 ...
It sounds like what you have is an EAV pattern (so that block of code is mostly joins to the same take) that you need to transpose for reporting. If that is the case then you might find the PIVOT feature can make those CTEs more efficient (both in code readability and execution run time): http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Top 50 recent answers are included