sql - plan - ¿Puedo obtener un mejor rendimiento usando un JOIN o usando EXISTS?

optimizar inner join sql server

Question

Tengo dos tablas Instituciones y resultados y quiero ver si hay resultados para las instituciones de esa manera que puedo excluir los que no tienen resultados.

¿Puedo obtener un mejor rendimiento usando un JOIN o usando EXISTS?

Gracias,
-Nimesh

Answer 1

¿Estás utilizando EXISTS como parte de una subconsulta correlacionada? Si es así, la unión casi siempre será más rápida.

Su base de datos debe tener formas de hacer comparaciones de consultas. Úselos para ver qué consulta se ejecuta más rápido.

Answer 2

Depende de tu optimizador. Probé los siguientes dos en Oracle 10g y 11g. En 10g, el segundo fue ligeramente más rápido. En 11g, eran idénticos.

Sin embargo, el # 1 es realmente un mal uso de la cláusula EXISTS. Use combinaciones para encontrar coincidencias.

select *
from
  table_one t1
where exists (
             select *
             from table_two t2
             where t2.id_field = t1.id_field
             )
order by t1.id_field desc


select t1.*
from 
  table_one t1
 ,table_two t2
where t1.id_field = t2.id_field
order by t1.id_field desc 

Answer 3

Depende.

En definitiva, los 2 tienen propósitos completamente diferentes.

Se UNE a 2 tablas para acceder a los registros relacionados. Si no necesita acceder a los datos en los registros relacionados, entonces no tiene necesidad de unirse a ellos.

EXISTS se puede usar para determinar si existe un token en un conjunto de datos determinado, pero no le permitirá acceder a los registros relacionados.

Publique un ejemplo de los 2 métodos que tiene en mente y tal vez pueda darle una mejor idea.

Con sus dos tablas Instituciones y resultados, si desea una lista de instituciones que tienen resultados, esta consulta será más eficiente:

select Institutions.institution_name 
from Institutions
inner join Results on (Institutions.institution_id = Results.institution_id)

Si tiene un institution_id y solo quiere saber si tiene resultados, usar EXISTS podría ser más rápido:

if exists(select 1 from Results where institution_id = 2)
  print "institution_id 2 has results"
else
  print "institution_id 2 does not have results"

Answer 4

Dependiendo del enunciado, las estadísticas y el servidor de base de datos, puede que no importe: se puede generar el mismo plan de consulta optimizado.

Básicamente hay 3 formas en que los DB unen las tablas bajo el capó:

• Bucle anidado: para una tabla mucho más grande que la segunda. Cada fila en la tabla más pequeña se verifica para cada fila en la más grande.

• Fusionar: para dos tablas en el mismo orden de clasificación. Ambos se ejecutan en orden y se emparejan donde correspondan.

• Hash - todo lo demás. Las tablas temporales se utilizan para construir las coincidencias.

Al usar existe, puede forzar efectivamente el plan de consulta para hacer un ciclo anidado. Esta puede ser la forma más rápida, pero realmente quiere que el planificador de consultas decida.

Yo diría que necesita escribir ambas declaraciones SQL y comparar los planes de consulta. Puede encontrar que cambian bastante según los datos que tenga.

Por ejemplo, si [Instituciones] y [Resultados] son ​​tamaños similares y ambos están agrupados en InstitutionID, una combinación de fusión sería más rápida. Si [Resultados] es mucho más grande que [Instituciones], un ciclo anidado puede ser más rápido.

Answer 5

Ellos hacen cosas totalmente diferentes. ¿Qué estás intentando lograr? Eso determinará mejor la estructura de consulta adecuada.

Answer 6

En casos como el anterior, la declaración Exists funciona más rápido que la de Joins. Exists le dará un solo registro y le ahorrará tiempo también. En caso de que se una, la cantidad de registros será mayor y todos los registros se deben usar.

Answer 7

En realidad, a partir de su vaga descripción del problema, me parece que una consulta NOT IN es la forma más obvia de codificarla:

SELECT *
  FROM Institutions
  WHERE InstitutionID NOT IN (
     SELECT DISTINCT InstitutionID
       FROM Results
     )

Answer 8

Si hay una diferencia en el rendimiento o no, debe usar lo que sea más apropiado para su propósito. Su objetivo es obtener una lista de instituciones (no resultados, no necesita esos datos adicionales). Así que seleccione Instituciones que no tengan Resultados ... traducción - use EXISTS.

Answer 9

Si la tabla RESULTADOS tiene más de una fila por INSTITUTION , EXISTS() tiene el beneficio adicional de no requerirle que seleccione instituciones distintas.

En cuanto al rendimiento, he visto que las joins, IN(), and EXISTS() son más rápidas en una variedad de usos. Para encontrar el mejor método para sus propósitos, debe probarlo.

Answer 10

Si quiere las instituciones que no tuvieron resultados, entonces la subconsulta "Donde no existe" será más rápida, ya que se detendrá tan pronto como encuentre un solo resultado para aquellos que tienen resultados ...

Si quiere las instituciones con resultados, pero en realidad no quiere los resultados, lo mismo. Use una subconsulta ''Donde Existe''. Se detendrá tan pronto como encuentre un solo resultado ... Esto también asegura que el conjunto de resultados solo tendrá un registro por institución, mientras que si tuviera una institución con resultados múltiples, utilice el El enfoque de combinación requeriría que agregue la palabra clave ''distinta'' o una cláusula ''Agrupar por'' para eliminar las filas de productos de la cesta duplicados que se producirían a partir de los registros de resultados múltiples que coinciden con una institución única.

Si necesita los resultados, realice una UNIÓN: una unión interna si no desea ver las instituciones sin resultados, y una combinación externa si desea ver TODAS las instituciones, incluidas las que no tienen resultados.

Answer 11

Si se refiere a utilizar una combinación externa izquierda (o derecha) o una subconsulta no existente, estoy bastante seguro de que la combinación externa izquierda gana en rendimiento. Por ejemplo:

SELECT t1.* FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

Lo anterior debe ser más rápido que la subconsulta equivalente, y si se refiere específicamente a existe, bueno, donde la estructura lo permita, una unión interna siempre será la opción preferida.

Answer 12

Una UNIÓN EXTERIOR IZQUIERDA tenderá a tener un mejor rendimiento que una NO EXISTE **, pero en tu caso quieres hacer EXISTE y usar una simple UNIÓN INTERNA no reproduce exactamente el comportamiento de EXISTS. Si tiene múltiples Resultados para una Institución, al hacer INNER JOIN devolverá varias filas para esa institución. Podrías evitar eso usando DISTINCT, pero entonces EXISTS probablemente sea mejor para el rendimiento de todos modos.

** Para aquellos que no están familiarizados con este método:

SELECT
     MyTable.MyTableID
FROM
     dbo.MyTable T1
LEFT OUTER JOIN dbo.MyOtherTable T2 ON
     T2.MyTableID = T1.MyTableID
WHERE
     T2.MyOtherTableID IS NULL

es equivalente a

SELECT
     MyTable.MyTableID
FROM
     dbo.MyTable T1
WHERE NOT EXISTS (SELECT * FROM MyOtherTable T2 WHERE T2.MyTableID = T1.MyTableID)

suponiendo que MyOtherTableID es una columna NOT NULL. Sin embargo, el primer método generalmente funciona más rápido que el método NOT EXISTS.

Answer 13

Yo diría que un JOIN es más lento, porque la ejecución de su consulta se detiene tan pronto como una llamada EXISTS encuentra algo, mientras que JOIN continuará hasta el final.

EDITAR: pero depende de la consulta. Esto es algo que debe juzgarse caso por caso.