É geralmente mais rápido seleccionar para uma tabela temporária do que seleccionar para uma tabela real?

escrever para tempdb é mais rápido do que seleccionar para uma tabela real não em tempdb

É verdade. Há duas melhorias IO no TempDb.

Escritos para uma tabela numa base de dados de utilizadores têm de ter os seus registos de registo descarregados para o disco aquando da submissão, ou se uma inserção minimamente registada (como SELECT … INTO), tem de ter as páginas da base de dados descarregadas para o disco aquando da submissão. A forma como o registo mínimo funciona numa base de dados de utilizadores é que as páginas da base de dados são gravadas directamente no disco. Quando o SELECT … INTO terminar, as novas páginas já foram todas escritas nos ficheiros físicos.

TempDb escritas não têm de ser descarregadas para o disco em commit uma vez que o TempDb nunca é recuperado após uma falha. Por isso, simplesmente não são. As suas alterações geram registos de registo, mas o buffer de registo só é descarregado no disco quando está cheio, não para cada commit.

E desde SQL Server 2014 as inserções minimamente registadas no TempDb também nem sempre são gravadas no disco. Se carregar uma pequena e efémera tabela de temperatura, esta poderá nunca ser gravada no disco. O registo terá alguns registos sobre as atribuições das páginas e entradas de metadados para a tabela, mas é isso.

EG executar o seguinte lote em tempdb, uma base de dados de recuperação completa e uma base de dados de recuperação simples para ver as diferenças.

drop table if exists foogodeclare @data bigintdeclare @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 fcross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fsselect * into foofrom 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_modelfrom sys.database_files fcross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

e verá algo como:

Para uma recuperação simples:

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

para uma recuperação completa:

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

e para tempdb:

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

Às vezes para tempdb verá o tampão de registo descarregado: