Est-il généralement plus rapide de sélectionner dans une table temporaire que de sélectionner dans une table réelle ?

Écrire dans tempdb est plus rapide qu’une table réelle qui n’est pas dans tempdb

C’est vrai. Il y a deux améliorations de l’entrée-sortie dans TempDb.

Les écritures sur une table dans une base de données utilisateur doivent avoir leurs enregistrements de logs vidés sur le disque lors du commit, ou si une insertion minimalement loguée (comme SELECT … INTO), doit avoir les pages de la base de données vidées sur le disque lors du commit. La façon dont la journalisation minimale fonctionne dans une base de données utilisateur est que les pages de la base de données sont écrites directement sur le disque. Au moment où le SELECT … INTO se termine, les nouvelles pages ont toutes été écrites dans les fichiers physiques.

Les écritures de TempDb n’ont pas besoin d’être vidées sur le disque lors du commit puisque TempDb n’est jamais récupéré après un échec. Elles ne le sont donc tout simplement pas. Vos modifications génèrent des enregistrements de journal, mais la mémoire tampon du journal n’est vidée sur le disque que lorsqu’elle est pleine, et non à chaque commit.

Et depuis SQL Server 2014, les insertions minimalement journalisées dans TempDb ne sont pas toujours écrites sur le disque non plus. Si vous chargez une petite table temporaire à courte durée de vie, il se peut qu’elle ne soit jamais écrite sur le disque du tout. Le journal aura quelques enregistrements sur les allocations de pages et les entrées de métadonnées pour la table, mais c’est tout.

EG exécute le lot suivant dans tempdb, une base de données de récupération complète et une base de données de récupération simple pour voir les différences.

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

et vous verrez quelque chose comme:

Pour une récupération simple:

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

pour une récupération complète :

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

et pour tempdb:

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

Parfois, pour tempdb, vous verrez le tampon de journal vidé:

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

.