sql - functions - UNIRSE IZQUIERDA Significativamente más rápido que UNIR INNER

sql functions

Question

Tengo una tabla ( MainTable ) con un poco más de 600,000 registros. Se une a sí mismo a través de una segunda tabla ( JoinTable ) en una relación de tipo padre / hijo:

SELECT   Child.ID, Parent.ID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID
JOIN     MainTable
AS       Parent
      ON Parent.ID = JoinTable.ParentID
     AND Parent.SomeOtherData = Child.SomeOtherData

Sé que cada registro secundario tiene un registro principal y que los datos en JoinTable son precisos.

Cuando ejecuto esta consulta, toma literalmente minutos para ejecutarse. Sin embargo, si me inscribo en Parent utilizando una combinación izquierda, demorará <1 segundo en ejecutarse:

SELECT   Child.ID, Parent.ID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID
LEFT JOIN MainTable
AS       Parent
      ON Parent.ID = JoinTable.ParentID
     AND Parent.SomeOtherData = Child.SomeOtherData
WHERE    ...[some info to make sure we don''t select parent records in the child dataset]...

Entiendo la diferencia en los resultados entre un INNER JOIN y un LEFT JOIN . En este caso, está devolviendo exactamente el mismo resultado que cada hijo tiene un padre. Si dejo que se ejecuten ambas consultas, puedo comparar los conjuntos de datos y son exactamente iguales.

¿Por qué es que un LEFT JOIN ejecuta mucho más rápido que un INNER JOIN ?

ACTUALIZACIÓN Se verificaron los planes de consulta y, cuando se usa una combinación interna, comienza con el conjunto de datos principal. Cuando se realiza una combinación a la izquierda, comienza con el conjunto de datos secundario.

Los índices que utiliza son todos iguales.

¿Puedo obligarlo a que empiece siempre con el niño? Usar una combinación a la izquierda funciona, simplemente se siente mal.

Preguntas similares se han hecho aquí antes, pero ninguna parece responder mi pregunta.

por ejemplo, la respuesta seleccionada en INNER JOIN vs LEFT JOIN en SQL Server dice que las combinaciones izquierdas son siempre más lentas que las combinaciones internas. El argumento tiene sentido, pero no es lo que estoy viendo.

Answer 1

La unión izquierda parece ser más rápida porque SQL está obligado a hacer primero la selección más pequeña y luego unirse a este conjunto más pequeño de registros. Por alguna razón, el optimizador no quiere hacer esto naturalmente.

3 maneras de forzar las uniones para que sucedan en el orden correcto:

1. Seleccione el primer subconjunto de datos en una tabla temporal (o variable de tabla) y luego únase a ella
2. Use combinaciones izquierdas (y recuerde que esto podría devolver datos diferentes porque es una combinación izquierda y no una combinación interna)
3. use la palabra clave FORCE ORDER. Tenga en cuenta que si los tamaños de tabla o los esquemas cambian, el plan de consulta puede no ser correcto (consulte https://dba.stackexchange.com/questions/45388/forcing-join-order )

Answer 2

Prueba este. Mismo resultado, enfoque diferente:

SELECT c.ID, p.ID 
FROM
(SELECT   Child.ID, JoinTable.ParentID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID) AS c
INNER JOIN 
(SELECT   Parent.ID, JoinTable.ID
FROM     MainTable
AS       Parent
JOIN     JoinTable
      ON Parent.ID = JoinTable.ParentID
     AND Parent.SomeOtherData = Child.SomeOtherData) AS p
ON c.ParentID = p.ID

Si no ayuda, usa cte:

;WITH cte AS
(SELECT   Child.ID, JoinTable.ParentID
FROM     MainTable
AS       Child
JOIN     JoinTable
      ON Child.ID = JoinTable.ID)
SELECT cte.ID, Parent.ID
FROM cte INNER JOIN 
MainTable
AS       Parent
      ON Parent.ID = cte.ParentID
     AND Parent.SomeOtherData = cte.SomeOtherData