Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was asked to test a change to a query, moving a Table Valued Function (TVF) into a CTE (non-recursive). My understanding was that each CTE reference is executed separately, so there was a possibility of the change leading to tempdb contention...

I was very surprised to see the query performance improve when using the CTE. IE:

 ;WITH cte AS (
    SELECT t.col FROM dbo.getValues() t)
 SELECT ...
   JOIN cte c ...
 UNION ALL
 SELECT ...
   JOIN cte c ...

Can anyone shed any light on why this is happening?

share|improve this question
    
    
Also this answer and the comments: stackoverflow.com/a/706983/61305 –  Aaron Bertrand Jun 14 '12 at 4:00
    
See Provide a hint to force intermediate materialization of CTEs or derived tables that shows how this can be achieved using TOP ... ORDER BY and Quassnoi's blog that uses USE PLAN to achieve this. Whether that is relevant to you or not who knows without more details / an actual execution plan. –  Martin Smith Jun 14 '12 at 6:53
add comment

1 Answer

up vote 4 down vote accepted

I think the behavior is that the CTE is "expected" to be evaluated multiple times, but it doesn't always work that way (e.g. it is not guaranteed). There are probably a lot of variables that dictate the behavior one way or the other. Can you share an actual execution plan?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.