¿Por qué hay una ENORME diferencia de performance entre la tabla temporal y la subselección?

Esta es una pregunta acerca de SQL Server 2008 R2

No soy un DBA, de lejos. Soy un desarrollador de Java, que tiene que escribir SQL de vez en cuando. (mayormente embedded en el código). Quiero saber si hice algo mal aquí, y si es así, qué puedo hacer para evitar que vuelva a suceder.

Q1:

SELECT something FROM (SELECT * FROM T1 WHERE condition1) JOIN ... 

Q1 presenta 14 uniones

Q2 es lo mismo que Q1, con una exception. (SELECCIONE * DE T1 DONDE condición1) se ejecuta antes y se almacena en una tabla temporal.

Esta no es una subconsulta correlacionada.

Q2:

 SELECT * INTO #tempTable FROM T1 WHERE condition1 SELECT something FROM #tempTable JOIN ... 

nuevamente, 14 se unen.

Lo que me desconcierta ahora es que Q1 tomó más de 2 minutos (lo intenté varias veces evitar el almacenamiento en caching para desempeñar un papel) mientras que Q2 (ambas consultas combinadas) duró 2 segundos. ¿Lo que da?

¿Por qué no se recomienda usar subconsultas?

El optimizador de database (independientemente de la database que esté utilizando) no siempre puede optimizar adecuadamente dicha consulta (con subconsultas). En este caso, el problema para el optimizador es elegir la forma correcta de unir los sets de resultados. Hay varios algorithms para unir dos sets de resultados. La elección del algorithm depende del número de loggings que están contenidos en uno y en el otro set de resultados. En caso de que unifique dos tablas físicas (la subconsulta no es una tabla física), la database puede determinar fácilmente la cantidad de datos en dos sets de resultados según las statistics disponibles. Si uno de los sets de resultados es una subconsulta, entender cuántos loggings devuelve es muy difícil. En este caso, la database puede elegir un plan de consulta incorrecto, por lo que se networkingucirá drásticamente el performance de la consulta.

Reescribir la consulta con el uso de tablas temporales pretende simplificar el optimizador de la database. En la consulta reescrita, todos los sets de resultados que participan en las uniones serán tablas físicas y la database determinará fácilmente la longitud de cada set de resultados. Esto permitirá que la database elija el más rápido garantizado de todos los planes de consulta posibles. Además, la database tomará la decisión correcta sin importar cuáles sean las condiciones. La consulta reescrita con tablas temporales funcionaría bien en cualquier database, esto es especialmente importante en el desarrollo de soluciones portátiles. Además, la consulta reescrita es más fácil de leer, más fácil de entender y depurar.

Se entiende que reescribir la consulta con tablas temporales puede ocasionar cierta desaceleración debido a los gastos adicionales: creación de tablas temporales. Si la database no se confundirá con la opción del plan de consulta, realizará la consulta anterior más rápido que una nueva. Sin embargo, esta desaceleración siempre será insignificante. Normalmente, la creación de una tabla temporal toma unos pocos milisegundos. Es decir, la demora no puede tener un impacto significativo en el performance del sistema y, por lo general, se puede ignorar.

¡Importante! No olvides crear índices para tablas temporales. Los campos de índice deben include todos los campos que se utilizan en condiciones de unión.

Hay muchas cosas por abordar aquí, índices, planes de ejecución, etc. La testing y la comparación de resultados es el path a seguir.

Podrías echarle un vistazo a los sospechosos habituales, los índices. Echa un vistazo al plan de ejecución y compáralos. Asegúrese de que la cláusula WHERE esté utilizando las correctas. Asegúrese de estar usando los índices en sus JOINs .

Estas respuestas seguramente te ayudarán mucho.

  • Rendimiento: Subconsulta o join
  • ¿Hay una diferencia de velocidad entre las tablas CTE, SubQuery y Temp?