I wish I had more time to write this out more cogently but be advised that you are always going to have overhead regardless of your temp storage solution. Perhaps you should investigate the query that retrieves that information to see if it can be made more efficient before exploring how it's stored for the subsequent queries.
In our shop, my DBA pointed me to using CTE's as well. The funny thing about it was that I was typically storing large amounts of data and switching to a CTE actually diminished the performance of my queries, so be sure do some tests before you completely switch over a large procedure.
It's important to be aware of how much room your temp tables have available when you are seeing slow downs for a temp table. If you have plenty of room for the temp tables then they will typically be plenty efficient. They are usually the right solution when you have a large amount of data to store because depending on how much memory is available to SQL Server, you can quickly and easily fill up the SQL Server's available memory when using a cte which would then totally kill any performance gain you would experience by being in memory. Conversely, if you only had a list consisting of a few columns with a minimal number of tuples then the CTE would be a better way to go.
May 25, 2010 at 10:40 AM