sql server - subconsultas - Eficiencia de SQL: DONDE EN subconsulta vs. ÚNETE luego GRUPO

subconsultas sql server

Question

Como ejemplo, quiero obtener la lista de todos los elementos con ciertas etiquetas aplicadas a ellos. Podría hacer cualquiera de los siguientes:

SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
    SELECT ItemTag.ItemID
    FROM ItemTag
    WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)

O

SELECT Item.ID, Item.Name
FROM Item
LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
GROUP BY Item.ID, Item.Name

O algo completamente diferente.

En general (asumiendo que hay una regla general), ¿cuál es un enfoque más eficiente?

Answer 1

SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
    SELECT ItemTag.ItemID
    FROM ItemTag
    WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)

o

SELECT Item.ID, Item.Name
FROM Item
LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID
WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
GROUP BY Item.ID

Su segunda consulta no se compilará, ya que hace referencia a Item.Name sin agrupar o agregar en él.

Si eliminamos GROUP BY de la consulta:

SELECT  Item.ID, Item.Name
FROM    Item
JOIN    ItemTag
ON      ItemTag.ItemID = Item.ID
WHERE   ItemTag.TagID = 57 OR ItemTag.TagID = 55

estas son todavía consultas diferentes, a menos que ItemTag.ItemId sea ​​una clave UNIQUE y esté marcada como tal.

SQL Server puede detectar una condición de IN en una columna UNIQUE , y simplemente transformará la condición de IN en una UNIQUE .

Si ItemTag.ItemID no es UNIQUE , la primera consulta usará una especie de algoritmo SEMI JOIN , que es bastante eficiente en SQL Server .

Puede transformar la segunda consulta en un JOIN :

SELECT  Item.ID, Item.Name
FROM    Item
JOIN    (
        SELECT DISTINCT ItemID
        FROMT  ItemTag
        WHERE  ItemTag.TagID = 57 OR ItemTag.TagID = 55
        ) tags
ON      tags.ItemID = Item.ID

pero este es un poco menos eficiente que IN o EXISTS .

Vea este artículo en mi blog para una comparación de rendimiento más detallada:

• IN vs. JOIN vs. EXISTS

Answer 2

Creo que dependería de cómo los maneje el optimizador, incluso puede ocurrir que usted obtenga el mismo rendimiento. Mostrar plan de ejecución es tu amigo aquí.

Answer 3

El rendimiento siempre parece obtener el voto, pero también se escucha que "es más barato comprar hardware que programadores"

El segundo gana en el rendimiento.

A veces es bueno mirar a SQL y conocer el propósito, pero para eso son los comentarios. La primera consulta es usar la otra tabla para un filtro, bastante sencillo.

El segundo tendría más sentido (a partir de un propósito comprensivo y no de desempeño) usando distintas en lugar de agrupar por. Yo esperaría que algunos agregados estuvieran en la selección, pero no hay ninguno. La velocidad mata.

Answer 4

El segundo es más eficiente en MySQL. MySQL volverá a ejecutar la consulta dentro de la instrucción IN para cada prueba de condición WHERE.

Answer 5

Es prácticamente imposible (a menos que seas uno de esos DBAs de gurúes locos) decir qué será rápido y qué no, sin mirar el plan de ejecución y / o realizar algunas pruebas de estrés.

Answer 6

ejecuta esto:

SET SHOWPLAN_ALL ON

A continuación, ejecute cada versión de la consulta

puede ver si devuelven el mismo plan y, si no, ver el TotalSubtreeCost en la primera fila de cada uno y ver qué tan diferentes son.

Answer 7

SELECT Item.ID, Item.Name
...
GROUP BY Item.ID

Esto no es válido T-SQL. Item.Name debe aparecer en el grupo por cláusula o dentro de una función agregada, como SUM o MAX.