sivapragasam2000 (5/6/2013)
Old topic but still relavent...In my previous job, I always used CTEs and they were much faster when compared to temp tables.
In the current place, strangely enough Temp tables almost always win by a mile.
Trust me; what I say is true.
Otherwise, in both places I have been writing Procs for SSRS reports against very similar databases in terms of volume, size and indexes.
From a processing engine stand point would it matter as to how servers are configured, so as to differ in how CTEs and Temp table execution is considered.
If you posted this as a new question you'd get a lot more answers.
In a nutshell, yeah, the server configuration matters. Faster/more memory, faster/more CPU, faster/more disks or the opposite absolutely have an affect on queries. That's why so many people just throw hardware at query problems rather than try to take the time to understand the fundamental issues. It really is easier, but usually far less effective.
As to the specifics of your situation, why would a CTE or a temp table be faster? It completely depends on the underlying structures, the indexes, the code, the statistics, and yeah, hardware. Without knowing more I couldn't hazard a guess. But remember that CTE are nothing but queries. No data is stored or special statistics are generated. Temp tables are data storage with statistics. Those differences in behavior will lead to different behaviors of queries in different circumstances.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning