Является ли использование «ИЛИ» в условии INNER JOIN - плохой идеей?

Пытаясь повысить скорость чрезвычайно медленного запроса (несколько минут для двух таблиц, содержащих всего ~ 50 000 строк в каждой, на SQL Server 2008, если это имеет значение), я сузил проблему до OR в моем внутреннее соединение, например:

SELECT mt.ID, mt.ParentID, ot.MasterID
  FROM dbo.MainTable AS mt
  INNER JOIN dbo.OtherTable AS ot ON ot.ParentID = mt.ID
                                  OR ot.ID = mt.ParentID

Я изменил это на (что я надеюсь) эквивалентную пару левых соединений, показанную здесь:

SELECT mt.ID, mt.ParentID,
   CASE WHEN ot1.MasterID IS NOT NULL THEN
      ot1.MasterID ELSE
      ot2.MasterID END AS MasterID
  FROM dbo.MainTable AS mt
  LEFT JOIN dbo.OtherTable AS ot1 ON ot1.ParentID = mt.ID
  LEFT JOIN dbo.OtherTable AS ot2 ON ot2.ID = mt.ParentID
  WHERE ot1.MasterID IS NOT NULL OR ot2.MasterID IS NOT NULL

.. и теперь запрос выполняется примерно за секунду!

Является ли вообще плохой идеей помещать OR в условие соединения? Или мне просто как-то не повезло с раскладкой столов?


sql sql-server tsql sql-server-2008 inner-join
person ladenedge    schedule 05.05.2011    source источник
comment
Покажите нам план выполнения вместо вашего запроса.   -  person Blindy    schedule 05.05.2011
comment
кажутся странными отношениями   -  person nathan gonzalez    schedule 05.05.2011
comment
@Blindy: хорошая идея. Оказывается, планы выполнения показывают именно то, что Квассной упоминает ниже: первый запрос приводит к вложенным циклам, а второй выполняется с хеш-соединением.   -  person ladenedge    schedule 05.05.2011

Ответы (3)


arrow_upward
124
arrow_downward

Этот вид JOIN не может быть оптимизирован до HASH JOIN или MERGE JOIN.

Это можно выразить как объединение двух наборов результатов:

SELECT  *
FROM    maintable m
JOIN    othertable o
ON      o.parentId = m.id
UNION
SELECT  *
FROM    maintable m
JOIN    othertable o
ON      o.id = m.parentId

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

person Quassnoi    schedule 05.05.2011
comment
в этом есть смысл, спасибо. Я все еще не уверен, есть ли что-то особенное в моем запросе или мне следует полностью избегать объединений шаблона ON w=x OR y=z? - person ladenedge; 05.05.2011
comment
@ladenedge: эти соединения будут выполняться с использованием сканирования таблицы во вложенном цикле. Это медленно, если ваши таблицы большие. - person Quassnoi; 05.05.2011
comment
для ясности: когда вы говорите «эти объединения», вы имеете в виду все объединения в форме ON w=x OR y=z? (Спасибо за терпеливость!) - person ladenedge; 05.05.2011
comment
@ladenedge: могут быть дополнительные условия, которые помогут SQL Server понять, что потребуется конкатенация. Скажем, запрос SELECT * FROM othertable WHERE parentId = 1 OR id = 2 будет использовать конкатенацию, если оба поля проиндексированы, поэтому теоретически нет ничего, что могло бы помешать сделать то же самое в цикле. Будет ли SQL Server строить этот план на самом деле или нет, зависит от очень многих факторов, но я никогда не видел, чтобы он был реализован в реальной жизни. - person Quassnoi; 05.05.2011
comment
Также обратите внимание, что если вы знаете, что они являются непересекающимися наборами, объединение ALL может значительно улучшить производительность, избегая объединения наборов результатов. - person Darren Clark; 30.10.2020

arrow_upward
6
arrow_downward

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

Select A.column, B.column
FROM TABLE1 A
INNER JOIN
TABLE2 B
ON A.Id = (case when (your condition) then b.Id else (something) END)
person MEO    schedule 21.12.2017

arrow_upward
0
arrow_downward

Вместо этого вы можете использовать UNION ALL.

SELECT mt.ID, mt.ParentID, ot.MasterID FROM dbo.MainTable AS mt Union ALL SELECT mt.ID, mt.ParentID, ot.MasterID FROM dbo.OtherTable AS ot

person Mitul Panchal    schedule 21.01.2019
comment
UNION ALL даст вам дубликаты по сравнению с JOIN с условием OR. - person CodeMonkey; 21.01.2019
comment
Ибо СОЮЗ будет прав. Для получения дополнительной информации прочтите следующую ссылку union-вместо-or-or - person Mitul Panchal; 21.01.2019
comment
да, но в вашем примере вы написали это с помощью union all, что неверно, поскольку статья, на которую вы ссылаетесь, также описывает. - person CodeMonkey; 21.01.2019