ejemplos - INNER JOIN vs LEFT JOIN rendimiento en SQL Server

inner join sql server ejemplos

Question

He creado un comando SQL que usa INNER JOIN para 9 tablas, de todas formas este comando lleva mucho tiempo (más de cinco minutos). Así que mi gente me sugiere cambiar INNER JOIN a LEFT JOIN porque el rendimiento de LEFT JOIN es mejor, ya que la primera vez es a pesar de lo que sé. Después de que cambié, la velocidad de consulta se mejora significativamente.

Me gustaría saber por qué LEFT JOIN es más rápido que INNER JOIN?

Mi comando SQL tiene el siguiente aspecto: SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D y así sucesivamente

Actualización: Este es un resumen de mi esquema.

FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
    INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
        ON a.CompanyCd = b.CompanyCd 
           AND a.SPRNo = b.SPRNo 
           AND a.SuffixNo = b.SuffixNo 
           AND a.dnno = b.dnno
    INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
        ON a.CompanyCd = h.CompanyCd
           AND a.sprno = h.AcctSPRNo
    INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
        ON c.CompanyCd = h.CompanyCd
           AND c.FSlipNo = h.FSlipNo 
           AND c.FSlipSuffix = h.FSlipSuffix 
    INNER JOIN coMappingExpParty d -- NO PK AND FK
        ON c.CompanyCd = d.CompanyCd
           AND c.CountryCd = d.CountryCd 
    INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
        ON b.CompanyCd = e.CompanyCd
           AND b.ProductSalesCd = e.ProductSalesCd 
    LEFT JOIN coUOM i -- PK = UOMId
        ON h.UOMId = i.UOMId 
    INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
        ON a.CompanyCd = j.CompanyCd
            AND b.BFStatus = j.BFStatus
            AND b.ProductSalesCd = j.ProductSalesCd
    INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
        ON e.ProductGroup1Cd  = g1.ProductGroup1Cd
    INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
        ON e.ProductGroup1Cd  = g2.ProductGroup1Cd

Answer 1

Encontré algo interesante en el servidor SQL cuando compruebo si las uniones internas son más rápidas que las de la izquierda.

Si no incluye los elementos de la tabla combinada a la izquierda, en la declaración de selección, la combinación a la izquierda será más rápida que la misma consulta con la combinación interna.

Si incluye la tabla combinada a la izquierda en la instrucción de selección, la combinación interna con la misma consulta fue igual o más rápida que la combinación izquierda.

Answer 2

Es más probable que sus problemas de rendimiento se deban a la cantidad de uniones que está haciendo y si las columnas a las que se une tienen índices o no.

En el peor de los casos, podría estar haciendo 9 exploraciones de tablas completas para cada combinación.

Answer 3

Existe un escenario importante que puede hacer que una combinación externa sea más rápida que una combinación interna que aún no se haya discutido.

Cuando se usa una combinación externa, el optimizador siempre tiene la libertad de eliminar la tabla externa unida del plan de ejecución si las columnas de combinación son el PK de la tabla externa, y ninguna de las columnas se selecciona de la tabla externa. Por ejemplo, SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY y B.KEY es la PK para B. Ambos, Oracle (creo que estaba usando la versión 10) y el servidor Sql (utilicé 2008 R2) podar la tabla B del plan de ejecución.

Lo mismo no es necesariamente cierto para una unión interna: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY puede o no requerir B en el plan de ejecución, dependiendo de las restricciones existentes.

Si A.KEY es una clave foránea anulable que hace referencia a B.KEY, entonces el optimizador no puede eliminar B del plan porque debe confirmar que existe una fila B para cada fila A.

Si A.KEY es una clave foránea obligatoria que hace referencia a B.KEY, entonces el optimizador es libre de eliminar a B del plan porque las restricciones garantizan la existencia de la fila. Pero solo porque el optimizador puede eliminar la tabla del plan, no significa que lo hará. SQL Server 2008 R2 NO elimina B del plan. Oracle 10 DEBE quitar B del plan. Es fácil ver cómo la unión externa superará a la unión interna en SQL Server en este caso.

Este es un ejemplo trivial, y no es práctico para una consulta independiente. ¿Por qué unirse a una mesa si no es necesario?

Pero esto podría ser una consideración de diseño muy importante al diseñar vistas. Con frecuencia, se construye una vista de "hacer todo" que une todo lo que un usuario puede necesitar en relación con una tabla central. (Especialmente si hay usuarios ingenuos que realizan consultas ad hoc que no entienden el modelo relacional) La vista puede incluir todas las columnas relevantes de muchas tablas. Pero los usuarios finales solo pueden acceder a las columnas de un subconjunto de las tablas dentro de la vista. Si las tablas se unen con uniones externas, el optimizador puede (y lo hace) eliminar las tablas innecesarias del plan.

Es fundamental asegurarse de que la vista que usa uniones externas dé los resultados correctos. Como ha dicho Aaronaught, no puedes sustituir ciegamente a OUTER JOIN por INNER JOIN y esperar los mismos resultados. Pero hay ocasiones en que puede ser útil por razones de rendimiento al usar vistas.

Una última nota: no he probado el impacto en el rendimiento a la luz de lo anterior, pero en teoría parece que debería poder reemplazar de forma segura una UNIÓN INTERIOR con una JUNTA EXTERNA si también agrega la condición <FOREIGN_KEY> NO ES NULA a la cláusula where.

Answer 4

Haber hecho una serie de comparaciones entre las combinaciones internas y externas de la izquierda y no haber podido encontrar una diferencia consistente. Hay muchas variables. Estoy trabajando en una base de datos de informes con miles de tablas, muchas con una gran cantidad de campos, muchos cambios a lo largo del tiempo (versiones de proveedores y flujo de trabajo local). No es posible crear todas las combinaciones de índices de cobertura para satisfacer las necesidades de una variedad tan amplia de consultas y manejar datos históricos. Han visto que las consultas internas eliminan el rendimiento del servidor debido a que dos tablas grandes (de millones a decenas de millones de filas) están unidas internamente, tanto en la extracción de una gran cantidad de campos como en la ausencia de índice de cobertura

El mayor problema, sin embargo, no parece verse en las discusiones anteriores. Tal vez su base de datos esté bien diseñada con activadores y un procesamiento de transacciones bien diseñado para garantizar una buena información. El mío frecuentemente tiene valores NULL donde no son esperados. Sí, las definiciones de la tabla podrían imponer no nulos, pero esa no es una opción en mi entorno.

Entonces, la pregunta es ... ¿diseña su consulta solo para la velocidad, una prioridad más alta para el procesamiento de transacciones que ejecuta el mismo código miles de veces por minuto? ¿O busca la precisión que proporcionará una unión externa izquierda? Recuerde que las combinaciones internas deben encontrar coincidencias en ambos lados, por lo que un NULL inesperado no solo eliminará los datos de las dos tablas, sino también las filas completas de información. Y sucede muy bien, no hay mensajes de error.

Puede ser muy rápido si obtiene el 90% de los datos necesarios y no descubre que las uniones internas han eliminado la información de forma silenciosa. Algunas veces las uniones internas pueden ser más rápidas, pero no creo que nadie haga esa suposición a menos que haya revisado el plan de ejecución. La velocidad es importante, pero la precisión es más importante.

Answer 5

Las uniones externas pueden ofrecer un rendimiento superior cuando se usan en vistas.

Supongamos que tiene una consulta que implica una vista, y esa vista se compone de 10 tablas unidas. Digamos que su consulta solo utiliza columnas de 3 de esas 10 tablas.

Si esas 10 tablas hubieran sido unidas internamente, entonces el optimizador de consultas tendría que unirlas todas a pesar de que la consulta en sí no necesita 7 de cada 10 de las tablas. Esto se debe a que las uniones internas podrían filtrar los datos, lo que los hace esenciales para el cálculo.

Si esas 10 tablas se hubieran unido externamente en su lugar, entonces el optimizador de consultas solo uniría las que eran necesarias: 3 de cada 10 de ellas en este caso. Esto se debe a que las uniones ya no filtran los datos y, por lo tanto, se pueden omitir las combinaciones no utilizadas.

Fuente: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/

Answer 6

Pruebe ambas consultas (la que tiene una unión interna y una izquierda) con OPTION (FORCE ORDER) al final y publique los resultados. OPTION (FORCE ORDER) es una sugerencia de consulta que obliga al optimizador a construir el plan de ejecución con el orden de unión que proporcionó en la consulta.

Si INNER JOIN comienza a funcionar tan rápido como LEFT JOIN , es porque:

• En una consulta compuesta en su totalidad por INNER JOIN s, la orden de unión no importa. Esto da libertad para que el optimizador de consultas ordene las uniones como mejor le parezca, por lo que el problema podría depender del optimizador.
• Con LEFT JOIN , ese no es el caso porque cambiar el orden de unión alterará los resultados de la consulta. Esto significa que el motor debe seguir el orden de unión que proporcionó en la consulta, que podría ser mejor que el optimizado.

No sé si esto responde a tu pregunta, pero una vez estuve en un proyecto que presentaba consultas muy complejas que hacían cálculos, lo que desbarataba completamente el optimizador. Tuvimos casos en los que una FORCE ORDER reduciría el tiempo de ejecución de una consulta de 5 minutos a 10 segundos.

Answer 7

Si todo funciona como debería, BUT, todos sabemos que no funciona como debería, especialmente en lo que respecta al optimizador de consultas, el almacenamiento en caché del plan de consulta y las estadísticas.

Primero sugeriría reconstruir el índice y las estadísticas, luego borrar la memoria caché del plan de consulta solo para asegurarse de que no esté arruinando las cosas. Sin embargo, he experimentado problemas incluso cuando se hace eso.

He experimentado algunos casos donde una unión izquierda ha sido más rápida que una unión interna.

El motivo subyacente es el siguiente: si tiene dos tablas y se une en una columna con un índice (en ambas tablas). La unión interna producirá el mismo resultado sin importar si recorre las entradas en el índice en la tabla uno y coincide con el índice en la tabla dos como si fuera a hacer lo contrario: recorra las entradas en el índice en la tabla dos y haga coincidir con el índice en la tabla uno. El problema es que cuando tiene estadísticas confusas, el optimizador de consultas usará las estadísticas del índice para encontrar la tabla con las entradas menos coincidentes (según sus otros criterios). Si tiene dos tablas con 1 millón en cada una, en la tabla uno tiene 10 filas coincidentes y en la tabla dos tiene 100000 filas coincidentes. La mejor manera sería hacer un escaneo de índice en la tabla uno y unir 10 veces en la tabla dos. Lo contrario sería una exploración de índice que recorre más de 100000 filas y trata de coincidir 100 000 veces y solo 10 tienen éxito. Entonces, si las estadísticas no son correctas, el optimizador podría elegir la tabla y el índice incorrectos para pasar.

Si el optimizador elige optimizar la combinación izquierda en el orden en que está escrito, funcionará mejor que la combinación interna.

PERO, el optimizador también puede optimizar una combinación izquierda subóptimamente como una combinación izquierda. Para que elija la que desea, puede utilizar la sugerencia de orden de fuerza.

Answer 8

Una LEFT JOIN no es absolutamente más rápida que una INNER JOIN . De hecho, es más lento; por definición, una combinación externa ( LEFT JOIN o LEFT JOIN RIGHT JOIN ) tiene que hacer todo el trabajo de una INNER JOIN más el trabajo adicional de extender nulos los resultados. También se esperaría que devolviera más filas, lo que aumentaría aún más el tiempo total de ejecución simplemente debido al mayor tamaño del conjunto de resultados.

(E incluso si un LEFT JOIN fuera más rápido en situaciones específicas debido a una confluencia de factores difíciles de imaginar, no es funcionalmente equivalente a un INNER JOIN , por lo que no puede simplemente reemplazar todas las instancias de uno con el otro.)

Lo más probable es que sus problemas de rendimiento se encuentren en otra parte, como no tener una clave candidata o una clave externa correctamente indexada. 9 mesas es mucho para unirse, por lo que la desaceleración podría estar literalmente en cualquier lugar. Si publica su esquema, podríamos proporcionarle más detalles.

Editar:

Reflexionando sobre esto, podría pensar en una circunstancia en la que una LEFT JOIN podría ser más rápida que una INNER JOIN , y es cuando:

• Algunas de las tablas son muy pequeñas (por ejemplo, menos de 10 filas);
• Las tablas no tienen índices suficientes para cubrir la consulta.

Considera este ejemplo:

CREATE TABLE #Test1
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, ''One'')
INSERT #Test1 (ID, Name) VALUES (2, ''Two'')
INSERT #Test1 (ID, Name) VALUES (3, ''Three'')
INSERT #Test1 (ID, Name) VALUES (4, ''Four'')
INSERT #Test1 (ID, Name) VALUES (5, ''Five'')

CREATE TABLE #Test2
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, ''One'')
INSERT #Test2 (ID, Name) VALUES (2, ''Two'')
INSERT #Test2 (ID, Name) VALUES (3, ''Three'')
INSERT #Test2 (ID, Name) VALUES (4, ''Four'')
INSERT #Test2 (ID, Name) VALUES (5, ''Five'')

SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name

SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name

DROP TABLE #Test1
DROP TABLE #Test2

Si ejecuta esto y ve el plan de ejecución, verá que la consulta INNER JOIN cuesta más que la LEFT JOIN , ya que cumple con los dos criterios anteriores. Es porque SQL Server quiere hacer una coincidencia de hash para INNER JOIN , pero hace bucles anidados para LEFT JOIN ; El primero suele ser mucho más rápido, pero como el número de filas es muy pequeño y no hay un índice para usar, la operación de hashing resulta ser la parte más costosa de la consulta.

Puede ver el mismo efecto escribiendo un programa en su lenguaje de programación favorito para realizar una gran cantidad de búsquedas en una lista con 5 elementos, en lugar de una tabla hash con 5 elementos. Debido al tamaño, la versión de la tabla hash es en realidad más lenta. Pero aumente a 50 elementos, o 5000 elementos, y la versión de la lista se reduce a un rastreo, porque es O (N) frente a O (1) para la tabla hash.

Pero cambie esta consulta para que esté en la columna de ID lugar de Name y verá una historia muy diferente. En ese caso, realiza bucles anidados para ambas consultas, pero la versión INNER JOIN puede reemplazar uno de los escaneos de índices agrupados con una búsqueda, lo que significa que literalmente será un orden de magnitud más rápido con un gran número de filas.

Así que la conclusión es más o menos lo que mencioné en varios párrafos anteriores; esto es casi seguro que es un problema de indexación o cobertura de índice, posiblemente combinado con una o más tablas muy pequeñas. Esas son las únicas circunstancias en las que SQL Server a veces puede elegir un plan de ejecución peor para una INNER JOIN que una LEFT JOIN .