Solución:
SQL es un lenguaje declarativo, no un lenguaje de procedimientos. Es decir, construye una declaración SQL para describir los resultados que desea. No le estás diciendo al motor SQL cómo para 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. Se requieren 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 usar 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, en teorí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 (puede llamar a esta eliminación de subconsultas común).
Las tablas temporales son un asunto diferente, porque proporciona más orientación sobre cómo se debe ejecutar 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 mejoras en el rendimiento. Además, si tiene un CTE (subconsulta) complicado que se usa 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 jugar 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 a menudo lo hace, es posible que pueda encontrar una manera de obtener un mejor rendimiento.
No hay ninguna regla. Encuentro los CTE más legibles y los uso a no ser que presentan 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 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 valorada en una tabla), el 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 en particular utilizando un CTE sea menos atractiva.
Honestamente, hay demasiadas variables para proporcionar una respuesta “correcta” a su pregunta. No hay 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 sola subconsulta deberían 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 descubierto una conocida), o puede ser que sus consultas no sean semánticamente equivalentes o que contiene un elemento que frustra 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 tiene el optimizador. Personalmente, los clasifico CTE, luego subconsulta, con la tabla #temp como último recurso.
#temp está materalizado y CTE no.
CTE es solo sintaxis, por lo que en teoría es solo una subconsulta. Se ejecuta. #temp se materializa. Por lo tanto, un CTE costoso en una combinació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, pero algunas 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 ocasiones en las que el optimizador de consultas funciona mejor con un #temp en comparación con una variable de tabla.
La capacidad de crear un PK en una variable de tabla o #temp le da al optimizador de consultas más información que un CTE (ya que no puede declarar un PK en un CTE).