We have a warehousing stored procedure that creates #temp tables (some of these have up to 100k rows), then creates indexes on them and uses them to join to other #temp tables or regular tables (some of regular have up to 15M rows on them).
I do not know if it worth the effort to convert the #temp tables to regular tables.
Hence my questions:
There is no difference in the way SQL Server deals with temp tables and regular tables in terms of how to join on them etc. You can basically see them as regular tables inside your connection context, the only special ability they have, is to be automatically dropped when the connection is closed.
Temp tables receive the same "attention" as regular tables in terms of index and stats maintenance, so I don't see how you would get a direct benefit just by changing them to regular tables.
Where you may see a difference is in terms of where load on the I/O subsystem could be spread. The temp tables are created in tempdb, so they may be created, populated and queried on different disks than the regular tables in a user database. That may give you a performance advantage, but maybe not. That depends on your system's hardware configuration.
Another point to consider is that tempdb is a "scratch pad" for a lot of other processes. Memory spills, index rebuilds (potentially), spooling, online index operations, row-versioning and some other stuff. These things could cause the temp tables inside tempdb to perform worse than regular tables. Again, it depends on your system though, as some of these things may be negligible or non-critical.I'm sure there are some other things to consider, but basically you will have to run some tests and baselines to see what is best for you, in this exact scenario, with the load/system usage etc. taken into account.
answered Dec 09, 2011 at 07:10 AM