Rendimiento de INNER JOIN vs LEFT JOIN en SQL Server
Creé un comando SQL que usa INNER JOIN en 9 tablas; de todos modos, este comando lleva mucho tiempo (más de cinco minutos). Entonces mi gente me sugirió cambiar INNER JOIN a LEFT JOIN porque el rendimiento de LEFT JOIN es mejor, a pesar de lo que sé. Después de que lo cambié, la velocidad de consulta mejoró significativamente.
Me gustaría saber por qué LEFT JOIN es más rápido que INNER JOIN.
Mi comando SQL se parece a continuación:
SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN Dy así sucesivamente
Actualización: esto es breve 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
A LEFT JOINno es en absoluto más rápido que un INNER JOIN. De hecho, es más lento; por definición, una unión externa ( LEFT JOINo RIGHT JOIN) tiene que hacer todo el trabajo de una INNER JOINmás el trabajo adicional de extender nula 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 a LEFT JOIN fuera más rápido en situaciones específicas debido a una confluencia de factores difícil de imaginar, no es funcionalmente equivalente a an INNER JOIN, por lo que no puedes simplemente reemplazar todas las instancias de uno con el otro.)
Lo más probable es que sus problemas de rendimiento residan en otra parte, como no tener una clave candidata o una clave externa indexada correctamente. 9 mesas es bastante para unirse, por lo que la desaceleración podría ocurrir literalmente en casi cualquier lugar. Si publica su esquema, es posible que podamos proporcionarle más detalles.
Editar:
Reflexionando más sobre esto, se me ocurre una circunstancia en la que a LEFT JOINpodría ser más rápido que an INNER JOIN, y es cuando:
- Algunas de las tablas son muy pequeñas (digamos, menos de 10 filas);
- Las tablas no tienen índices suficientes para cubrir la consulta.
Considere 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 INNER JOINconsulta cuesta más que la consulta LEFT JOIN, porque satisface los dos criterios anteriores. Es porque SQL Server quiere hacer una coincidencia hash para INNER JOIN, pero realiza bucles anidados para LEFT JOIN; la primera normalmente es mucho más rápida, pero como el número de filas es muy pequeño y no hay ningún índice para usar, la operación hash 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 comparación con 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 ralentizará, porque es O(N) frente a O(1) para la tabla hash.
Pero cambie esta consulta para que esté en la IDcolumna en lugar de Namey verá una historia muy diferente. En ese caso, realiza bucles anidados para ambas consultas, pero la INNER JOINversión puede reemplazar uno de los escaneos de índice agrupado con una búsqueda, lo que significa que esto será literalmente un orden de magnitud más rápido con una gran cantidad de filas.
Entonces la conclusión es más o menos la que mencioné varios párrafos arriba; Es casi seguro que se trata de un problema de indexación o de cobertura de índice, posiblemente combinado con una o más tablas muy pequeñas. Esas son las únicas circunstancias bajo las cuales SQL Server a veces puede elegir un peor plan de ejecución para INNER JOINun archivo LEFT JOIN.
Existe un escenario importante que puede llevar a que una unión externa sea más rápida que una unión interna que aún no se ha analizado.
Cuando se utiliza una combinación externa, el optimizador siempre tiene la libertad de eliminar la tabla combinada externa del plan de ejecución si las columnas de combinación son la PK de la tabla externa y no se hace referencia a ninguna de las columnas de la tabla externa fuera de la combinación externa. Por ejemplo SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY, B.KEY es la PK de B. Tanto Oracle (creo que estaba usando la versión 10) como Sql Server (usé 2008 R2) eliminan 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.KEYpuede requerir o no B en el plan de ejecución dependiendo de las restricciones que existan.
Si A.KEY es una clave externa 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 externa obligatoria que hace referencia a B.KEY, entonces el optimizador puede eliminar B del plan porque las restricciones garantizan la existencia de la fila. Pero el hecho de que el optimizador pueda eliminar la tabla del plan no significa que lo hará. SQL Server 2008 R2 NO elimina B del plan. Oracle 10 SÍ elimina B del plan. Es fácil ver cómo la combinación externa superará a la combinación interna en SQL Server en este caso.
Este es un ejemplo trivial y no práctico para una consulta independiente. ¿Por qué unirse a una mesa si no es necesario?
Pero esta podría ser una consideración de diseño muy importante al diseñar vistas. Con frecuencia se crea una vista de "hacer todo" que reúne todo lo que un usuario pueda necesitar en relación con una mesa 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 tablas dentro de la vista. Si las tablas están unidas con uniones externas, entonces el optimizador puede (y lo hace) eliminar las tablas innecesarias del plan.
Es fundamental asegurarse de que la vista que utiliza uniones externas proporcione los resultados correctos. Como ha dicho Aaronaught, no se puede sustituir ciegamente OUTER JOIN por INNER JOIN y esperar los mismos resultados. Pero hay ocasiones en las que puede resultar útil por motivos de rendimiento al utilizar 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 INNER JOIN con una OUTER JOIN si también agrega la condición <FOREIGN_KEY> IS NOT NULL a la cláusula donde.
Si todo funciona como debería, no debería, PERO todos sabemos que no todo funciona como debería, especialmente cuando se trata del optimizador de consultas, el almacenamiento en caché del plan de consultas y las estadísticas.
Primero, sugeriría reconstruir el índice y las estadísticas, luego borrar el caché del plan de consulta solo para asegurarse de que no arruine las cosas. Sin embargo, he experimentado problemas incluso cuando se hace eso.
He experimentado algunos casos en los que una unión izquierda ha sido más rápida que una unión interna.
La razón subyacente es esta: si tiene dos tablas y las une en una columna con un índice (en ambas tablas). La combinación interna producirá el mismo resultado sin importar si recorre las entradas en el índice de la tabla uno y las compara con el índice de la tabla dos como si hiciera lo contrario: recorre las entradas en el índice de la tabla dos y las combina con el índice. en la tabla uno. El problema es que cuando tiene estadísticas engañosas, el optimizador de consultas utilizará las estadísticas del índice para encontrar la tabla con menos entradas coincidentes (según sus otros criterios). Si tiene dos tablas con 1 millón en cada una, en la tabla uno tendrá 10 filas coincidentes y en la tabla dos tendrá 100000 filas coincidentes. La mejor manera sería hacer un escaneo de índice en la tabla uno y hacer coincidir 10 veces en la tabla dos. Lo contrario sería un escaneo de índice que recorre 100000 filas e intenta hacer coincidir 100000 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 recorrer.
Si el optimizador elige optimizar la combinación izquierda en el orden en que está escrita, funcionará mejor que la combinación interna.
PERO, el optimizador también puede optimizar una unión izquierda de manera subóptima como una semiunión izquierda. Para que elija el que desee, puede utilizar la sugerencia de orden forzada.
Pruebe ambas consultas (la que tiene unión interna e izquierda) OPTION (FORCE ORDER)al final y publique los resultados. OPTION (FORCE ORDER)es una sugerencia de consulta que obliga al optimizador a crear el plan de ejecución con el orden de unión que proporcionó en la consulta.
Si INNER JOINempieza a funcionar tan rápido como LEFT JOIN, es porque:
- En una consulta compuesta enteramente por
INNER JOINs, el orden de unión no importa. Esto le da libertad al optimizador de consultas para ordenar las combinaciones 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 haciendo cálculos, lo que arruinó por completo el optimizador. Tuvimos casos en los que FORCE ORDERreduciría el tiempo de ejecución de una consulta de 5 minutos a 10 segundos.
