Table vs Temp Table Performance

Question

Which is faster for millions of records: Permanent Table or Temp Tables?

I have to use it only for 15 million records. After processing is complete, we delete these records.

Answer 1

In your situation we use a permanent table called a staging table. This is a common method with large imports. In fact we generally use two staging tables one with the raw data and one with the cleaned up data which makes researching issues with the feed easier (they are almost always a result of new and varied ways our clients find to send us junk data, but we have to be able to prove that). Plus you avoid issues like having to grow temp db or causing issues for other users who want to use temp db but have to wait while it grows for you, etc.

You can also use SSIS and skip the staging table(s), but I find the ability to go back and research without having to reload a 50,000,000 table is very helpful.

Answer 2

Temp tables are in memory (unless they're too big), so in theory they should be REALLY fast. But it's usually not. As a rule of thumb, try to stay away from temp tables, unless that is the only solution. Can you give us some more information about what you're trying to do? It could probably be done with a derived query

Answer 3

Permanent Table is faster in most cases than temp table.

Have a look on : http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

Answer 4

It depends.

Temp tables are stored in the tempdb database, which may or may not be on a different drive than your actual database. So a lot depends on a) the speed of those drives and b) which databases/files are on the same drive.
(for example, your actual database will be faster if database files and log files are on different physical drives)

---

If you use an availability solution like Database Mirroring, temp tables are probably faster:
At work, we are using synchronous Database Mirroring, which means that if we write to our database, the data is immediately written to the mirror server as well, and the main server waits for the mirror's confirmation before returning to the caller(!).

So if you insert 15 million records into a table, process them (probably involving some big updates on all of them) and delete them afterwards, SQL Server has to propagate all these changes immediately over the network to the mirror server.

On the other hand, doing this in a temp table will stay local on the server, in the tempdb database.

Answer 5

I personally would use a permanent table and truncate it before each use. In my experience it is easier to understand/maintain. However, my best advice to you is to try both and see which one performs better.

Answer 6

If you don't use tempdb, make sure the recovery model of the database you are working in is not set to "Full". This will cause a lot of overhead on those 50M row inserts.

Ideally, you should use a staging database, simple recovery model, on RAID 10 if possible, and size it ahead of time to provide enough space for all your operations. Turn auto-grow off.

Use INSERT ... WITH (TABLOCK) to avoid row-level logging:

INSERT INTO StagingTable WITH (TABLOCK) (.....)
SELECT .....

Likewise for BULK INSERT. If you drop and recreate, create your clustered index prior to insert. If you can't, insert into one table first, then insert from that into another table with the right clustering, and truncate the first table. Avoid small batch sizes on BULK INSERT if possible. Read the BULK INSERT documentation closely, as you can sabotage performance with the wrong options.

Avoid INSERT ... EXEC. Every row is logged.

Avoid UPDATEs, unless you need to calculate running totals. Generally, it is cheaper to insert from one table into another, and then truncate the first table, than to update in place. Running total calculations are the exception, since they can be done with an UPDATE and variables to accumulate values between rows.

Avoid table variables for anything except control structures, since they prevent parallelization. Do not join your 50M row table to a table variable, use a temp table instead.

Don't be afraid of cursors for iteration. Use cursor variables, and declare them with the STATIC keyword against low-cardinality columns at the front of the clustered index. Use this to slice big tables into more manageable chunks.

Don't try to do too much in any one statement.