È generalmente più veloce selezionare in una tabella temporanea che selezionare in una tabella reale?

Scrivere in tempdb è più veloce di una tabella reale non in tempdb

È vero. Ci sono due miglioramenti di IO in TempDb.

Le scritture su una tabella in un database utente devono avere i loro record di log scaricati su disco al momento del commit, o se si tratta di un inserimento con log minimo (come SELECT … INTO), devono avere le pagine del database scaricate su disco al momento del commit. Il modo in cui la registrazione minima funziona in un database utente è che le pagine del database sono scritte direttamente su disco. Nel momento in cui SELECT … INTO viene completata, le nuove pagine sono state tutte scritte nei file fisici.

Le scritture di TempDb non hanno bisogno di essere scaricate su disco al momento del commit poiché TempDb non viene mai recuperato dopo un errore. Quindi semplicemente non lo sono. Le vostre modifiche generano record di log, ma il buffer del log viene lavato su disco solo quando è pieno, non per ogni commit.

E da SQL Server 2014 gli inserti minimamente loggati in TempDb non sono sempre scritti su disco. Se si carica una tabella temporanea piccola e di breve durata, potrebbe non essere mai scritta su disco. Il log avrà alcuni record sulle allocazioni di pagina e le voci dei metadati per la tabella, ma questo è tutto.

Esegui il seguente batch in tempdb, un database di recupero completo e un database di recupero semplice per vedere le differenze.

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 vedrete qualcosa come:

Per il recupero semplice:

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

per il recupero completo:

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

e per tempdb:

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

A volte per tempdb si vedrà il buffer di log lavato:

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