CTE RUNNING SLOW ON LEFT JOIN

Question

I have a view that has several CTEs in it. The CTE individually works fine. However the last past of the CTE Where it is doing a LEFT JOIN on 2 ctes i.e cte4 AND CTE5, that takes foreover to run and doesnt even stop, only 200 recs are in the CTE. The LEFT JOIN in the end on 2 ctes i.e cte4 and cte5 is causing the slowness. The things is I cant even use #temp tables since this is from a view. Any suggestions/alternatives? sample code below WITH cte AS ( SELECT DISTINCT
P.ACCT ... ), cte2 AS ( SELECT DISTINCT CTE.ACCT .... ),

cte3 AS ( SELECT DISTINCT CTE2.ACCT --- FROM cte2 LEFT JOIN ( SELECT --

			(	SELECT 
					..
					FROM (
							SELECT 
							 ...
							FROM.. WITH (NOLOCK)
							
						) A
					WHERE Tier_00 = 1
			)										C	ON...
			WHERE..
					AND (..	)
		)	..										ON	..

),

cte4 AS ( SELECT DISTINCT ACCT = cte3.ACCT .. FROM cte3 LEFT JOIN ( SELECT .. FROM cte2

			LEFT JOIN 
					(
						SELECT 
						 ..
						..
						FROM  .. P
						LEFT JOIN..
						LEFT JOIN 
							(
								SELECT
								 ..
								FROM (
										
										SELECT 
										  Tier_02 =..
										FROM .  R WITH (NOLOCK)
										

									
									)   A
								WHERE..
							)											C		
									ON..
						WHERE	..
								AND (	..)
					)	RevBase ON	...
					GROUP BY..
	) 
	.. ON (..) 

),

cte5 AS ( SELECT DISTINCT ..CTE4.ACCT AS ACCT AS pvt

)

// /*Final Select This part has issue and is VERY SLOW */ // SELECT
DISTINCT ACCT =a. ACCT ...FROM cte4 A LEFT JOIN cte5 B ON A... = B...

GO

Answer 1

It is impossible to give detailed advice on a query that we only see fragments off.

But, generally, when you have many CTEs, it can sometimes help if you materialise some intermediate CTE into a temp table, and then work from there.

When you have complex queries, the optimizer can easily go wrong. When you stick in a temp table with statistics, the optimizer has more information for the rest of the steps. Watch out that this cuts both ways. The CTEs are logical building blocks, and not necessarily computed as such, and the optimizer may recast the computation order. So if you have a temp table in the wrong place, execution time can increase.

If you are referring to the same CTE multiple times in a query, that is definitely a good candidate for a temp table, because SQL Server will expand the definition of the CTE for every occurrence, so there will be double work, one way or another.

Answer 2

Hi @Desigal ,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data and the expected result of the sample.

In addition, please also provide the actual execution plan (e.g. run the query with "include actual execution plan" turned on.) if possible which is very helpful to us so that we could provide more helpful suggestions.

Based on your limited information provided, you could have a try to use template tables instead of multiple CTEs like below:

;with cte as (...),  
cte1 as (...),  
cte2 as (...),  
cte3 as (...),  
select DISTINCT  
ACCT = cte3.ACCT  
..  
into #temp1   
FROM cte3  
LEFT JOIN ...)  
  
select DISTINCT  
..temp1.ACCT AS ACCT  
AS pvt  
...  
into #temp2  
from #temp1  
...  
  
SELECT  
DISTINCT  
ACCT =a. ACCT  
...FROM #temp1 A  
LEFT JOIN #temp2 B ON A... = B...  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The alternative would be a stored procedure, but you cannot use a stored procedure in the same way as a view.

You could also make it a multi-statement table-valued function. You cannot use temp tables in functions, but you can use table variables. They are not equally good, since they don't have statistics. But at least you can materialise CTE4 in a table variable, so it does not have to be computed twice.

Maybe it is possible to rewrite the query, so that you don't have to use CTE4 twice, but without no knowledge of the query and what it actually does, I cannot say how that would be done.

Answer 4

Hi @Desigal ,

Thanks for your update.

You could consider to create necessary clustered/non-clustered indexes on all related table(s) in your CTEs.

Please also update statistics of all tables if possible to make sure the statistics of all tables are up to date.

You could also check whether there is any blocking/submitted transaction/deadlock in your related tables which might also cause the performance issue.

After all, you could provide your execution plan and DDL of all related tables, then we could check whether we could rewrite or enhance your query.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.