Rendimiento de SQL Server de SELECT INTO vs INSERT INTO para tablas temporales

Problema

SQL Server incluye código SELECT…INTO e INSERT…INTO para insertar datos en tablas temporales. ¿Puede proporcionar algunos ejemplos y describir qué opción funciona mejor?

Solución

SQL Server incluye las dos opciones para las tablas temporales:

• Tabla temporal local
• Tabla temporal global

Es necesario añadir el prefijo ‘#’ para las tablas temporales locales y ‘##’ para las tablas temporales globales. Estos objetos se crearán en la base de datos del sistema TempDB. Las tablas temporales se almacenan en la base de datos TempDB con un nombre de objeto único y se crean a nivel de conexión o de sesión. Estas tablas son visibles y accesibles sólo dentro de la sesión. Podemos eliminar la tabla temporal utilizando el comando DROP TABLE o la tabla temporal se eliminará automáticamente cuando la sesión se desconecte. Además, SQL Server mantiene las estadísticas de las tablas temporales.

La creación y eliminación de tablas temporales requiere el acceso y la modificación de las páginas del mapa de asignación de TempDB (IAM, SGAM y PES). Las tablas temporales se almacenan en caché en la reserva de búferes en comparación con las tablas permanentes basadas en el disco. Con el almacenamiento en caché de las tablas temporales, SQL Server no eliminará la tabla físicamente, sino que la truncará y mantendrá las páginas IAM y de datos. Cuando la tabla se cree más tarde, SQL Server reutilizará las páginas anteriores, lo que reduce el número de modificaciones de página necesarias.

Las tablas temporales se pueden crear de dos maneras:

• Crear tabla
• Seleccionar en

En general, el rendimiento de ambas opciones es similar para una pequeña cantidad de datos. Los datos se insertan rápidamente en la tabla temporal, pero si la cantidad de datos es grande, entonces podemos experimentar un rendimiento de consulta pobre. Esto suele ocurrir con las tablas temporales cuando insertamos un gran número de filas.

Ejemplo de INSERT INTO de SQL Server

SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLCREATE TABLE #list_to_table(Id BIGINT)SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML) INSERT INTO #list_to_tableSELECT f.x.value('.', 'BIGINT') AS IdFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table

Tiempo de análisis y compilación de SQL Server:

CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. 

Cuando hay una gran cantidad de valores separados por comas como parámetro de entrada,el código puede retrasarse unos segundos mientras se insertan los datos en el temptable.

Ejemplo de SELECT INTO de SQL Server

Como solución alternativa podemos utilizar el comando SELECT…INTO que generalmente se comporta mejor que el comando INSERT…SELECT.

SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLSELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)SELECT f.x.value('.', 'BIGINT') AS IdINTO #list_to_tableFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table

SQL Server parse and compile time:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server INSERT…SELECT vs. SELECT…INTO con un conjunto de datos más grande

Los ejemplos anteriores son muy pequeños, así que vamos a utilizar el ejemplo de abajo para ver cómo thisperforms con una gran cantidad de datos. Esta prueba tendrá más de un millón de filas.

SQL Server INSERT… SELECT

CREATE TABLE #EMPLOYEEEEE (Emp_id BIGINT, f_name NVARCHAR(100), l_name NVARCHAR(100), Email NVARCHAR(100), is_active BIT) INSERT INTO #EMPLOYEESELECT Emp_id, f_name, l_name, Email, is_active FROM employee

SQL Server parse and compile time:

SQL Server Execution Times: CPU time = 1407 ms, elapsed time = 3241 ms.(1727264 row(s) affected)

SQL Server SELECT…INTO

SELECT Emp_id, f_name, l_name, Email, is_activeeINTO #EMPLOYEEFROM employee

Tiempo de análisis y compilación de SQL Server:

SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)

Resultados y análisis

Como podemos ver el SELECT…INTO fue considerablemente más rápido 489ms comparado con3241ms.

El comando INSERT…INTO reutilizará las páginas de datos que se crean en la caché para las operaciones de inserción/actualización/borrado. El comando SELECT…INTO creará nuevas páginas para la creación de la tabla de forma similar a las tablas regulares y las eliminará físicamente cuando se elimine la tabla temporal.

Siguientes pasos

• Tenga en cuenta estas consideraciones de rendimiento cuando desarrolle su código o tenga que ajustar el código que no tenga un rendimiento óptimo.
• Pruebe escenarios adicionales en su entorno para determinar qué técnica de codificación funcionará mejor.
• Tenga siempre la mente abierta a varias técnicas de codificación, pruebe cada técnicay deje que el rendimiento dicte la respuesta.
Última actualización: 2017-01-04




Acerca del autor

Jignesh Raiyani es un desarrollador/DBA de SQL Server con experiencia en el diseño y desarrollo de procedimientos T-Procedimientos SQL y ajuste del rendimiento de las consultas.
Ver todos mis consejos

Recursos relacionados

• Más consejos para desarrolladores de bases de datos…