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

lécriture dans tempdb est plus rapide quune table réelle non dans tempdb

Cest vrai. Il y a deux améliorations dE / S dans TempDb.

Les écritures dans une table dune base de données utilisateur doivent avoir leurs enregistrements de journal vidés sur le disque lors de la validation, ou si un insert minimalement enregistré (comme SELECT … INTO), les pages de la base de données doivent être vidées sur le disque lors de la validation. 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ù SELECT … INTO se termine, les nouvelles pages ont toutes été écrites dans les fichiers physiques.

Les écritures TempDb ne doivent pas être vidées sur le disque lors de la validation car TempDb nest jamais récupéré après une panne. Donc, ils ne sont tout simplement pas. Vos modifications génèrent des enregistrements de journal, mais le tampon de journal est vidé sur le disque seulement quand il est plein, pas pour chaque validation.

Et depuis SQL Server 2014 , les insertions à journalisation minimale dans TempDb ne sont pas toujours écrites sur le disque. Si vous chargez un petit, table temporaire de courte durée, il ne sera peut-être jamais écrit sur le disque. Le journal contiendra quelques enregistrements sur les allocations de page et les entrées de métadonnées pour la table, mais cest tout.

EG exécutez ce qui suit batch 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 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 

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 du journal vidé:

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

Commentaires

• Il y a un cas où linsertion initiale est plus rapide mais elle revient pour vous mordre plus tard cependant. Cette démo montre une requête apportant des données dans le cache tampon qui prend beaucoup plus de temps car le rédacteur paresseux est occupé à écrire sur le disque des pages tempdb qui sont sales pour une table temporaire qui nexiste plus youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• Oui. Il y a ' une amélioration future potentielle pour éliminer le vidage des pages du pool de tampons qui ne sont allouées à aucun objet. Mais le chargement dune grande table temporaire entraînera toujours les E / S, soit directement, soit indirectement en réduisant la mémoire disponible pour la mise en cache.

De même que les écritures dans tempdb ne sont souvent pas toutes les E / S disque / réseau qui touchent, comme cela a été étendu dans la réponse de David Browne , en fonction de votre IO configuration, vous constaterez peut-être que même lorsque les données sont suffisamment volumineuses pour devoir être spoulées sur le disque, elles sont toujours plus rapides que de les sélectionner dans une table « normale »:

• TempDB peut être différent ont donc sa propre bande passante dE / S. Ceci est particulièrement important avec les disques rotatifs plutôt que les disques SSD. La lecture à partir de et lécriture dans la même base de données (ou une autre base de données sur les mêmes disques) impliquera plus de mouvements de tête qui ajoutez plus de latence dE / S et réduisez potentiellement votre bande passante dE / S effective. La copie de données entre des bases de données sur différents disques / baies naura pas la même latence supplémentaire.

• TempDB peut même être activé plus que votre stockage principal. Peut-être sur des disques locaux où le stockage principal est sur le réseau, ou sur des disques SSD NVMe où le magasin principal est sur des disques traditionnels.

Ces deux différences peuvent également être observées à lintérieur du même base de données si vous utilisez plusieurs groupes de fichiers pour répartir des parties des données entre différents disques / baies.

Linverse peut également être vrai si vous avez plusieurs bases de données qui sont activement utilisées. Comme TempDB est une ressource partagée, elle et les lecteurs / réseau qui lhébergent peuvent être soumis à une charge plus élevée que les fichiers de données de nimporte quelle base de données individuelle.