sql server - tipo - ¿Cuándo debo usar una variable de tabla en lugar de una tabla temporal en el servidor SQL?

tablas temporales vs variables tipo tabla sql server

Question

Estoy aprendiendo más detalles en la tabla de variables. Dice que las tablas temporales siempre están en el disco y que las variables de la tabla están en la memoria, es decir, el rendimiento de la variable de tabla es mejor que la tabla temporal porque la variable de tabla usa menos operaciones de E / S que la tabla temporal.

Pero a veces, si hay demasiados registros en una variable de tabla que no pueden estar contenidos en la memoria, la variable de tabla se colocará en el disco como la tabla temporal.

Pero no sé qué son los "demasiados registros". 100,000 registros? o 1000,000 registros? ¿Cómo puedo saber si una variable de tabla que estoy usando está en la memoria o está en el disco? ¿Existe alguna función o herramienta en SQL Server 2005 para medir la escala de la variable de tabla o avisarme cuando la variable de tabla se coloca en el disco desde la memoria?

Answer 1

Estoy totalmente de acuerdo con Abacus (lo siento, no tengo suficientes puntos para comentar).

Además, tenga en cuenta que no necesariamente se reduce a cuántos registros tiene, sino al tamaño de sus registros.

Por ejemplo, ¿ha considerado la diferencia de rendimiento entre 1,000 registros con 50 columnas cada uno frente a 100,000 registros con solo 5 columnas cada uno?

Por último, ¿quizás está consultando / almacenando más datos de los que necesita? Aquí hay una buena lectura sobre las estrategias de optimización de SQL . Limite la cantidad de datos que está obteniendo, especialmente si no los está utilizando todos (algunos programadores de SQL se vuelven perezosos y simplemente seleccionan todo aunque solo usan un pequeño subconjunto). No olvides que el analizador de consultas SQL también puede convertirse en tu mejor amigo.

Answer 2

Microsoft dice aquí

Las variables de la tabla no tienen estadísticas de distribución, no activarán recompilaciones. Por lo tanto, en muchos casos, el optimizador construirá un plan de consulta en el supuesto de que la variable de la tabla no tenga filas. Por este motivo, debe tener cuidado al usar una variable de tabla si espera un número mayor de filas (más de 100). Las tablas temporales pueden ser una mejor solución en este caso.

Answer 3

Su pregunta muestra que ha sucumbido a algunos de los conceptos erróneos comunes que rodean las variables de tabla y las tablas temporales.

He escrito una respuesta bastante extensa en el sitio de DBA observando las diferencias entre los dos tipos de objetos. Esto también responde a su pregunta sobre el disco frente a la memoria (no vi ninguna diferencia significativa en el comportamiento entre los dos).

Con respecto a la pregunta en el título, sin embargo, en cuanto a cuándo usar una variable de tabla en comparación con una tabla temporal local, no siempre tiene una opción. En las funciones, por ejemplo, solo es posible usar una variable de tabla y si necesita escribir en la tabla en un ámbito secundario, solo lo #temp una tabla #temp (los parámetros con valores de tabla permiten el acceso de solo lectura ).

A continuación, puede elegir algunas sugerencias (aunque el método más confiable es simplemente probar ambas con su carga de trabajo específica).

1. Si necesita un índice que no se puede crear implícitamente a través de una restricción UNIQUE o PRIMARY KEY entonces necesita una tabla #temporary ya que no es posible crearlos en las variables de la tabla. (Los ejemplos de dichos índices son no únicos, índices filtrados o índices con INCLUDE d columnas). NB: SQL Server 2014 permitirá que se declaren en línea índices no únicos para las variables de la tabla.
2. Si va a agregar y eliminar repetidamente grandes números de filas de la tabla, use una tabla #temporary . Eso es compatible con TRUNCATE (que es más eficiente que DELETE para tablas grandes) y, además, las inserciones posteriores después de TRUNCATE pueden tener un mejor rendimiento que las que siguen a DELETE como se ilustra aquí .
3. Si va a eliminar o actualizar una gran cantidad de filas, entonces la tabla temporal puede tener un rendimiento mucho mejor que una variable de la tabla, si es capaz de usar el uso compartido de conjuntos de filas (consulte el apartado "Efectos del uso compartido de conjuntos de filas" a continuación para ver un ejemplo).
4. Si el plan óptimo que usa la tabla variará dependiendo de los datos, use una tabla #temporary . Esto permite la creación de estadísticas, lo que permite que el plan se compile dinámicamente de acuerdo con los datos (aunque para las tablas temporales almacenadas en caché en procedimientos almacenados, el comportamiento de la recompilación debe entenderse por separado).
5. Si es poco probable que el plan óptimo para la consulta con la tabla cambie alguna vez, entonces puede considerar una variable de tabla para omitir la sobrecarga de la creación de estadísticas y las recompilaciones (posiblemente se necesitarían sugerencias para corregir el plan que desea).
6. Si la fuente de los datos insertados en la tabla proviene de una declaración SELECT potencialmente costosa, entonces considere que el uso de una variable de tabla bloqueará la posibilidad de que este use un plan paralelo.
7. Si necesita que los datos de la tabla sobrevivan a una reversión de una transacción de usuario externo, utilice una variable de tabla. Un posible caso de uso para esto podría ser registrar el progreso de diferentes pasos en un lote largo de SQL.
8. Cuando se utiliza una tabla #temp dentro de una transacción de usuario, los bloqueos se pueden mantener más tiempo que para las variables de la tabla (potencialmente hasta el final de la transacción frente al final de la instrucción que depende del tipo de bloqueo y nivel de aislamiento) y también puede evitar el truncamiento de la transacción tempdb iniciar sesión hasta que finalice la transacción del usuario. Así que esto podría favorecer el uso de variables de tabla.
9. Dentro de las rutinas almacenadas, tanto las variables de tabla como las tablas temporales se pueden almacenar en caché. El mantenimiento de metadatos para las variables de la tabla en caché es menor que para #temporary tablas #temporary . Bob Ward señala en su presentación de tempdb que esto puede causar una contención adicional en las tablas del sistema en condiciones de alta concurrencia. Además, cuando se trata de pequeñas cantidades de datos, esto puede hacer una diferencia mensurable en el rendimiento .

Efectos de compartir el conjunto de filas

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

Answer 4

Utilice una variable de tabla si para una cantidad muy pequeña de datos (miles de bytes)

Utilice una tabla temporal para una gran cantidad de datos

Otra forma de pensar en ello: si cree que podría beneficiarse de un índice, estadísticas automatizadas o cualquier bondad del optimizador de SQL, entonces su conjunto de datos es probablemente demasiado grande para una variable de tabla.

En mi ejemplo, solo quería poner unas 20 filas en un formato y modificarlas como un grupo, antes de usarlas para ACTUALIZAR / INSERTAR una tabla permanente. Así que una variable de tabla es perfecta.

Pero también estoy ejecutando SQL para volver a rellenar miles de filas a la vez, y definitivamente puedo decir que las tablas temporales funcionan mucho mejor que las variables de tabla.

Esto no es diferente de cómo los CTE son una preocupación por una razón de tamaño similar: si los datos en el CTE son muy pequeños, encuentro que el CTE tiene un rendimiento tan bueno o mejor que el optimizador, pero si es bastante grande, entonces te duele mucho

Mi comprensión se basa principalmente en http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/ , que tiene muchos más detalles.

Answer 5

escribiendo datos en tablas declaradas declare @tb y luego de unirme a otras tablas, me di cuenta de que el tiempo de respuesta en comparación con las tablas temporales tempdb .. # tb es mucho mayor.

Cuando me uno a ellos con @tb, el tiempo es mucho más largo para devolver el resultado, a diferencia de #tm , el retorno es casi instantáneo.

Hice pruebas con una combinación de 10,000 filas y una con otras 5 tablas

Answer 6

La tabla de variables está disponible solo para la sesión actual, por ejemplo, si necesita EXEC otro procedimiento almacenado dentro del actual, deberá pasar la tabla como Table Valued Parameter y, por supuesto, esto afectará el rendimiento, con tablas temporales que puede hacer esto con solo pasar el nombre de la tabla temporal

Para probar una tabla temporal:

• Editor de consultas de Open Management Studio.
• Crear una tabla temporal
• Abre otra ventana del editor de consultas
• Seleccione de esta tabla "Disponible"

Para probar una tabla de variables:

• Editor de consultas de Open Management Studio.
• Crear una tabla de variables
• Abre otra ventana del editor de consultas
• Seleccione de esta tabla "No disponible"

algo más que he experimentado es: si su esquema no tiene el privilegio GRANT para crear tablas, use tablas de variables.