Unanswered: Temp table vs permanent table - performancewise
Is there a difference in performance when using temp or permanent tables? If so under which conditions?
I added a significant mod to an existing stored proc. I create temp tables in this mod (using 'select into') wherever a staging table is needed for pre-processing. Subsequently, last step of the mod is to update a permanent table with values from a temp table.
As a result, I got a 30 min increase in runtime of the proc. I'm still not sure if it is because there were other processes contending for resoueces, but wanted to square away the possibility of using temp tables affecting the performance.
Thanks to all!
P. S. I already searched the forum to answer to this question, but ...
1. Lot's of contention on system tabels in temdb. Really only an issue if you are creating/dropping lot's of tables in a short amount of time. For example, if this proc was run each time someone came to a web site and you had lot's of users you coul dcause a pretty bad bottleneck.
2. There are some SELECTs or UPDATEs or other actions on the temp tables that were previously supported by indexes on the permanent staging tables but are now doing table scans, etc.
3. You tempdb is an some really ancient (slow) file system and your table spaces are on something much better.