¿Existe alguna diferencia de rendimiento entre CTE, subconsulta, tabla temporal o variable de tabla?
En esta excelente pregunta SO , se discutieron las diferencias entre CTEy .sub-queries
Me gustaría preguntar específicamente:
¿En qué circunstancias cada uno de los siguientes es más eficiente/más rápido?
- CTE
- Subconsulta
- Mesa Temporal
- Variables de tabla
Tradicionalmente, he usado muchas temp tablesen el desarrollo stored procedures, ya que parecen más legibles que muchas subconsultas entrelazadas.
Non-recursive CTELos s encapsulan conjuntos de datos muy bien y son muy legibles, pero ¿existen circunstancias específicas en las que se puede decir que siempre funcionarán mejor? ¿O se trata de tener que juguetear 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 primera opción ya que tienen un histograma asociado, es decir, estadísticas.
SQL es un lenguaje declarativo, no un lenguaje procedimental. Es decir, 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 de SQL encuentren el mejor plan de consulta. Se necesitan 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. En teoría, el rendimiento de los CTE y las subconsultas debería ser 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 y calcularse fácilmente una vez. Luego, los resultados podrían almacenarse y leerse varias veces. Desafortunadamente, SQL Server no parece aprovechar este método de optimización básico (a esto se le podría llamar eliminación de subconsultas común).
Las tablas temporales son un asunto diferente, porque brindan más orientación sobre cómo se debe ejecutar la consulta. Una diferencia importante es que el optimizador puede utilizar estadísticas de la tabla temporal para establecer su plan de consultas. Esto puede resultar en mejoras de rendimiento. Además, si tiene un CTE (subconsulta) complicado que se utiliza más de una vez, almacenarlo en una tabla temporal a menudo aumentará el rendimiento. La consulta se ejecuta solo una vez.
La respuesta a su pregunta es que necesita probar para obtener el rendimiento que espera, especialmente para 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 suele ser así, es posible que pueda encontrar una manera de obtener un mejor rendimiento.
No hay ninguna regla. Considero que los CTE son más legibles y los uso 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 e intento reescribirlo usando un enfoque diferente. Por lo general, hay más en el problema que la forma en que elegí declarar mis intenciones con la consulta.
Ciertamente, hay casos en los que puede desentrañar CTE o eliminar subconsultas y reemplazarlas con una tabla #temp y reducir la duración. Esto puede deberse a varias cosas, como estadísticas obsoletas, la incapacidad de obtener estadísticas precisas (por ejemplo, unirse a una función con valores de tabla), paralelismo o incluso la incapacidad de generar un plan óptimo debido a la complejidad de la consulta ( en cuyo caso dividirlo puede darle al optimizador una oportunidad de luchar). 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.
Sinceramente, hay demasiadas variables para dar 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 sepa que, en teoría, la misma semántica para un CTE o una única subconsulta debería ejecutar exactamente lo mismo. Creo que su pregunta sería más valiosa si presenta algunos casos en los que esto no es cierto: puede ser que haya descubierto una limitación en el optimizador (o haya descubierto una conocida), o puede ser que sus consultas no sean semánticamente equivalentes. o que contiene un elemento que impide la optimización.
Por lo tanto, sugeriría escribir la consulta de la manera que le parezca más natural y solo desviarse cuando descubra un problema de rendimiento real que está teniendo el optimizador. Personalmente, los clasifico como CTE y luego como subconsulta, siendo la tabla #temp el último recurso.
