CTE versus #TEMP Tables


  • We have a Stored Procedure with 20+ Common Table Expressions. Just easier to take timely extracts, 2-month info, 3-month info, 6-month info, 12-month info, etc...

    The query runs real smooth until the final query in which we are joining two CTEs together. Then it just completely bogged down and never completed. Once we changed the last couple of pieces to create #Temp Tables rather than CTEs and joined on the #Temp Tables, that solved the problem.

    Why? Did we reach some sort of threshold in memory with all of these CTEs and it just choked on itself? Can anyone offer any kind of insight onto this?

    Thanks for your review and am hopeful for a reply.


    Friday, November 01, 2013 5:23 PM


All replies