Cuál tiene mejor rendimiento: Tablas Derivadas o Tablas Temporales
A veces podemos escribir una consulta tanto con tabla derivada como con tabla temporal. mi pregunta es ¿cuál es mejor? ¿Por qué?
4 answers
La tabla derivada es una construcción lógica.
Puede almacenarse en el tempdb, construirse en tiempo de ejecución reevaluando la instrucción subyacente cada vez que se accede a ella, o incluso optimizarla.
La tabla temporal es una construcción física. Es una tabla en tempdb que se crea y rellena con los valores.
Cuál es mejor depende de la consulta en la que se utilizan, la instrucción que se utiliza para derivar una tabla y muchos otros factores.
Por ejemplo, CTE (expresiones de tabla comunes) en SQL Server pueden (y muy probablemente lo harán) ser reevaluadas cada vez que se usen. Esta consulta:
WITH q (uuid) AS
(
SELECT NEWID()
)
SELECT *
FROM q
UNION ALL
SELECT *
FROM q
Será muy probablemente producir dos diferentes NEWID()'s.
En este caso, se debe usar una tabla temporal ya que garantiza que sus valores persistan.
Por otro lado, esta consulta:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM master
) q
WHERE rn BETWEEN 80 AND 100
Es mejor con una tabla derivada, porque usar una tabla temporal requerirá recuperar todos los valores de master, mientras que esto la solución simplemente escaneará los primeros registros 100 usando el índice en id.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-24 14:10:29
Depende de las circunstancias.
Ventajas de las tablas derivadas:
Una tabla derivada es parte de una consulta más grande y única, y se optimizará en el contexto del resto de la consulta. Esto puede ser una ventaja, si la optimización de la consulta ayuda al rendimiento (generalmente lo hace, con algunas excepciones). Ejemplo: si rellena una tabla temporal y luego consume los resultados en una segunda consulta, en efecto está vinculando el motor de base de datos a un método de ejecución (ejecute el primero consulta en su totalidad, guardar todo el resultado, ejecutar la segunda consulta) donde con una tabla derivada el optimizador podría ser capaz de encontrar un método de ejecución más rápido o ruta de acceso.
Una tabla derivada solo "existe" en términos del plan de ejecución de la consulta - es puramente una construcción lógica. Realmente no hay mesa.
Ventajas de las tablas temporales
La tabla "existe", es decir, se materializa como una tabla, al menos en memoria, que contiene el resultado establecer y puede ser reutilizado.
En algunos casos, el rendimiento se puede mejorar o el bloqueo se puede reducir cuando se tiene que realizar una transformación elaborada de los datos - por ejemplo, si desea obtener un conjunto de filas 'instantánea' de una tabla base que está ocupada, y luego hacer algún cálculo complicado en ese conjunto, puede haber menos contención si obtiene las filas de la tabla base y las desbloquea lo más rápido posible, luego haga el trabajo de forma independiente. En algunos casos la sobrecarga de un la tabla de temperatura real es pequeña en relación con la ventaja en concurrencia.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-02-24 19:34:04
Quiero añadir una anécdota aquí, ya que me lleva a aconsejar lo contrario de la respuesta aceptada. Estoy de acuerdo con el pensamiento presentado en la respuesta aceptada, pero es principalmente teórico. Mi experiencia me ha llevado a recomendar tablas temporales sobre tablas derivadas, expresiones de tabla comunes y funciones de valor de tabla. Utilizamos tablas derivadas y expresiones de tabla comunes ampliamente con mucho éxito basado en pensamientos consistentes con la respuesta aceptada hasta que comenzamos a tratar con un resultado más grande establece y / o consultas más complejas. Luego encontramos que el optimizador no optimizaba bien con la tabla derivada o CTE.
Hoy vi un ejemplo que duró 10:15. Inserté los resultados de la tabla derivada en una tabla temporal y me uní a la tabla temporal en la consulta principal y el tiempo total se redujo a 0:03. Por lo general, cuando vemos un gran problema de rendimiento, podemos abordarlo rápidamente de esta manera. Por esta razón recomiendo tablas temporales a menos que su consulta sea relativamente simple y están seguros de que no procesará grandes conjuntos de datos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-04-25 19:47:25
La gran diferencia es que puede poner restricciones incluyendo una clave primaria en una tabla temporal. Para grandes (me refiero a millones de registros) en algún momento se puede obtener un mejor rendimiento con temporal. Tengo la consulta clave que necesita 5 joins (cada joins pasa a ser similar). El rendimiento estaba bien con 2 joins y luego en el tercer rendimiento salió mal y el plan de consulta se volvió loco. Incluso con sugerencias no pude corregir el plan de consulta. Intentó reestructurar las uniones como tablas derivadas y sigue siendo el mismo problemas de rendimiento. Con las tablas temporales se puede crear una clave primaria (a continuación, cuando se rellena la primera ordenación en PK). Cuando SQL podía unirse a las 5 tablas y utilizar el rendimiento PK pasó de minutos a segundos. Me gustaría que SQL admitiera restricciones en tablas derivadas y CTE (incluso si solo es un PK).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-08-07 14:14:17