Elegir entre variables de tabla y tablas temporales (ST011, ST012)

La gente puede, y lo hace, discutir mucho sobre los méritos relativos de las variables de tabla y las tablas temporales. A veces, como cuando escribe funciones, no tiene otra opción; pero cuando lo haga, encontrará que ambos tienen sus usos y es fácil encontrar ejemplos en los que cualquiera de los dos es más rápido. En este artículo, explicaré los factores principales que intervienen en la elección de uno u otro, y demostraré algunas «reglas» simples para obtener el mejor rendimiento.

Suponiendo que sigue las reglas básicas de participación , entonces debería considerar las variables de la tabla como primera opción cuando trabaje con conjuntos de datos relativamente pequeños. Es más fácil trabajar con ellos y activan menos recompilaciones en las rutinas en las que se utilizan, en comparación con el uso de tablas temporales. Las variables de tabla también requieren menos recursos de bloqueo, ya que son «privadas» para el proceso y el lote que las creó. SQL Prompt implementa esta recomendación como una regla de análisis de código, ST011: considere usar una variable de tabla en lugar de una tabla temporal.

Si está realizando un procesamiento más complejo en datos temporales, o necesita usar cantidades más que razonablemente pequeñas de datos en ellos, es probable que las tablas temporales locales sean una mejor opción. SQL Code Guard incluye una regla de análisis de código, basada en su recomendación, ST012: considere usar una tabla temporal en lugar de una variable de tabla, pero actualmente no está implementada en el indicador SQL.

Pros y contras de las variables de tabla y las tablas temporales

Las variables de tabla tienden a tener mala prensa, porque las consultas que las usan ocasionalmente dan como resultado planes de ejecución muy ineficientes. Sin embargo, si sigue algunas reglas simples, son una buena opción para tablas de trabajo intermedias y para pasar resultados entre rutinas, donde los conjuntos de datos son pequeños y el procesamiento requerido es relativamente sencillo.

Las variables de tabla son muy sencillas de usar, principalmente porque son de «mantenimiento cero». Están en el ámbito del lote o la rutina en la que se crean y se eliminan automáticamente una vez que se completa la ejecución, por lo que se utilizan dentro de una conexión de larga duración. no se arriesga a problemas de acaparamiento de recursos en tempdb. Si una variable de tabla se declara en un procedimiento almacenado, es local para ese procedimiento almacenado y no puede ser referenciada en un procedimiento anidado. Tampoco hay recompilaciones basadas en estadísticas para variables de tabla y no puede ALTER uno, por lo que las rutinas que las usan tienden a generar menos recompilaciones que las que usan tablas temporales. Tampoco están completamente registradas, por lo que crearlas y completarlas es más rápido y requiere menos espacio en la t registro de transacciones. Cuando se utilizan en procedimientos almacenados, hay menos contención en las tablas del sistema, en condiciones de alta concurrencia. En resumen, es más fácil mantener las cosas ordenadas y ordenadas.

Cuando se trabaja con conjuntos de datos relativamente pequeños, son más rápidos que la tabla temporal comparable. Sin embargo, a medida que aumenta el número de filas, más allá de aproximadamente 15.000 filas, pero varía según el contexto, puede encontrarse con dificultades, principalmente debido a su falta de compatibilidad con las estadísticas. Incluso los índices que imponen restricciones PRIMARY KEY y UNIQUE en las variables de la tabla no tienen estadísticas . Por lo tanto, el optimizador utilizará una estimación codificada de 1 fila devuelta de una variable de tabla y, por lo tanto, tenderá a elegir operadores óptimos para trabajar con conjuntos de datos pequeños (como el operador Nested Loops para combinaciones). Cuantas más filas haya en la variable de la tabla, mayores serán las discrepancias entre la estimación y la realidad, y más ineficaces serán las opciones de plan del optimizador. El plan resultante es a veces espantoso.

El desarrollador experimentado o DBA estará atento a este tipo de problema y estará listo para agregar el OPTION (RECOMPILE) sugerencia de consulta a la declaración que usa la variable de tabla. Cuando enviamos un lote que contiene una variable de tabla, el optimizador primero compila el lote en cuyo punto la variable de la tabla está vacía. Cuando el lote comienza a ejecutarse, la sugerencia hará que solo se vuelva a compilar esa declaración, momento en el que se completará la variable de la tabla y el optimizador puede usar el recuento de filas real para compilar un nuevo plan para esa declaración. A veces, pero rara vez, ni siquiera esto ayudará. Además, la dependencia excesiva de esta sugerencia anulará hasta cierto punto la ventaja que tienen las variables de tabla de causar menos recompilaciones que las tablas temporales.

En segundo lugar, ciertas limitaciones de índice con variables de tabla se vuelven más importantes cuando se trata de grandes conjuntos de datos. Si bien ahora puede usar la sintaxis de creación de índices en línea para crear índices no agrupados en una variable de tabla, existen algunas restricciones y todavía no hay estadísticas asociadas.

Incluso con recuentos de filas relativamente modestos, puede encontrar problemas de rendimiento de consultas si intenta ejecutar una consulta que es una combinación y se olvida de definir un PRIMARY KEY o UNIQUE restricción en la columna que está usando para la combinación. Sin los metadatos que proporcionan, el optimizador no tiene conocimiento del orden lógico de los datos, o si los datos en la columna de combinación contienen valores duplicados, y probablemente elegirá operaciones de combinación ineficientes, lo que resultará en consultas lentas. Si está trabajando con un montón de variables de tabla, solo puede usarlo como una lista simple que probablemente se procese de un solo trago (escaneo de tabla). Si combina el uso de la pista OPTION (RECOMPILE), para obtener estimaciones de cardinalidad precisas, y una clave en la columna de combinación para que el optimizador sea útil metadatos, para conjuntos de datos más pequeños a menudo puede lograr velocidades de consulta similares o mejores que usar una tabla temporal local.

Una vez que el recuento de filas aumenta más allá de la zona de confort de una variable de tabla, o necesita hacer datos más complejos procesamiento, entonces es mejor cambiar para usar tablas temporales. Aquí, tiene todas las opciones disponibles para la indexación, y el optimizador tendrá el lujo de usar estadísticas para cada uno de estos índices. Por supuesto, la desventaja es que las mesas temporales tienen un costo de mantenimiento más alto. Debe asegurarse de limpiar después de usted mismo, para evitar la congestión de tempdb. Si altera una tabla temporal, o modifica los datos en ella, puede incurrir en recompilaciones de la rutina padre.

Las tablas temporales son mejores cuando hay un requisito para una gran cantidad de eliminaciones e inserciones (uso compartido de conjuntos de filas ). Esto es especialmente cierto si los datos deben eliminarse por completo de la tabla, ya que solo las tablas temporales admiten el truncamiento. Los compromisos en el diseño de variables de tabla, como la falta de estadísticas y recompilaciones, funcionan en contra de ellos si los datos son volátiles.

Cuando vale la pena usar variables de tabla

Nosotros Comenzaremos con un ejemplo donde una variable de tabla es ideal y da como resultado un mejor rendimiento. Produciremos una lista de empleados para Adventureworks, en qué departamento trabajan y los turnos en los que trabajan. Estamos tratando con un pequeño conjunto de datos (291 filas).

Pondremos los resultados en una segunda tabla temporal, como si estuviéramos pasando el resultado al siguiente lote. El Listado 1 muestra el código.

Y aquí hay un resultado típico en mi máquina de prueba lenta:

El uso de una tabla temporal es consistentemente más lento, aunque las ejecuciones individuales pueden variar bastante.

Los problemas de escala y el olvido de proporcionar una clave o una pista

¿Cómo es el rendimiento si unimos dos variables de tabla? Probémoslo. Para este ejemplo, necesitamos dos tablas simples, una con todas las palabras comunes en el idioma inglés (CommonWords), y la otra con una lista de todas las palabras en Drácula de Bram Stoker. (WordsInDracula). La descarga de TestTVsAndTTs incluye el script para crear estas dos tablas y completar cada una desde su archivo de texto asociado. Hay 60,000 palabras comunes, pero Bram Stoker solo usó 10,000 de ellas. El primero está fuera del punto de equilibrio, donde uno comienza a preferir tablas temporales.

Usaremos cuatro consultas de combinación externa simples, probando el resultado para NULL valores, para averiguar las palabras comunes que no están en Drácula, palabras comunes que están en Drácula, palabras en Drácula que son poco comunes y finalmente otra consulta para encontrar palabras comunes en Drácula, pero uniéndose en la dirección opuesta. Verá las consultas en breve, cuando muestre el código del equipo de prueba.

A continuación se muestran los resultados de las ejecuciones de prueba iniciales. En la primera ejecución, ambas variables de la tabla tienen claves primarias, y en la segunda ambas son montones, solo para ver si exagero los problemas de no declarar un índice en una variable de tabla. Finalmente, ejecutamos las mismas consultas con tablas temporales. Todas las pruebas se ejecutaron, deliberadamente, en un servidor de desarrollo lento, con fines ilustrativos; obtendrá resultados muy diferentes con un servidor de producción.

Los resultados muestran que cuando las variables de la tabla son montones, corre el riesgo de que la consulta se ejecute durante diez minutos en lugar de 100 milisegundos. Estos son un gran ejemplo del horrible desempeño que puede experimentar si no conoce las reglas. Sin embargo, incluso cuando usamos claves primarias, la cantidad de filas con las que estamos tratando significa que usar tablas temporales ahora es dos veces más rápido.

No profundizaré en los detalles de los planes de ejecución detrás de estas métricas de rendimiento, además de dar algunas explicaciones generales de las principales diferencias. Para las consultas de tabla temporal, el optimizador, armado con un conocimiento completo de la cardinalidad y los metadatos de las restricciones de la clave principal, elige un operador Merge Join eficiente para realizar la operación de combinación.Para la variable de tablas con claves primarias, el optimizador conoce el orden de las filas en la columna de combinación y que no contienen duplicados, pero asume que solo se trata de una fila, por lo que elige una combinación de bucles anidados. Aquí, escanea una tabla y luego, para cada fila devuelta, realiza búsquedas individuales de la otra tabla. Esto se vuelve menos eficiente cuanto más grandes son los conjuntos de datos y es especialmente malo en los casos en los que escanea la variable de la tabla CommonWords, porque da como resultado más de 60K búsquedas de Dracula variable de tabla. La combinación de Nested Loops alcanza el «pico de ineficiencia» para dos consultas de diez minutos utilizando montones de variables de tabla, porque implica miles de escaneos de tabla de CommonWords. Curiosamente, las dos consultas de «palabras comunes en Drácula» funcionan mucho mejor y esto se debe a que, para esas dos, el optimizador eligió en su lugar una combinación Hash Match.

En general, las tablas temporales parecen ser la mejor opción , pero aún no hemos terminado. Agreguemos la sugerencia OPTION (RECOMPILE) a las consultas que usan las variables de tabla con claves primarias, y vuelva a ejecutar las pruebas para estas consultas y las consultas originales utilizando las tablas temporales. Dejamos de lado los montones pobres por el momento.

Como puede ver, la ventaja de rendimiento de la tabla temporal desaparece. recuentos de filas correctos y entradas ordenadas, el optimizador elige la combinación de combinación mucho más eficiente.

¿Qué pasaría, te preguntarás, si le dieras a esos montones pobres el OPTION (RECOMPILE) ¿sugerencia también? Mira, la historia cambia para ellos de modo que los tres tiempos están mucho más cerca.

Curiosamente, las dos consultas de «palabras comunes en Drácula» que fueron rápido incluso en montones ahora es mucho más lento. Armado con los recuentos de filas correctos, el optimizador cambia su estrategia, pero debido a que todavía no tiene ninguno de los metadatos útiles disponibles cuando definimos restricciones y claves, hace una mala elección. Escanea el montón CommonWords y luego intenta una «agregación parcial», estimando que se agregará de 60K filas a unos pocos cientos. No sabe que no hay duplicados, por lo que de hecho, no se agrega en absoluto, y la agregación y la posterior unión se derraman en tempdb.

La plataforma de prueba

Tenga en cuenta que esta es la plataforma de prueba en su forma final mostrando un rendimiento aproximadamente igual para los tres tipos diferentes de tabla. Deberá eliminar las OPTION (RECOMPILE) sugerencias para volver al original.

Listado 2

Conclusiones

No hay nada imprudente en el uso de variables de tabla. Ofrecen un mejor rendimiento cuando se utilizan para los fines para los que fueron diseñados y hacen su propia limpieza. En cierto punto, los compromisos que les dan un mejor rendimiento (no activar recompilaciones, no proporcionar estadísticas, no retroceder, no hay paralelismo) se convierten en su ruina.

A menudo, el experto en SQL Server dará sabios consejos sobre el tamaño del resultado que causará problemas para una variable de tabla. Los resultados que le he mostrado en este artículo le sugerirán que esto simplifica demasiado los problemas. Hay dos factores importantes: si tiene un resultado de más, digamos, 1000 filas (y esta cifra depende del contexto), entonces debe tener un PRIMARY KEY o la tecla UNIQUE para cualquier consulta que se una a una variable de tabla. En cierto punto, también necesitará activar una recompilación para obtener un plan de ejecución decente, que tiene sus propios gastos generales.

Incluso entonces, el rendimiento puede sufrir mucho, especialmente si está realizando un procesamiento más complejo. , porque el optimizador aún no tiene acceso a las estadísticas y, por lo tanto, no tiene conocimiento de la selectividad de ningún predicado de consulta. En tales casos, deberá cambiar al uso de tablas temporales.

Lecturas adicionales