sql-server sql-server-2005 exists query-performance sql-in

SQL Server IN vs. EXISTS Performance



exists sql server (8)

Tengo curiosidad por saber cuál de las siguientes opciones sería más eficiente.
Siempre he sido un poco cauteloso sobre el uso de IN porque creo que SQL Server convierte el conjunto de resultados en una gran declaración IF . Para un gran conjunto de resultados, esto podría dar como resultado un bajo rendimiento. Para pequeños conjuntos de resultados, no estoy seguro de que sea preferible. Para grandes conjuntos de resultados, ¿ EXISTS no sería más eficiente?

WHERE EXISTS (SELECT * FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

vs.

WHERE bx.BoxID IN (SELECT BoxID FROM Base WHERE [Rank = 2])

Entonces, IN no es lo mismo que EXISTS ni producirá el mismo plan de ejecución.

Habitualmente, EXISTS se usa en una subconsulta correlacionada, lo que significa que se UNIRÁ a la consulta interna EXISTS con su consulta externa. Eso agregará más pasos para producir un resultado, ya que necesita resolver las combinaciones de consultas externas y las combinaciones de consultas internas y luego hacer coincidir sus cláusulas where para unir ambas.

Por lo general, IN se utiliza sin correlacionar la consulta interna con la consulta externa, y eso se puede resolver en un solo paso (en el mejor de los casos).

Considera esto:

  1. Si usa IN y el resultado de la consulta interna es de millones de filas de valores distintos, probablemente lo haga MÁS LENTO que EXISTE, dado que la consulta EXISTS es efectiva (tiene los índices correctos para unirse a la consulta externa).

  2. Si usa EXISTS y la combinación con su consulta externa es compleja (lleva más tiempo realizar, sin índices adecuados) ralentizará la consulta por el número de filas en la tabla externa, a veces el tiempo estimado para completar puede ser en días. Si el número de filas es aceptable para su hardware dado, o la cardinalidad de los datos es correcta (por ejemplo, menos valores DISTINCT en un conjunto de datos grande) IN puede realizar más rápido que EXISTS.

  3. Todo lo anterior se anotará cuando tenga una buena cantidad de filas en cada tabla (por justo me refiero a algo que exceda el procesamiento de su CPU y / o los umbrales de RAM para el almacenamiento en caché).

Entonces, la RESPUESTA es DEPENDE. Puede escribir una consulta compleja dentro de IN o EXISTS, pero como regla general, debe intentar usar IN con un conjunto limitado de valores distintos y EXISTS cuando tenga muchas filas con muchos valores distintos.

El truco es limitar el número de filas a escanear.

Saludos,

MarianoC

Fuera de mi cabeza y no estoy seguro de que sea correcto: creo que el segundo será más rápido en este caso.

  1. En el primero, la subconsulta correlacionada probablemente hará que se ejecute la subconsulta para cada fila.
  2. En el segundo ejemplo, la subconsulta solo debería ejecutarse una vez, ya que no está correlacionada.
  3. En el segundo ejemplo, la IN se cortocircuitará tan pronto como encuentre una coincidencia.

He hecho algunas pruebas en SQL Server 2005 y 2008, y tanto EXISTS como IN regresan con exactamente el mismo plan de ejecución real, como han dicho otros. El Optimizador es óptimo. :)

Sin embargo, algo en lo que hay que estar consciente, EXISTS, IN y JOIN, a veces puede devolver resultados diferentes si no frase su consulta correctamente: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

La respuesta aceptada es miope y la pregunta un poco floja en eso:

1) No mencione explícitamente si un índice de cobertura está presente en la izquierda, derecha o en ambos lados.

2) Ninguno toma en cuenta el tamaño de la entrada del lado izquierdo establecido y el del lado derecho configurado.
(La pregunta solo menciona un gran conjunto de resultados general).

Creo que el optimizador es lo suficientemente inteligente como para convertir entre "en" vs "existe" cuando hay una diferencia de costo significativa debido a (1) y (2), de lo contrario, puede usarse como una pista (por ejemplo, existe para alentar el uso de un índice buscable en el lado derecho).

Ambas formas se pueden convertir para unirse a formularios internamente, tienen el orden de combinación invertido y se ejecutan como bucle, hash o fusión, según los recuentos de filas estimadas (izquierda y derecha) y la existencia de índice en los lados izquierdo, derecho o ambos.

Los planes de ejecución suelen ser idénticos en estos casos, pero hasta que vea cómo el optimizador influye en todos los demás aspectos de los índices, nunca lo sabrá.

Para optimizar los EXISTS , sea muy literal; algo solo tiene que estar allí, pero en realidad no necesita ningún dato devuelto por la subconsulta correlacionada. Estás evaluando una condición booleana.

Asi que:

WHERE EXISTS (SELECT TOP 1 1 FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

Debido a que la subconsulta correlacionada es RBAR , el primer resultado acierto hace que la condición sea verdadera y no se procesa más.

EXISTS será más rápido porque una vez que el motor ha encontrado un golpe, dejará de buscar ya que la condición ha demostrado ser cierta.
Con IN recogerá todos los resultados de la subconsulta antes del procesamiento posterior.