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

scrivere in tempdb è più veloce di una tabella effettiva 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 un inserimento con registrazione minima (come SELECT … INTO), deve avere le pagine del database scaricate su disco sul commit. Il modo in cui funziona la registrazione minima in un database utente è che le pagine del database vengono scritte direttamente su disco. Quando SELECT … INTO viene completato, le nuove pagine sono state tutte scritte nei file fisici.

Le scritture di TempDb non devono essere scaricate sul disco al momento del commit poiché TempDb non viene mai ripristinato dopo un errore. Quindi semplicemente non lo sono. Le tue modifiche generano record di log, ma il buffer di log viene scaricato sul disco solo quando è pieno, non per ogni commit.

E da SQL Server 2014 gli inserimenti con registrazione minima in TempDb non vengono sempre scritti su disco. Se carichi un piccolo, tabella temporanea di breve durata potrebbe non essere mai scritta su disco. Il registro conterrà alcuni record sulle allocazioni di pagina e le voci di metadati per la tabella, ma questo è tutto.

EG esegui quanto segue batch in tempdb, un database di ripristino completo e un database di ripristino semplice per vedere le differenze.

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 

e vedrai qualcosa come:

Per il ripristino semplice:

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

per il ripristino 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 vedrai il buffer di log svuotato:

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

Commenti

• Cè un caso in cui linserimento iniziale è più veloce ma torna comunque a morderti in seguito. Questa demo mostra una query che porta i dati nella cache del buffer che impiega molto più tempo poiché il lazy writer è impegnato a scrivere su disco tempdb pagine sporche per una tabella temporanea che non esiste più youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• Sì. Cè ' un potenziale miglioramento futuro per eliminare lo svuotamento delle pagine del pool di buffer non allocate a nessun oggetto. Ma il caricamento di una tabella temporanea di grandi dimensioni guiderà sempre lIO, direttamente o indirettamente, riducendo la memoria disponibile per la memorizzazione nella cache.

Oltre a scrivere in tempdb, spesso non tutti gli IO su disco / rete, come spiegato nella risposta di David Browne , a seconda del tuo IO configurazione potresti scoprire che anche quando i dati sono abbastanza grandi da dover essere inviati in spool su disco è ancora più veloce che selezionarli in una tabella “normale”:

• TempDB potrebbe essere diverso unità, quindi avere una propria larghezza di banda di I / O. Ciò è particolarmente significativo con unità in rotazione piuttosto che SSD. La lettura da e scrittura nello stesso database (o un altro database sulle stesse unità) comporterà più movimenti della testa che aggiungere più latenza di I / O e potenzialmente limitare la larghezza di banda di I / O effettiva. La copia di dati tra database su unità / array diversi non avrà la stessa latenza aggiuntiva.

• TempDB potrebbe anche essere attivo ster rispetto alla memoria principale. Forse su unità locali in cui si trova larchiviazione principale sulla rete o SSD NVMe in cui larchivio principale si trova su unità tradizionali.

Entrambe queste differenze possono essere viste anche allinterno dello stesso database se si utilizzano più filegroup per distribuire parti dei dati tra unità / array differenti.

Lopposto può essere vero anche se si hanno più database attivamente in uso. Poiché TempDB è una risorsa condivisa, e le unità / rete che lo ospitano, potrebbero essere sottoposte a un carico maggiore rispetto ai file di dati per ogni singolo DB.