streszczenie
SQL Server używa prawidłowego przyłączenia (wewnętrznym lub zewnętrznym) i dodaje projekcje gdzie to konieczne do przestrzegania wszystkich semantykę oryginalnego zapytania przy wykonywaniu wewnętrznych tłumaczenia między zastosować i przyłączyć .
Różnice w planach można wyjaśnić różną semantyką agregatów z klauzulą i bez grupy w programie SQL Server.
Detale
Dołącz vs Zastosuj
Będziemy musieli być w stanie odróżnić podanie od przyłączenia :
Zastosować
Wewnętrzne (dolne) wejście Apply jest uruchamiane dla każdego rzędu zewnętrznego (górnego) wejścia, z jedną lub więcej wartości parametrów strony wewnętrznej dostarczonych przez bieżący zewnętrzny rząd. Ogólnym wynikiem zastosowania jest kombinacja (suma wszystkich) wszystkich wierszy utworzonych przez sparametryzowane wewnętrzne wykonanie strony. Obecność parametrów oznacza zastosowanie jest czasami nazywana połączeniem skorelowanym.
Zastosowanie, jest zawsze realizowane w planach realizacji przez zagnieżdżonych pętli operatora. Operator będzie miał właściwość Odwołania zewnętrzne zamiast dołączać predykaty. Zewnętrzne odniesienia to parametry przekazywane od strony zewnętrznej do strony wewnętrznej podczas każdej iteracji pętli.
Przystąp
Sprzężenie ocenia predykat złączenia u operatora sprzężenia. Łączenie może być generalnie realizowane przez operatorów Hash Match , Merge lub Nested Loops w SQL Server.
Po wybraniu zagnieżdżonych pętli można je odróżnić od zastosowania przez brak zewnętrznych odniesień (i zwykle obecność predykatu łączenia). Wewnętrzne wejście sprzężenia nigdy nie odwołuje się do wartości z wejścia zewnętrznego - strona wewnętrzna jest nadal wykonywana raz dla każdego zewnętrznego rzędu, ale wykonania strony wewnętrznej nie zależą od żadnych wartości z bieżącego zewnętrznego rzędu.
Aby uzyskać więcej informacji, zobacz mój post Zastosuj kontra dołącz do zagnieżdżonej pętli .
... dlaczego istnieje połączenie zewnętrzne w planie wykonania zamiast połączenia wewnętrznego ?
Sprzężenie zewnętrzne powstaje, gdy optymalizator przekształci zastosowanie do sprzężenia (przy użyciu reguły o nazwie ApplyHandler), aby sprawdzić, czy może znaleźć tańszy plan oparty na sprzężeniu. Łączenie musi być złączeniem zewnętrznym dla poprawności, gdy zastosowanie zawiera agregat skalarny . Nie możemy zagwarantować, że połączenie wewnętrzne przyniesie takie same wyniki, jak oryginalne zastosowanie, jak zobaczymy.
Agregaty skalarne i wektorowe
- Agregat bez odpowiedniej
GROUP BYklauzuli jest agregatem skalarnym .
- Agregat z odpowiednią
GROUP BYklauzulą jest agregatem wektorowym .
W SQL Server agregacja skalarna zawsze tworzy wiersz, nawet jeśli nie podano żadnych wierszy do agregacji. Na przykład COUNTsuma skalarna bez wierszy wynosi zero. Wektor COUNT suma wierszy nie jest zbiór pusty (nie wierszy w ogóle).
Poniższe zapytania dotyczące zabawek ilustrują różnicę. Możesz także przeczytać więcej o agregatach skalarnych i wektorowych w moim artykule Zabawa z agregatami skalarnymi i wektorowymi .
-- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;
-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();
db <> demo skrzypiec
Przekształcenie dotyczy dołączenia
Wspomniałem wcześniej, że połączenie musi być złączeniem zewnętrznym dla poprawności, gdy oryginalne zastosowanie zawiera agregat skalarny . Aby szczegółowo wyjaśnić, dlaczego tak się dzieje, użyję uproszczonego przykładu zapytania:
DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);
INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);
SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;
Prawidłowy wynik dla kolumny cto zero , ponieważ COUNT_BIGjest to agregat skalarny . Podczas tłumaczenia tego zapytania zastosowania do formularza przyłączenia SQL Server generuje wewnętrzną alternatywę, która wyglądałaby podobnie do poniższej, gdyby została wyrażona w T-SQL:
SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
SELECT B.A, c = COUNT_BIG(*)
FROM @B AS B
GROUP BY B.A
) AS J1
ON J1.A = A.A;
Aby przepisać zastosowanie jako łączenie nieskorelowane, musimy wprowadzić GROUP BYtabelę pochodną (w przeciwnym razie nie byłoby Akolumny do dołączenia). Łączenie musi być łączeniem zewnętrznym, aby każdy wiersz z tabeli @Anadal generował wiersz na wyjściu. Lewe złączenie utworzy NULLkolumnę for, cgdy predykat złączenia nie będzie miał wartości true. Trzeba NULLto przełożyć na zero, COALESCEaby dokończyć poprawną transformację od zastosowania .
Poniższa wersja demonstracyjna pokazuje, w jaki sposób sprzężenie zewnętrzne i COALESCEwymagane są do uzyskania tych samych wyników przy użyciu sprzężenia jako pierwotnego zapytania zastosowania :
db <> demo skrzypiec
Z GROUP BY
... dlaczego odkomentowanie grupy według klauzuli powoduje wewnętrzne połączenie?
Kontynuując uproszczony przykład, ale dodając GROUP BY:
DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);
INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);
-- Original
SELECT * FROM @A AS A
CROSS APPLY
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;
COUNT_BIGJest teraz wektor kruszywo, więc poprawny wynik dla pustego zbioru wejściowego nie jest już do zera, to nie rząd w ogóle . Innymi słowy, uruchomienie powyższych instrukcji nie daje żadnego wyniku.
Te semantyki są znacznie łatwiejsze do zaakceptowania podczas tłumaczenia z zastosowania do łączenia , ponieważ CROSS APPLYnaturalnie odrzuca każdy rząd zewnętrzny, który nie generuje wewnętrznych rzędów bocznych. Dlatego możemy teraz bezpiecznie korzystać z połączenia wewnętrznego, bez dodatkowej projekcji ekspresji:
-- Rewrite
SELECT A.*, J1.c
FROM @A AS A
JOIN
(
SELECT B.A, c = COUNT_BIG(*)
FROM @B AS B
GROUP BY B.A
) AS J1
ON J1.A = A.A;
Poniższa wersja demonstracyjna pokazuje, że przepisywanie wewnętrznego łączenia daje takie same wyniki, jak oryginalne zastosowanie z agregacją wektorową:
db <> demo skrzypiec
Optymalizator wybiera połączenie wewnętrzne z małym stolikiem, ponieważ szybko wyszukuje tani plan łączenia (znaleziono wystarczająco dobry plan). Optymalizator oparty na kosztach może przepisać połączenie z powrotem na aplikację - być może znajdując tańszy plan zastosowania, tak jak tutaj, jeśli zostanie użyte sprzężenie w pętli lub wskazówka forceseek - ale w tym przypadku nie jest to warte wysiłku.
Notatki
Uproszczone przykłady wykorzystują różne tabele o różnych treściach, aby wyraźniej pokazać różnice semantyczne.
Można argumentować, że optymalizator powinien być w stanie uzasadnić, że samosprzężenie nie jest w stanie wygenerować niedopasowanych (niepołączonych) wierszy, ale dzisiaj nie zawiera takiej logiki. Wielokrotny dostęp do tej samej tabeli w zapytaniu nie gwarantuje generalnie tych samych wyników, w zależności od poziomu izolacji i równoczesnej aktywności.
Optymalizator martwi się tymi semantykami i przypadkami brzegowymi, więc nie musisz.
Bonus: wewnętrzny plan zastosowania
SQL Server może utworzyć wewnętrzny plan zastosowania (nie wewnętrzny plan łączenia !) Dla przykładowego zapytania, po prostu decyduje się go nie robić ze względu na koszty. Koszt zewnętrznego planu dołączenia pokazanego w pytaniu wynosi 0,02898 jednostek w wystąpieniu SQL Server 2017 mojego laptopa.
Możesz wymusić zastosowanie planu (skorelowanego łączenia) za pomocą nieudokumentowanej i nieobsługiwanej flagi śledzenia 9114 (która wyłącza ApplyHandleritp.) Tylko dla ilustracji:
SELECT *
FROM #MyTable AS mt
CROSS APPLY
(
SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
FROM #MyTable AS mt2
WHERE mt2.Col_A = mt.Col_A
--GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);
Powoduje to zastosowanie planu zagnieżdżonych pętli z leniwą szpulą indeksu. Całkowity szacowany koszt wynosi 0,0463983 (wyższy niż wybrany plan):
Należy pamiętać, że plan wykonania z zastosowaniem zastosowanych pętli zagnieżdżonych daje prawidłowe wyniki przy użyciu semantyki „łączenia wewnętrznego” bez względu na obecność GROUP BYklauzuli.
W rzeczywistym świecie, chcielibyśmy zazwyczaj mają indeks do wspierania poszukiwania na wewnętrznej stronie stosuje zachęcić SQL Server, aby wybrać tę opcję, naturalnie, na przykład:
CREATE INDEX i ON #MyTable (Col_A, Col_B);
db <> demo skrzypiec
