Czy posiadanie „LUB” w stanie INNER JOIN to zły pomysł?

Próbując poprawić szybkość niezwykle powolnego zapytania (kilka minut na dwóch tabelach zawierających tylko ~ 50 000 wierszy w każdej, w SQL Server 2008, jeśli ma to znaczenie), zawęziłem problem do połączenia ORwewnętrznego, na przykład:

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

Zmieniłem to na (mam nadzieję) równoważną parę lewych złączeń, pokazaną tutaj:

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

.. a zapytanie działa teraz za około sekundę!

Czy wprowadzenie ORwarunku złączenia jest ogólnie złym pomysłem ? A może po prostu mam pecha w układzie moich stołów?

— ładenedge
źródło

Pokaż nam plan wykonania zamiast zapytania.

— Blindy

wydaje się dziwny związek

— nathan gonzalez

@Blindy: dobry pomysł. Okazuje się, że plany wykonania pokazują tylko to, o czym Quassnoi wspomina poniżej: pierwsze zapytanie skutkuje zagnieżdżonymi pętlami, podczas gdy drugie jest wykonywane za pomocą haszowania.

— ladenedge

Odpowiedzi:

115

Tego rodzaju JOINnie można zoptymalizować do a HASH JOINlub a MERGE JOIN.

Można to wyrazić jako konkatenację dwóch zestawów wyników:

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

jednak każdy z nich jest equijoinem, jednak SQL Serveroptymalizator nie jest wystarczająco inteligentny, aby zobaczyć go w zapytaniu, które napisałeś (chociaż są one logicznie równoważne).

— Quassnoi
źródło

to ma sens, dziękuję. Nadal nie jestem pewien, czy jest coś dziwnego w moim zapytaniu, czy też powinienem ON w=x OR y=zcałkowicie unikać łączenia wzorca?

— ladenedge

@ladenedge: te łączenia zostaną wykonane przy użyciu skanowania tabeli w zagnieżdżonej pętli. Jest to powolne, jeśli twoje stoły są duże.

— Quassnoi

żeby było jasne, kiedy mówisz „te złączenia”, masz na myśli wszystkie złączenia w formie ON w=x OR y=z? (Dzięki za cierpliwość!)

— ladenedge

@ladenedge: mogą istnieć dodatkowe warunki, które pomogą SQL Serverzrozumieć, że konieczne będzie połączenie. Powiedzmy, że zapytanie SELECT * FROM othertable WHERE parentId = 1 OR id = 2użyje konkatenacji, jeśli oba pola są indeksowane, więc teoretycznie nie ma nic, co uniemożliwiłoby zrobienie tego samego w pętli. To SQL Server, czy ten plan faktycznie zbuduje, czy nie, zależy od bardzo wielu czynników, ale nigdy nie widziałem go zbudowanego w prawdziwym życiu.

— Quassnoi

Zauważ również, że jeśli wiesz, że są to zbiory rozłączne, suma ALL może znacznie poprawić wydajność, unikając scalania zestawów wynikowych.

— Darren Clark

Używam następującego kodu, aby uzyskać inny wynik niż warunek, który zadziałał dla mnie.

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)

— MEO
źródło

-2

Zamiast tego możesz użyć 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

— Mitul Panchal
źródło

UNION ALLda ci powiela w porównaniu do JOINz ORwarunku.

— CodeMonkey,

Bo ta UNIA będzie miała rację. Aby uzyskać więcej informacji, przeczytaj poniższy link union-zamiast-or

— Mitul Panchal

tak, ale w twoim przykładzie napisałeś to, union allco nie jest poprawne, ponieważ artykuł, do którego prowadzi link, również opisuje.

— CodeMonkey,