¿Es generalmente más rápido seleccionar en una tabla temporal que seleccionar en una tabla real?

escribir en tempdb es más rápido que en una tabla real que no esté en tempdb

Es cierto. Hay dos mejoras de IO en TempDb.

Las escrituras en una tabla en una base de datos de usuario deben tener sus registros de registro vaciados en el disco en la confirmación, o si una inserción mínimamente registrada (como SELECT … INTO), debe tener las páginas de la base de datos vaciadas en el disco en la confirmación. La forma en que funciona el registro mínimo en una base de datos de usuario es que las páginas de la base de datos se escriben directamente en el disco. Para cuando se completa SELECT … INTO, todas las páginas nuevas se han escrito en los archivos físicos.

Las escrituras de TempDb no tienen que ser vaciadas al disco en la confirmación ya que TempDb nunca se recupera después de una falla. Así que simplemente no lo son. Sus cambios generan registros de registro, pero el búfer de registro se descarga en el disco solo cuando está lleno, no para cada confirmación.

Y desde SQL Server 2014 , las inserciones mínimamente registradas en TempDb no siempre se escriben en el disco tampoco. Si carga un pequeño, tabla temporal de corta duración, es posible que nunca se escriba en el disco. El registro tendrá algunos registros sobre las asignaciones de páginas y las entradas de metadatos para la tabla, pero eso es todo.

Por ejemplo, ejecute lo siguiente lote en tempdb, una base de datos de recuperación completa y una base de datos de recuperación simple para ver las diferencias.

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 

y verá algo como:

Para una recuperación simple:

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

para una recuperación completa:

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

y para tempdb:

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

A veces, para tempdb, verá el búfer de registro vaciado:

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

Comentarios

• Hay un caso en el que la inserción inicial es más rápida pero, sin embargo, vuelve para morderlo más tarde. Esta demostración muestra una consulta que lleva datos al caché del búfer que tarda mucho más, ya que el escritor perezoso está ocupado escribiendo en las páginas tempdb del disco que están sucias para una tabla temporal que ya no existe youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
• Sí. Hay ' s una posible mejora futura para eliminar el vaciado de páginas del Buffer Pool no asignadas a ningún objeto. Pero cargar una tabla temporal grande siempre generará IO, ya sea directa o indirectamente al reducir la memoria disponible para el almacenamiento en caché.

Además de escribir en tempdb, a menudo no todos los E / S de red / disco de impacto, como se amplía en respuesta de David Browne , según su E / S configuración, puede encontrar que incluso cuando los datos son lo suficientemente grandes como para tener que ponerlos en cola en el disco, es más rápido que seleccionarlos en una tabla «normal»:

• TempDB puede estar en diferentes unidades, por lo que tienen su propio ancho de banda de E / S. Esto es especialmente significativo con unidades giratorias en lugar de SSD. Leer desde y escribir en la misma base de datos (u otra base de datos en las mismas unidades) implicará más movimientos de cabeza que agregue más latencia de IO y potencialmente acelere su ancho de banda de IO efectivo. Copiar datos entre bases de datos en diferentes unidades / arreglos no tendrá la misma latencia adicional.

• TempDB puede incluso estar en fa medio más ester que su almacenamiento principal. Quizás en unidades locales donde el almacenamiento principal está en la red, o SSD NVMe donde la tienda principal está en unidades tradicionales.

Ambas diferencias también pueden verse dentro de la misma base de datos si utiliza varios grupos de archivos para distribuir partes de los datos entre diferentes unidades / matrices.

Lo contrario también puede ser cierto si tiene varias bases de datos que están activamente en uso. Como TempDB es un recurso compartido, y las unidades / red que lo albergan, pueden estar bajo más carga que los archivos de datos de cualquier base de datos individual.