W rozdziale tym znajdziesz opis procesu przetwarzania zapytań SQL, skoncentrowany na klauzuli FROM. Jest to kontynuacja zagadnień, poruszonych w artykule – logiczne przetwarzanie zapytań. Temat ten podzieliłem na dwie części, ze względu na zakres i konieczność omówienia wcześniej zasad łączenia tabel w SQL. Polecam uwadze te dwa artykuły, które prezentują fundamentalne reguły pisania zapytań SQL.

W tej części, znajdziesz informacje o sposobie realizacji zapytań do wielu tabel. Przedstawię wpływ kolejności JOINów na wydajność oraz typowe błędy logiczne.

---

Zapytania SQL do wielu tabel

Przypomnijmy podstawowe zasady kolejności przetwarzania zapytań SQL. Pierwszym krokiem jest określenie źródeł danych, czyli wykonywanie poleceń umieszczonych we FROM.

W przypadku kwerend odwołujących się do jednego zbioru sprawa jest bardzo prosta. Wynikiem przetwarzania kroku FROM, jest wtedy tabela wirtualna VT1. Zawiera ona wszystkie rekordy ze zbioru źródłowego. Jest tożsama z nią, posiada te same kolumny i rekordy. Zbiór VT1 jest także źródłem kolejnego kroku – przetwarzania WHERE. Jak widać w zapytaniach do jednej tabeli nie ma szczególnej filozofii.

W rzeczywistych środowiskach bazodanowych, kwerendy dotyczą zazwyczaj wielu tabel.

Dla przykładu rozważmy scenariusz zapytania SQL w bazie testowej Northwind. Interesują nas informacje o Klientach z określonego miasta – Madrytu. Chcemy znać detale ich zamówień, złożonych we wrześniu 1996 – nazwy i ilości produktów, które kupili.

Struktura tej bazy danych jest znormalizowana. Informacje które nas interesują, wymagają połączenia 4 tabel.

Przykładowa kwerenda, realizująca to zadanie, może wyglądać tak :

SELECT  c.CompanyName, o.OrderID, od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
       INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID
       INNER JOIN dbo.Products p on od.ProductID = p.ProductID
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'

Żeby było ciekawiej (trochę zaczepnie), zastosowałem jako pierwsze połączenie – LEFT OUTER JOIN. Chcę tym samym przy okazji analizy złączeń, pokazać jeden z częstszych błędów logicznych.

W wyniku okazuje się, że jest tylko jeden Klient, który w dodatku złożył tylko jedno zamówienie w podanym przedziale dat.

Zanim przystąpimy do szczegółowej analizy naszego zapytania, możemy podzielić klauzulę FROM na etapy. Bierzemy pod uwagę ilość występujących tu złączeń i związanych z nimi operacji. Mamy trzy złączenia – będą więc trzy etapy.

Zapiszmy FROM bardziej obrazowo :

FROM 
	-- ETAP 1 – łączenie dwóch pierwszych tabel
          dbo.Customers c 	
             LEFT OUTER JOIN  dbo.Orders o      ON c.CustomerID = o.CustomerID 

	-- ETAP 2 – do wyniku ETAPu 1 łączymy tabelę dbo.[Order details]
             INNER JOIN dbo.[Order Details] od  ON od.OrderID = o.OrderID     

	-- ETAP 3 – do wyniku ETAPu 2 łączymy tabelę dbo.Products
             INNER JOIN dbo.Products p          ON od.ProductID = p.ProductID  

Zasady połączeń, obowiązujące w zapytaniach do wielu tabel SQL, są identyczne jak te opisane dla przykładu łączenia dwóch tabel.

Zaczynamy zawsze od łączenia dwóch pierwszych, stojących zaraz po FROM. Możemy także stosować nawiasy (o tym w następnym przykładzie). Mają one priorytet – czyli łączona jest tabela z wynikiem operacji w nawiasach.

Przy łączeniu trzech i więcej tabel, każda kolejna jest dołączana do wyniku poprzedniego kroku. Algorytm działa iteracyjnie od lewej do prawej, aż do przetworzenia ostatniego zbioru. Prześledźmy zatem dokładnie kolejne etapy przetwarzania FROM naszej kwerendy.

---

ETAP 1

Łączenie zewnętrzne (LEFT OUTER JOIN) pierwszych dwóch tabel :

SELECT  c.CompanyName, o.OrderID 
FROM   dbo.Customers c  
		LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID 

Krok ten, zostanie przetworzony zgodnie z typowymi regułami logicznego przetwarzania złączeń :

• Pobierz wszystkie dane z tabeli dbo.Customers
• Pobierz wszystkie dane z tabeli dbo.Orders
• Wykonaj złączenie zewnętrzne
  • Najpierw iloczyn kartezjański tych dwóch tabel
  • Następnie filtracja rekordów zgodna z wyrażeniami określonymi w ON
  • Dodanie „odfiltrowanych” rekordów z tabeli stojącej po LEWEJ stronie operatora JOIN (z tabeli dbo.Customers). Wartości atrybutów tabeli dbo.Orders dla tych „niedopasowanych elementów” z kroku poprzedniego uzupełnij NULLami.

W wyniku otrzymamy wszystkich Klientów (całą zawartość tabeli dbo.Customers) wraz z informacjami o ich zleceniach. Zwróć uwagę na pierwsze dwa rekordy. Są to elementy „dorzucone” w krokach charakterystycznych dla połączeń zewnętrznych – klienci którzy nie złożyli żadnych zleceń.

CompanyName                              OrderID
---------------------------------------- -----------
FISSA Fabrica Inter. Salchichas S.A.     NULL
Paris spécialités                        NULL
Vins et alcools Chevalier                10248
Toms Spezialitäten                       10249
...
Richter Supermarkt                       11075
Bon app'                                 11076
Rattlesnake Canyon Grocery               11077

(832 row(s) affected)

---

ETAP 2

Zbiór wynikowy poprzedniego etapu (VT1.1), będzie tabelą stojącą po LEWEJ stronie kolejnego złączenia. Tym razem będzie to złączenie wewnętrzne. VT1.1 umieściłem dla lepszego zobrazowania w nawiasie.

SELECT  c.CompanyName, o.OrderID , od.Quantity 
FROM   -- w nawiasie wynik ETAPu 1
       (
           dbo.Customers c  
	 LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
       )
           INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID

Wykonywane są więc kroki :

• Pobierz wszystkie dane ze zbioru będącego wynikiem pierwszego złączenia – VT1.1
• Pobierz wszystkie dane z tabeli dbo.[Order Details].
• Wykonaj złączenie wewnętrzne tych zbiorów
  • iloczyn kartezjański
  • filtrowanie rekordów

CompanyName                              OrderID     Quantity
---------------------------------------- ----------- --------
Vins et alcools Chevalier                10248       12
Vins et alcools Chevalier                10248       10
Vins et alcools Chevalier                10248       5
Toms Spezialitäten                       10249       9
...
Rattlesnake Canyon Grocery               11077       2
Rattlesnake Canyon Grocery               11077       4
Rattlesnake Canyon Grocery               11077       2

(2155 row(s) affected)

Zwróć uwagę co się stało z Klientami, którzy nie złożyli żadnych zleceń. W poprzednim kroku, dzięki LEFT JOIN, byli oni uwzględnieni (dorzuceni do wyniku). Teraz, przy kolejnym złączeniu – elementy te zostały odfiltrowane.

W naszym scenariuszu nie miało to znaczenia na wynik końcowy. W końcu chcemy uzyskać informacje tylko o Klientach, którzy złożyli zamówienia. Tak naprawdę pierwszym złączeniem, powinien być INNER JOIN.

Zdarza się, że początkujący użytkownicy SQL, oczekują w takim przypadku, że w wyniku będą również mieli Klientów bez zleceń. Jest to dość często popełniany błąd logiczny na który chciałem zwrócić Twoją uwagę. Wynika on z niewiedzy, w jakiej kolejności wykonywane są złączenia. Być może także z nadinterpretacji zakresu działania LEFT OUTER JOIN, który przecież dotyczy tylko pierwszego złączenia.

Wróćmy do dalszej analizy naszego przykładu.

---

ETAP 3

Algorytm powtórzony zostanie jeszcze raz, dołączając finalnie do powyższego wyniku ostatnią tabelę – dbo.Products.

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   -- w nawiasie wynik ETAPu 2
       (
          dbo.Customers c  
          LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
          INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID 
       )
          INNER JOIN dbo.Products p on od.ProductID = p.ProductID

Wykonywane są znów standardowe kroki, jak przy każdym złączeniu dwóch zbiorów :

• Pobierz wszystkie dane ze zbioru będącego wynikiem etapu 2 (umieszczone w nawiasie) – VT1.2.
• Pobierz wszystkie dane z tabeli dbo.Products.
• Wykonaj złączenie wewnętrzne tych zbiorów
  • Iloczn kartezjański
  • Filtrowanie rekordów.

CompanyName                              OrderID     Quantity ProductName
---------------------------------------- ----------- -------- ------------------------------
Vins et alcools Chevalier                10248       12       Queso Cabrales
Vins et alcools Chevalier                10248       10       Singaporean Hokkien Fried Mee
Vins et alcools Chevalier                10248       5        Mozzarella di Giovanni
Toms Spezialitäten                       10249       9        Tofu
...
Rattlesnake Canyon Grocery               11077       2        Röd Kaviar
Rattlesnake Canyon Grocery               11077       4        Rhönbräu Klosterbier
Rattlesnake Canyon Grocery               11077       2        Original Frankfurter grüne Soße

(2155 row(s) affected)

W tym momencie kończy się przetwarzanie klauzuli FROM. Wszystkie złączenia zostały wykonane. Zbiór wynikowy oznaczyłem jako VT2, aby odróżnić go od prostego przypadku zapytania do jednej tabeli (VT1). Zostaje on przekazany do kolejnego kroku – filtrowania rekordów w WHERE. Proces przetwarzania postępuje dalej zgodnie z opisanym tutaj logicznym porządkiem.

---

Stosowanie nawiasów w złączeniach tabel i filtracja we FROM

Rozważmy teraz scenariusz, ponownie w bazie Northwind, w którym chcemy uzyskać wiedzę o WSZYSTKICH Klientach z Madrytu, wraz z informacją o produktach, które zamówili w grudniu 1996. Jeśli nie złożyli żadnego zamówienia w tym okresie, to również chcemy ich zobaczyć.

Zapytanie realizujące to zadanie, powinno zwrócić następujący zbiór :

Jest to szczególny przypadek, w którym filtrowanie rekordów ze względu na datę zamówienia, powinno odbywać się we FROM zamiast w WHERE. Zobaczysz tu także praktyczne zastosowanie nawiasów we FROM, dzięki którym możemy sterować logiką połączeń. Nie będę stosował tu podzapytań, tylko czyste, proste złączenia.

Aby uzyskać informacje o wszystkich Klientach z Madrytu z ich zamówieniami (na razie bez filtrowania daty), można zapisać zapytanie w ten sposób :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM    dbo.Customers c  
        LEFT OUTER JOIN 
        ( 
              dbo.Orders o INNER JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID
                           INNER JOIN dbo.Products p ON od.ProductID = p.ProductID 
        ) ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid'

Uzyskamy teraz faktyczny LEFT OUTER JOIN tabeli dbo.Customers wraz ze wszystkimi szczegółami zamówień.

Pierwsze złączenie będzie połączeniem tabeli dbo.Customers z wynikiem operacji ujętych w nawias. Wykonane zostaną najpierw wszystkie złączenia wewnątrz nawiasu. Najpierw tabeli dbo.Orders z dbo.[Order Details]. Następnie do wyniku dołączona zostanie tabela dbo.Products.

Dopiero na samym końcu, zostanie wykonane połączenie zewnętrzne, lewostronne, tabeli dbo.Customers z efektem działań w nawiasie (wszystkie szczegóły zamówień).

Alternatywnie, moglibyśmy połączenia zapisać bez nawiasów, z wykorzystaniem RIGHT OUTER JOIN :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   dbo.Orders o 	
          INNER JOIN dbo.[Order Details] od  ON od.OrderID = o.OrderID
          INNER JOIN dbo.Products p          ON od.ProductID = p.ProductID 
          RIGHT OUTER JOIN dbo.Customers c   ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid'

Wynik będzie identyczny. Najpierw zostaną wykonane kolejno wszystkie INNER JOINy. Na samym końcu dołączona zostanie tabela dbo.Cutomers, dorzucając w ostatnim kroku wszystkich Klientów bez zleceń (RIGHT OUTER JOIN).

Teraz zastanów się, gdzie powinniśmy wykonywać filtrację rekordów jeśli chodzi o daty zamówień. Co uzyskamy stosując filtrację w WHERE ?

SELECT  c.CompanyName, o.OrderID, od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN  
       ( dbo.Orders o INNER JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID
                      INNER JOIN dbo.Products p ON od.ProductID = p.ProductID 
        ) ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' 
    and ( o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30' OR o.OrderDate is null)

Filtracja WHERE jest bezapelacyjna. Raz usunięte rekordy, nie mają prawa się więcej pojawić w zbiorze wynikowym. Nie ma tu mechanizmu „dorzucania” elementów takiego jak w przypadku połączeń zewnętrznych. Dlatego powyższy zapis odsiał nam niestety wszystkich Klientów z Madrytu, którzy złożyli zamówienia, ale w innych datach.

Przypomnę, że chcieliśmy ich także zobaczyć w wyniku końcowym. Straciliśmy więc tutaj wiedzę o firmie „Bólido Comidas preparadas”.

Rozwiązaniem tego problemu jest przeniesienie filtracji ze względu na datę do klauzuli FROM, tak aby dorzucić ewentualnych Klientów z zamówieniami złożonymi poza interesującym nas zakresem dat. Właściwie zapisana kwerenda będzie wyglądała tak :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN 
       ( dbo.Orders o  INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID
                       INNER JOIN dbo.Products p on od.ProductID = p.ProductID 
       ) ON c.CustomerID = o.CustomerID 
            and o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30' 
WHERE c.City = 'Madrid'

Dzięki temu zabiegowi, uzyskamy wiedzę o WSZYSTKICH Klientach z Madrytu. Dodatkowo o zamówieniach z określonego przedziału czasowego.

---

Logiczna a fizyczna kolejność łączenia zbiorów

Fizyczna realizacja połączeń zbiorów przez silnik bazodanowy (plan wykonania) może się różnić od przyjętych w teorii zasad (od lewej do prawej). Jednak wynik zawsze musi być identyczny z rachunkiem teoretycznym.

Zauważ, że czasem warto zajrzeć ciut dalej, np. do filtrowania WHERE aby już na etapie FROM, nie analizować wszystkich rekordów, które i tak zostaną później odfiltrowane. Ponadto fizyczna kolejność wykonywania połączeń, filtracji – też ma znaczenie na wydajność.

Wybór najkrótszej i najbardziej efektywnej metody dostępu do danych, to przede wszystkim zadanie dla optymalizatora zapytań. Najważniejszą zasadą jego działań jest zawsze przewidywalny efekt końcowy. Musi on być w 100% zgodny z opisanymi zasadami teoretycznymi.

Optymalizator potrafi ograniczać i efektywnie łączyć zbiory. Obecne w analizie teoretycznej iloczyny kartezjańskie czy też pobieranie wszystkich wierszy tabeli zazwyczaj nie mają miejsca.
Zapiszmy więc w różny sposób kwerendę realizującą zadanie z przykładu pierwszego :

-- Pierwszą tabelą do której odwołujemy się jest dbo.Customers
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 

-- Pierwszą tabelą do której odwołujemy się jest dbo.Orders
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Orders o 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
	inner join dbo.Products p on od.ProductID = p.ProductID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 

-- Pierwszą tabelą do której odwołujemy się jest dbo.Products 
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Products p 
	inner join dbo.[Order Details] od on od.ProductID = p.ProductID
	inner join dbo.Orders o on od.OrderID = o.OrderID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 

Pomimo, że złączenia zapisane są w różnej kolejności, silnik bazodanowy i tak po swojemu zrealizuje plan działania. Wszystkie mają identyczny :

Pytanie które samo nasuwa się po analizie tego przypadku. Czy jednak kolejność joinów może mieć znaczenie? Jeśli tak to może spróbujmy „zoptymalizować” zapytanie i na siłę ją zmienić.

---

Kolejność połączeń a wydajność

Kolejność jest istotna. W końcu każde z takich łączeń, może produkować różnej wielkości zbiory pośrednie. Możemy założyć, że im bardziej selektywnie będą wykonywane złączenia od samego początku, tym bardziej efektywnie zostanie przetworzona cała kwerenda.

Sprawdźmy więc jakie plany zapytań, będą generowane dla pokazanych powyżej zapytań z zastosowaniem siłowego wymuszenia kolejności połączeń.

Mam możliwość ingerowania w proces optymalizacji poprzez zastosowanie HINTów. Do wymuszenia kolejności złączeń służy opcja :

          OPTION (FORCE ORDER)

Która wymusza na optymalizatorze określoną kolejność od LEWEJ do PRAWEJ. Wykonaj poniższy skrypt, który obrazuje 4 kwerendy zwracające te same wyniki. Skorzystaj z opcji Include Actual Execution Plan, aby porównać plany wykonania i ocenić procentowy udział obciążeniowy każdego z tych poleceń.

-- Kwerenda 1 – dajmy szanse optymalizatorowi
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
where c.City = 'Madrid'    and o.OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc

-- Kwerenda 2 z FORCE ORDER - identycznie zapisana jak Kwerenda 1
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
where c.City = 'Madrid'    and o.OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)

-- Kwerenda 3 z FORCE ORDER
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Orders o 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
	inner join dbo.Products p on od.ProductID = p.ProductID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
where City = 'Madrid'    and OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)

-- Kwerenda 4 z FORCE ORDER
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Products p 
	inner join dbo.[Order Details] od on od.ProductID = p.ProductID
	inner join dbo.Orders o on od.OrderID = o.OrderID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
where City = 'Madrid'    and OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)

Wydajnościowo, najlepsza okazała się kwerenda pierwsza, w której pozwoliliśmy optymalizatorowi, wybór własnego planu wykonania. Każda inna, wykorzystująca podpowiedź, siłowego narzucania kolejności działań Wyniki mówią same za siebie :

---

Stosowanie hintów bywa pomocne ale musi być stosowane świadomie. Rozkłady, optymalne sposoby dostępu do danych mogą się zmieniać. Raz utworzony plan, może po pewnym czasie nie być optymalnym, dlatego raczej odradzałbym stosowanie tego typu wskazówek początkującym użytkownikom SQL.