Is it generally faster to select into a temp table than selecting into an actual table?

Question

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and storing the data in memory?

Answer 1

writing to tempdb is faster than an actual table not in tempdb

It's true. There are two IO enhancements in TempDb.

Writes to a table in a user database must have their log records flushed to disk on commit, or if a minimally-logged insert (like SELECT ... INTO), must have the database pages flushed to disk on commit. The way the minimal logging works in a user database is that the database pages are written directly to disk. By the time the SELECT ... INTO completes, the new pages have all been written to the physical files.

TempDb writes don't have to be flushed to disk on commit since TempDb is never recovered after a failure. So they simply aren't. Your changes generate log records, but the log buffer is flushed to disk only when it's full, not for every commit.

And since SQL Server 2014 the minimally-logged inserts in TempDb aren't always written to disk either. If you load a small, short-lived temp table it may never be written to disk at all. The log will have a few records about the page allocations and metadata entries for the table, but that's it.

EG run the following batch in tempdb, a full recovery database and a simple recovery database to see the differences.

drop table if exists foo
go
declare @data bigint
declare @log bigint 
select @log = sum(case when type_desc = 'LOG' then num_of_bytes_written end) 
      ,@data =  sum(case when type_desc = 'ROWS' then num_of_bytes_written end) 
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

select * 
into foo
from sys.objects 

select -@log + sum(case when type_desc = 'LOG' then num_of_bytes_written end)  log_bytes
      ,-@data +  sum(case when type_desc = 'ROWS' then num_of_bytes_written end) data_bytes
      , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

and you'll see something like:

For simple recovery:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
24576                16384                SIMPLE

for full recovery:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
36864                0                    FULL

and for tempdb:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
0                    0                    SIMPLE

Sometimes for tempdb you'll see the log buffer flushed:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
61440                0                    SIMPLE

Answer 2

As well as writes to tempdb often not every hitting disk/network IO, as extended upon in David Browne's answer, depending on your IO configuration you may find that even when the data is big enough to have to be spooled to disk it is still faster than selecting into a "normal" table:

• TempDB may be on different drives, so have its own IO bandwidth. This is especially significant with spinning drives rather than SSDs. Reading from and writing to the same database (or another database on the same drives) will involve more head movements which add more IO latency and potentially throttle your effective IO bandwidth. Copying data between databases on different drives/arrays will not have the same extra latency.

• TempDB may even be on faster media than your main storage. Perhaps on local drives where main storage is on the network, or NVMe SSDs where the main store is on traditional drives.

Both of these differences may also be seen inside the same database if you use multiple filegroups to spread parts of the data between different drives/arrays.

The opposite can also be true if you have multiple databases that are actively in use. As TempDB is a shared resource it, and the drives/network that host it, might be under more load than the data files for any individual DB.