I am working with common table expressions and observing what I would consider abnormal behavior. I have created a script that initializes a CTE that contains 22,000 plus rows of data. Later in the script I perform multiple (4) queries filtering the data in the cte. All the resultsets from the 4 queries are returned in one result set via union all clause. This script takes minutes to return the final dataset. I then wrote the same script using a temporary table in place of the cte. This script runs and returns the final result set in seconds.

I would expect the script using the cte would run faster than the on using a temporoary table. the questions I have is:

1. Is there an optimum number of rows in a cte after which performance stats to degrade?
2. Is there a server setting that will optimize SQL server performance for cte's?

I've found the same problem with CTEs. I'd love to hear from someone who has got them to perform reasonably with  'production-size' data.

I found very interesting information in it and it is really helpful i think.