What is the comparative speed of temporary tables to physical tables in SQL?

Question

I have a script that needs to extract data temporarily to do extra operations on it, but then doesn't need to store it any further after the script has run. I currently have the data in question in a series of temporary local tables (CREATE TABLE #table), which are then dropped as their use is completed. I was considering switching to physical tables, treated in the same way (CREATE TABLE table), if there would be an improvement in the speed of the script for it (or other advantages, maybe?).

...So, is there a difference in performance, between temporary tables and physical tables? From what I'm reading, temporary tables are just physical tables that only the session running the script can look at (cutting down on locking issues).

EDIT: I should point out that I'm talking about physical tables vs. temporary tables. There is a lot of info available about temporary tables vs. table variables, e.g. http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html.

Answer 1

For MySql at least, the only time savings you are going to get is the time savings of actually creating the temporary table. AFAIK all the tables are treated the same on disk, they just happen to go away at the end of your session. This is what I have seen in practice as well. Again, this is mysql 4.x and 5.x

Answer 2

I am not 100% sure on this, but I believe that table variables are strictly in memory but temp tables reside in the tempdb, which is stored on disk. This is using SQL Server, I am not sure how consistent the different RDMS's are about this.

Answer 3

Temporary tables are a big NO in SQL Server.

• They provoke query plan recompilations which is costly.
• Creating and dropping the table are also costly operations that you are adding to your process.
• If there is a big amount of data going to the temporary data your operations will be slow on the lack of indexes. You CAN create indexes on temporary tables. But I will never recommend a temporary table for anything with a big amount of records.

Your other approach: To create and then drop regular tables just creates the same overhead.

Another approach: Using existing tables, augmenting the rows with an additional column to differentiate which rows pertain to each user/session could be used. Removes the burden to create/drop the tables but, then, you will need to be paranoid with the code that generate the value to differentiate the rows AND you will have to develop a way to maintain the table for those cases where a session ended prematurely and there are leftovers (rows that were not removed at the end of the processing).

I recommend you to rethink your processing strategy. Some alternatives are as easy as using correlated queries, derived tables or table variables. Take a look at: http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

---

Edit: The approach of creating and dropping regular tables and the approach of reusing a regular table augumented with an additional field: Both will generate query plan recompilations because the amount of data changed will trigger the reevaluation of table statistics. Again, your best approach is to find alternate ways to proccess your data.

Answer 4

What kind of data operations are you doing, and how much data are you working with?

I would stick with the temporary tables - for large data sets, I've always found it to be by far the best way to go, and not just in SQL Server. You could try a global temporary table (CREATE TABLE ##tablename), which lasts beyond just the scope of the create statement.

From SQL Server Books Online (2005):

If you create the global temporary table ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.

Answer 5

One thing to consider is whether you can count on only one user running this proc at a time. If you have simultaneous users, then the regular table option could have interference. The temp table could be unique to the user.