Эффективность SQL: ГДЕ В подзапросе или JOIN, затем GROUP

В качестве примера, я хочу получить список всех элементов с определенными тегами, применяемыми к ним. Я мог бы выполнить одно из следующих действий:

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

или

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

Или что-то совсем другое.

В целом (при условии, что существует общее правило), какой более эффективный подход?

Ответ 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)

или

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 без группировки или агрегации на нем.

Если мы удалим GROUP BY из запроса:

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

это все еще разные запросы, если только ItemTag.ItemId не является UNIQUE и помечен как таковой.

SQL Server способен обнаруживать условие IN в столбце UNIQUE и просто преобразует условие IN в JOIN.

Если ItemTag.ItemId не UNIQUE, первый запрос будет использовать своего рода алгоритм SEMI JOIN, которые достаточно эффективны в SQL Server.

Вы можете преобразовать второй запрос в 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

но эта мелочь менее эффективна, чем IN или EXISTS.

См. эту статью в своем блоге для более детального сравнения производительности:

IN vs. JOIN vs. EXISTS

Ответ 2

Я думаю, что это будет зависеть от того, как оптимизатор справится с ними, возможно, даже в случае, если вы закончите с той же производительностью. Отобразить план выполнения - ваш друг здесь.

Ответ 3

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

Это недопустимый T-SQL. Item.Name должно появляться в предложении group by или внутри агрегатной функции, такой как SUM или MAX.

Ответ 4

Это практически невозможно (если вы не из тех сумасшедших администраторов-гуру), чтобы сказать, что будет быстро и что не будет, не глядя на план выполнения и/или выполняя стресс-тесты.

Ответ 5

запустите это:

SET SHOWPLAN_ALL ON

затем запустите каждую версию запроса

вы можете увидеть, вернут ли они тот же план, и если не посмотреть TotalSubtreeCost в первой строке каждого и посмотреть, насколько они отличаются.

Ответ 6

Производительность всегда, кажется, получает голос, но вы также слышите "дешевле покупать оборудование, чем программисты"

Вторая победа в производительности.

Иногда приятно смотреть на SQL и знать цель, но для чего нужны комментарии. Первый запрос использует другую таблицу для фильтра - довольно прямолинейно.

Второй из них будет иметь больше смысла (с точки зрения понимания, а не производительности), используя различные, а не групповые. Я бы ожидал, что некоторые агрегаты будут в списке, но их нет. Скорость убивает.

Ответ 7

Второй вариант более эффективен в MySQL. MySQL будет повторно выполнять запрос в инструкции IN для каждого теста условия WHERE.