Luego de tanto batallar ya hallamos la respuesta de esta traba que agunos lectores de nuestra web presentan. Si tienes algún dato que compartir no dejes de aportar tu comentario.
Solución:
A LEFT JOIN es absolutamente no más rápido que un INNER JOIN. De hecho, es más lento; por definición, una combinación externa (LEFT JOIN o RIGHT JOIN) tiene que hacer todo el trabajo de un INNER JOIN más el trabajo extra de null-ampliar los resultados. También se esperaría que devolviera más filas, lo que aumentaría aún más el tiempo de ejecución total simplemente debido al tamaño más grande del conjunto de resultados.
(E incluso si un LEFT JOINfueron más rápido en específico situaciones debido a una confluencia de factores difícil de imaginar, no es funcionalmente equivalente a una INNER JOIN, por lo que no puede simplemente reemplazar todas las instancias de una con la otra.)
Lo más probable es que sus problemas de desempeño se encuentren en otra parte, como no tener un candidato key o extranjero key indexado correctamente. 9 mesas es mucho para unirse, por lo que la desaceleración podría estar literalmente en casi cualquier lugar. Si publica su esquema, es posible que podamos brindarle más detalles.
Editar:
Reflexionando más sobre esto, podría pensar en una circunstancia bajo la cual un LEFT JOIN podría ser más rápido que un INNER JOIN, y es entonces cuando:
- Algunas de las tablas son muy pequeño (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 el INNER JOIN de hecho, la consulta cuesta más que la LEFT JOIN, porque satisface los dos criterios anteriores. Es porque SQL Server quiere hacer una coincidencia de hash para el INNER JOIN, pero hace bucles anidados para el LEFT JOIN; el primero es normalmente mucho más rápido, pero como el número de filas es tan pequeño y no hay índice para usar, la operación hash resulta ser la parte más cara 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, frente a una tabla hash con 5 elementos. Debido al tamaño, la versión de la tabla hash es más lenta. Pero aumente a 50 elementos, o 5000 elementos, y la versión de la lista se ralentiza, porque es O (N) frente a O (1) para la tabla hash.
Pero cambie esta consulta para que esté en el ID columna en lugar de Name y verás una historia muy diferente. En ese caso, realiza bucles anidados para ambas consultas, pero el INNER JOIN La versión puede reemplazar uno de los escaneos de índice agrupados con una búsqueda, lo que significa que esto literalmente será un orden de magnitud más rápido con una gran cantidad de filas.
Entonces la conclusión es más o menos lo que mencioné en varios párrafos arriba; Es casi seguro que se trata de un problema de indexación o de cobertura de índices, posiblemente combinado con una o más tablas muy pequeñas. Esas son las únicas circunstancias en las que SQL Server podría a veces eligen un plan de ejecución peor para un INNER JOIN que un LEFT JOIN.
Existe un escenario importante que puede llevar a que una combinación externa sea más rápida que una combinación interna que aún no se ha discutido.
Cuando se usa una combinación externa, el optimizador siempre tiene la libertad de eliminar la tabla unida externa del plan de ejecución si las columnas de combinación son el PK de la tabla externa, y ninguna de las columnas de la tabla externa se hace referencia fuera de la combinación externa en sí. Por ejemplo SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY y B.KEY es el PK para 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 true 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 que existan.
Si A.KEY es un foráneo anulable key haciendo referencia a B.KEY, el optimizador no puede eliminar B del plan porque debe confirmar que existe una fila B para cada fila A.
Si A.KEY es un extranjero obligatorio key haciendo referencia a B.KEY, 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Í quita B del plan. Es fácil ver cómo la combinación externa superará en rendimiento a la combinació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 crea una vista de “hacer todo” que une todo lo que un usuario pueda necesitar relacionado con una tabla central. (Especialmente si hay usuarios ingenuos que realizan consultas ad-hoc que no comprenden 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 están unidas con combinaciones externas, el optimizador puede (y lo hace) eliminar las tablas innecesarias del plan.
Es fundamental asegurarse de que la vista que utiliza combinaciones externas proporcione los resultados correctos. Como ha dicho Aaronaught, no 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 un INNER JOIN con un OUTER JOIN si también agrega la condición
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 la caché del plan de consulta solo para asegurarme de que no esté arruinando las cosas. Sin embargo, he tenido problemas incluso cuando se hizo.
He experimentado algunos casos en los que una combinación izquierda ha sido más rápida que una combinación interna.
La razón subyacente es la siguiente: si tiene dos tablas y se 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 coincide con el índice de la tabla dos como si hiciera lo contrario: recorra las entradas en el índice de la tabla dos y coincida con el índice en la tabla uno. El problema es que cuando tiene estadísticas engañosas, el optimizador de consultas usará 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 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 hacer coincidir 10 veces en la tabla dos. Lo contrario sería un escaneo de índice que recorre más de 100000 filas e intenta hacer coincidir 100000 veces y solo 10 tienen éxito. Entonces, si las estadísticas no son correctas, el optimizador puede elegir la tabla y el índice incorrectos para recorrer.
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 unión izquierda de manera subóptima como una semi unión izquierda. Para que elija el que desea, puede usar la sugerencia de orden forzado.