Är det i allmänhet snabbare att välja i en temp-tabell än att välja i en faktisk tabell?

att skriva till tempdb är snabbare än en faktisk tabell som inte finns i tempdb

Det stämmer. Det finns två IO-förbättringar i TempDb.

Skriver till en tabell i en användardatabas måste ha sina loggposter spolas till disk vid engagemang, eller om en minimiloggad insats (som SELECT … INTO) måste databassidorna spolas till disk Det sätt på vilket minimal loggning fungerar i en användardatabas är att databassidorna skrivs direkt till disken. När SELECT … INTO har slutförts har de nya sidorna skrivits till de fysiska filerna.

TempDb skriver behöver inte spolas till disk on commit eftersom TempDb aldrig återställs efter ett fel. Så de är helt enkelt inte. Dina ändringar genererar loggposter, men loggbufferten spolas till hårddisken bara när den är full, inte för varje engagemang.

Och sedan SQL Server 2014 skrivs inte de minimalt loggade insatserna i TempDb alltid till hårddisken. Om du laddar en liten, kortvarig temp-tabell kanske det aldrig skrivs till disk alls. Loggen kommer att ha några poster om sidtilldelningarna och metadataposterna för tabellen, men det är det.

EG kör följande batch i tempdb, en fullständig återställningsdatabas och en enkel återställningsdatabas för att se skillnaderna.

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 

och du ser något som:

För enkel återställning:

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

för fullständig återställning:

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

och för tempdb:

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

Ibland för tempdb ser du loggbufferten spolas:

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

Kommentarer

• Det finns ett fall där den första infogningen är snabbare men det kommer tillbaka för att bita dig senare. Denna demo visar en fråga som tar data in i buffertcachen tar mycket längre tid eftersom den lata författaren är upptagen med att skriva till skivans tempdb-sidor som är smutsiga för en temp-tabell som inte finns längre youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• Ja. Det finns ' en potentiell framtida förbättring för att eliminera spolning av buffertpoolsidor som inte tilldelats något objekt. Men att ladda en stor temp-tabell kommer alltid att köra IO, antingen direkt eller indirekt genom att minska det minne som finns tillgängligt för cachning.

Förutom att skriva till tempdb ofta inte alla träffar på disk / nätverk IO, som utökat i David Brownes svar , beroende på din IO konfiguration kan det hända att även när data är tillräckligt stora för att de ska behöva spolas till disk är det fortfarande snabbare än att välja i en ”normal” tabell:

• TempDB kan vara på olika enheter, så ha sin egen IO-bandbredd. Detta är särskilt viktigt med snurrande enheter snarare än SSD-enheter. Att läsa från och skriva till samma databas (eller en annan databas på samma enheter) kommer att innebära fler huvudrörelser som lägg till mer IO-latens och potentiellt begränsa din effektiva IO-bandbredd. Kopiering av data mellan databaser på olika enheter / matriser har inte samma extra latens.

• TempDB kan till och med vara på fa ster media än din huvudlagring. Kanske på lokala enheter där huvudlagring finns i nätverket eller NVMe SSD där huvudbutiken finns på traditionella enheter.

Båda dessa skillnader kan också ses i samma databas om du använder flera filgrupper för att sprida delar av data mellan olika enheter / matriser.

Det motsatta kan också vara sant om du har flera databaser som används aktivt. Eftersom TempDB är en delad resurs kan den och de enheter / nätverk som är värd för den vara under mer belastning än datafilerna för varje enskild DB.