sql - IN vs. JOIN con conjuntos de filas grandes

sql-server-2005 performance

Question

Quiero seleccionar filas en una tabla donde la clave principal está en otra tabla. No estoy seguro de si debería usar un operador JOIN o IN en SQL Server 2005. ¿Hay alguna diferencia de rendimiento significativa entre estas dos consultas SQL con un conjunto de datos grande (es decir, millones de filas)?

SELECT *
FROM a
WHERE a.c IN (SELECT d FROM b)

SELECT a.*
FROM a JOIN b ON a.c = b.d

Answer 1

Además de ir y probarlo en una gran parte de los datos de prueba para usted, yo diría que use las UNIONES. Siempre he tenido un mejor rendimiento utilizándolos en la mayoría de los casos en comparación con una subconsulta IN, y tienes muchas más opciones de personalización en cuanto a cómo unirte, qué se selecciona, qué no, etc.

Answer 2

De la documentación de MSDN sobre Fundamentos de Subquery:

Muchas declaraciones de Transact-SQL que incluyen subconsultas pueden formularse alternativamente como combinaciones. Se pueden formular otras preguntas solo con subconsultas. En Transact-SQL, generalmente no hay diferencia de rendimiento entre una instrucción que incluye una subconsulta y una versión semánticamente equivalente que no lo hace. Sin embargo, en algunos casos donde debe verificarse la existencia, una unión produce un mejor rendimiento. De lo contrario, la consulta anidada se debe procesar para cada resultado de la consulta externa para garantizar la eliminación de duplicados. En tales casos, un enfoque de unión arrojaría mejores resultados.

En el ejemplo que proporcionó, la consulta anidada solo se debe procesar una vez para cada uno de los resultados de la consulta externa, por lo que no debe haber diferencia de rendimiento. Verificar los planes de ejecución para ambas consultas debería confirmar esto.

Nota: Aunque la pregunta en sí no especificaba SQL Server 2005, respondí con esa suposición basada en las etiquetas de pregunta. Es posible que otros motores de base de datos (incluso versiones diferentes de SQL Server) no se optimicen de la misma manera.

Answer 3

El IN se evalúa (y la selección de b vuelve a ejecutar) para cada fila en a, mientras que JOIN se optimiza para usar índices y otros trucos de paginación prolijos ...

En la mayoría de los casos, sin embargo, el optimizador probablemente podría construir una JOIN a partir de una subconsulta correlacionada y terminar con el mismo plan de ejecución de todos modos.

Editar: Por favor, lea los comentarios a continuación para mayor ... discusión sobre la validez de esta respuesta, y la respuesta real a la pregunta del OP. =)

Answer 4

En general, se considera que una unión sería más eficiente que la subconsulta IN; sin embargo, el optimizador SQL * Server normalmente no genera una diferencia de rendimiento notable. Aun así, probablemente sea mejor codificar usando la condición de unión para mantener sus estándares consistentes. Además, si alguna vez necesita migrar sus datos y código, el motor de la base de datos puede no ser tan indulgente (por ejemplo, usar una combinación en lugar de una sub consulta IN hace una gran diferencia en MySql).

Answer 5

Hablando por experiencia en una mesa con 49,000,000 filas, recomendaría LEFT OUTER JOIN. El uso de IN o EXISTS tomó 5 minutos para completarse donde termina la IZQUIERDA OUTER JOIN en 1 segundo.

SELECT a.*
FROM a LEFT OUTER JOIN b ON a.c = b.d
WHERE b.d is not null -- Given b.d is a primary Key with index

En realidad en mi consulta hago esto en 9 tablas.

Answer 6

La teoría solo te llevará tan lejos en preguntas como esta. Al final del día, querrás probar ambas consultas y ver cuál realmente se ejecuta más rápido. He tenido casos en los que la versión JOIN tomó más de un minuto y la versión IN tardó menos de un segundo. También he tenido casos donde JOIN era realmente más rápido.

Personalmente, tiendo a comenzar con la versión IN si sé que no necesitaré ningún campo de la tabla de subconsulta. Si eso comienza a funcionar lento, optimizaré. Afortunadamente, para grandes conjuntos de datos, la reescritura de la consulta marca una diferencia tan notable que simplemente puede sincronizarla desde el Analizador de consultas y saber que está progresando.

¡Buena suerte!

Answer 7

Ninguno. Use un ANSI-92 JOIN:

SELECT a.*
FROM a JOIN b a.c = b.d

Sin embargo, es mejor que EXISTE

SELECT a.*
FROM a
WHERE EXISTS (SELECT * FROM b WHERE a.c = b.d)

Esto elimina los duplicados que podría generar el JOIN, pero se ejecuta igual de rápido si no más rápido

Answer 8

Observe el plan de ejecución para ambos tipos y saque sus conclusiones. A menos que el número de registros devueltos por la subconsulta en la instrucción "IN" sea muy pequeño, la variante IN es casi seguramente más lenta.

Answer 9

Siempre he sido partidario de la metodología IN. Este enlace contiene detalles de una prueba realizada en PostgresSQL. http://archives.postgresql.org/pgsql-performance/2005-02/msg00327.php

Answer 10

Son diferentes consultas con diferentes resultados. Con la consulta IN obtendrá 1 fila de la tabla ''a'' siempre que el predicado coincida. Con la consulta INNER JOIN, obtendrá una * b filas cuando la condición de unión coincida. Entonces, con valores en a de {1,2,3} yb de {1,2,2,3} obtendrás 1,2,2,3 de JOIN y 1,2,3 de IN.

EDITAR - Creo que puede encontrar algunas respuestas aquí que le darán una idea errónea. Pruébalo tú mismo y verás que estos son todos buenos planes de consulta:

create table t1 (t1id int primary key clustered)
create table t2 (t2id int identity primary key clustered
    ,t1id int references t1(t1id)
)


insert t1 values (1)
insert t1 values (2)
insert t1 values (3)
insert t1 values (4)
insert t1 values (5)

insert t2 values (1)
insert t2 values (2)
insert t2 values (2)
insert t2 values (3)
insert t2 values (4)


select * from t1 where t1id in (select t1id from t2)
select * from t1 where exists (select 1 from t2 where t2.t1id = t1.t1id)
select t1.* from t1 join t2 on t1.t1id = t2.t1id

Los primeros dos planes son idénticos. El último plan es un bucle anidado, se espera esta diferencia porque, como mencioné anteriormente, la unión tiene una semántica diferente.

Answer 11

Usaría un join, apostando a que será muchísimo más rápido que IN. Esto supone que hay claves primarias definidas, por supuesto, permitiendo así que la indexación acelere enormemente las cosas.

Answer 12

Actualizar:

Este artículo en mi blog resume mi respuesta y mis comentarios a otras respuestas, y muestra los planes de ejecución reales:

• IN vs. JOIN vs. EXISTS

SELECT  *
FROM    a
WHERE   a.c IN (SELECT d FROM b)

SELECT  a.*
FROM    a
JOIN    b
ON      a.c = b.d

Estas consultas no son equivalentes. Pueden producir resultados diferentes si su tabla b no se conserva en clave (es decir, los valores de bd no son únicos).

El equivalente de la primera consulta es el siguiente:

SELECT  a.*
FROM    a
JOIN    (
        SELECT  DISTINCT d
        FROM    b
        ) bo
ON      a.c = bo.d

Si bd es UNIQUE y está marcada como tal (con UNIQUE INDEX o UNIQUE CONSTRAINT ), estas consultas son idénticas y probablemente utilizarán planes idénticos, ya que SQL Server es lo suficientemente inteligente como para tener esto en cuenta.

SQL Server puede emplear uno de los siguientes métodos para ejecutar esta consulta:

• Si hay un índice en ac , d es UNIQUE b es relativamente pequeño en comparación con a , entonces la condición se propaga a la subconsulta y se utiliza el INNER JOIN normal (con b leading)

• Si hay un índice en bd d no es UNIQUE , entonces la condición también se propaga y se usa LEFT SEMI JOIN . También se puede usar para la condición anterior.

• Si hay un índice tanto en bd como ac y son grandes, se usa MERGE SEMI JOIN

• Si no hay índice en ninguna tabla, entonces se construye una tabla hash en b y se usa HASH SEMI JOIN .

Ninguno de estos métodos reevalúa toda la subconsulta cada vez.

Vea esta entrada en mi blog para más detalles sobre cómo funciona esto:

• Recuento de filas faltantes: SQL Server

Hay enlaces para todos los RDBMS de los cuatro grandes.