eliminar - tablas temporales vs variables tipo tabla sql server

¿Hay una diferencia de rendimiento entre CTE, Sub-Query, Tabla temporal o Variable de tabla? (4)

#temp está materalizado y CTE no.

CTE es solo sintaxis, así que en teoría es solo una subconsulta. Se ejecuta. #temp se materializa. Entonces un costoso CTE en una unión que se ejecuta muchas veces puede ser mejor en un #temp. Por otro lado, si se trata de una evaluación fácil que no se ejecuta sino unas pocas veces, entonces no vale la pena la sobrecarga de #temp.

Hay algunas personas en SO a las que no les gusta la variable de tabla pero me gustan porque están materializadas y son más rápidas de crear que #temp. Hay momentos en que el optimizador de consultas mejora con un #temp en comparación con una variable de tabla.

La capacidad de crear un PK en una variable #temp o tabla le da al optimizador de consultas más información que un CTE (ya que no se puede declarar un PK en un CTE).

En esta excelente pregunta SO , las diferencias entre CTE y sub-queries se discutieron.

Me gustaría preguntar específicamente:

¿En qué circunstancias cada uno de los siguientes es más eficiente / más rápido?

CTE
Sub-Query
Tabla temporal
Variable de tabla

Tradicionalmente, he usado muchas temp tables para desarrollar stored procedures , ya que parecen más legibles que muchas subconsultas entrelazadas.

Non-recursive CTE encapsulan muy bien conjuntos de datos, y son muy legibles, pero ¿hay circunstancias específicas en las que se pueda decir que siempre tendrán un mejor rendimiento? ¿o se trata de tener que jugar siempre con las diferentes opciones para encontrar la solución más eficiente?

EDITAR

Recientemente me dijeron que, en términos de eficiencia, las tablas temporales son una buena opción ya que tienen un histograma asociado, es decir, estadísticas.

No hay regla Encuentro que los CTE son más legibles y los utilizo a menos que presenten algún problema de rendimiento, en cuyo caso investigo el problema real en lugar de adivinar que el CTE es el problema y trato de volver a escribirlo utilizando un enfoque diferente. Generalmente hay más en el tema que la forma en que elegí declarar declarativamente mis intenciones con la consulta.

Ciertamente, hay casos en los que puede desentrañar CTE o eliminar subconsultas y reemplazarlos con una tabla #temp y reducir la duración. Esto puede deberse a varias cosas, como estadísticas viciadas, la incapacidad de obtener estadísticas precisas (por ejemplo, unirse a una función con valores de tabla), el paralelismo o incluso la incapacidad de generar un plan óptimo debido a la complejidad de la consulta ( en cuyo caso, dividirlo puede dar al optimizador una posibilidad de lucha). Pero también hay casos en los que la E / S involucrada en la creación de una tabla #temp puede superar los otros aspectos de rendimiento que pueden hacer que una forma de plan particular usando un CTE sea menos atractiva.

Honestamente, hay demasiadas variables para proporcionar una respuesta "correcta" a su pregunta. No existe una forma predecible de saber cuándo una consulta puede inclinarse a favor de un enfoque u otro; solo debe saber que, en teoría, la misma semántica para un CTE o una única subconsulta debe ejecutar exactamente lo mismo. Creo que su pregunta sería más valiosa si presenta algunos casos en los que esto no es cierto, es posible que haya descubierto una limitación en el optimizador (o que haya descubierto una conocida) o que sus consultas no sean semánticamente equivalentes. o que uno contiene un elemento que frustra la optimización.

Por lo tanto, sugeriría que escribas la consulta de una manera que parezca más natural para ti, y solo se desviará cuando descubras un problema de rendimiento real que está teniendo el optimizador. Personalmente los clasifico como CTE, luego como subconsulta, y la tabla #temp como último recurso.

SQL es un lenguaje declarativo, no un lenguaje de procedimiento. Es decir, usted construye una declaración SQL para describir los resultados que desea. No le está diciendo al motor SQL cómo hacer el trabajo.

Como regla general, es una buena idea dejar que el motor SQL y el optimizador SQL encuentren el mejor plan de consulta. Hay muchos años-persona de esfuerzo para desarrollar un motor SQL, así que deje que los ingenieros hagan lo que saben hacer.

Por supuesto, hay situaciones en las que el plan de consulta no es óptimo. Luego, desea utilizar sugerencias de consulta, reestructurar la consulta, actualizar estadísticas, usar tablas temporales, agregar índices, etc. para obtener un mejor rendimiento.

En cuanto a tu pregunta El rendimiento de los CTE y las subconsultas debería ser, en teoría, el mismo, ya que ambos proporcionan la misma información al optimizador de consultas. Una diferencia es que un CTE utilizado más de una vez podría identificarse fácilmente y calcularse una vez. Los resultados podrían almacenarse y leerse varias veces. Desafortunadamente, SQL Server no parece aprovechar este método de optimización básico (puede llamar a esta eliminación de subconsulta común).

Las tablas temporales son una cuestión diferente, ya que proporciona más orientación sobre cómo debe ejecutarse la consulta. Una diferencia importante es que el optimizador puede usar estadísticas de la tabla temporal para establecer su plan de consulta. Esto puede resultar en ganancias de rendimiento. Además, si tiene un CTE (subconsulta) complicado que se usa más de una vez, almacenarlo en una tabla temporal a menudo dará un impulso en el rendimiento. La consulta se ejecuta solo una vez.

La respuesta a su pregunta es que necesita jugar para obtener el rendimiento que espera, especialmente para las consultas complejas que se ejecutan de forma regular. En un mundo ideal, el optimizador de consultas encontraría la ruta de ejecución perfecta. Aunque a menudo sí lo hace, es posible que pueda encontrar una manera de obtener un mejor rendimiento.

Solo dos cosas creo que SIEMPRE es preferible usar una # Tabla de temperatura en lugar de un CTE:

No puede poner una clave principal en un CTE para que los datos a los que accede el CTE tengan que atravesar cada uno de los índices en las tablas del CTE en lugar de simplemente acceder al PK o al índice en la tabla temporal.
Debido a que no puede agregar restricciones, índices y claves principales a un CTE, son más propensos a que los errores ingresen y los datos sean malos.

-un día cuando ayer

Aquí hay un ejemplo donde las restricciones #table pueden evitar datos incorrectos, que no es el caso en CTE

DECLARE @BadData TABLE ( ThisID int , ThatID int ); INSERT INTO @BadData ( ThisID , ThatID ) VALUES ( 1, 1 ), ( 1, 2 ), ( 2, 2 ), ( 1, 1 ); IF OBJECT_ID(''tempdb..#This'') IS NOT NULL DROP TABLE #This; CREATE TABLE #This ( ThisID int NOT NULL , ThatID int NOT NULL UNIQUE(ThisID, ThatID) ); INSERT INTO #This SELECT * FROM @BadData; WITH This_CTE AS (SELECT * FROM @BadData) SELECT * FROM This_CTE;