He escrito un UDF con valores de tabla que comienza con un CTE para devolver un subconjunto de las filas de una tabla grande. Hay varias uniones en el CTE. Un par de tablas internas y una izquierda se unen a otras tablas, que no contienen muchas filas. El CTE tiene una cláusula where que devuelve las filas dentro de un rango de fechas, a fin de devolver solo las filas necesarias.

Luego estoy haciendo referencia a este CTE en 4 uniones de izquierda propia, para construir subtotales usando diferentes criterios.

La consulta es bastante compleja, pero aquí hay una pseudo versión simplificada.

WITH DataCTE as
(
     SELECT [columns] FROM table
                      INNER JOIN table2
                      ON [...]

                      INNER JOIN table3
                      ON [...]

                      LEFT JOIN table3
                      ON [...]
)
SELECT [aggregates_columns of each subset] FROM DataCTE Main
LEFT JOIN DataCTE BananasSubset
               ON [...] 
             AND Product = 'Bananas'
             AND Quality = 100
LEFT JOIN DataCTE DamagedBananasSubset
               ON [...]
             AND Product = 'Bananas'
             AND Quality < 20
LEFT JOIN DataCTE MangosSubset
               ON [...]
GROUP BY [

Tengo la sensación de que SQL Server se confunde y llama al CTE para cada autounión, lo que parece confirmado al mirar el plan de ejecución, aunque confieso que no soy un experto en leerlos.

Supuse que SQL Server es lo suficientemente inteligente como para realizar la recuperación de datos del CTE solo una vez, en lugar de hacerlo varias veces.

He intentado el mismo enfoque, pero en lugar de utilizar un CTE para obtener el subconjunto de datos, utilicé la misma consulta de selección que en el CTE, pero en su lugar lo envié a una tabla temporal.

La versión que hace referencia a la versión CTE tarda 40 segundos. La versión que hace referencia a la tabla temporal tarda entre 1 y 2 segundos.

¿Por qué SQL Server no es lo suficientemente inteligente como para mantener los resultados de CTE en la memoria?

Me gustan los CTE, especialmente en este caso ya que mi UDF es un valor de tabla, por lo que me permitió mantener todo en una sola declaración.

Para usar una tabla temporal, necesitaría escribir una tabla con múltiples declaraciones valorada en UDF, que encuentro una solución un poco menos elegante.

¿Algunos de ustedes tuvieron este tipo de problemas de rendimiento con CTE y, de ser así, cómo los resolvieron?

Gracias,

Kharlos