konkatenacja dwóch lub więcej zbiorów danych jest najczęściej wyrażana w T-SQL za pomocą klauzuliUNION ALL. Biorąc pod uwagę, że SQL Server optimizer może często zmieniać kolejność połączeń i agregatów w celu poprawy wydajności, całkiem rozsądne jest oczekiwanie, że SQL Server rozważy również zmianę kolejności wejść konkatenacyjnych, gdzie zapewniłoby to przewagę. Na przykład optymalizator może rozważyć korzyści wynikające z przepisania A UNION ALL B jako B UNION ALL A.

w rzeczywistości optymalizator SQL Server tego nie robi. Dokładniej rzecz biorąc, w wersjach SQL Server do 2008 R2 było pewne ograniczone wsparcie dla zmiany kolejności danych wejściowych konkatenacji, ale zostało to usunięte w SQL Server 2012 i od tego czasu nie pojawiło się ponownie.

SQL Server 2008 R2

intuicyjnie kolejność wejść konkatenacyjnych ma znaczenie tylko wtedy, gdy istnieje cel wiersza. Domyślnie SQL Server optymalizuje plany wykonania na podstawie tego, że wszystkie kwalifikujące się wiersze zostaną zwrócone klientowi. Gdy cel wiersza jest aktywny, optymalizator próbuje znaleźć plan wykonania, który szybko wytworzy kilka pierwszych wierszy.

cele wiersza mogą być ustawione na wiele sposobów, na przykład za pomocąTOPFAST n podpowiedzi zapytania lub za pomocąEXISTS (który ze swojej natury musi znaleźć co najwyżej jeden wiersz). Tam, gdzie nie ma celu wiersza (tzn. klient wymaga wszystkich wierszy), generalnie nie ma znaczenia, w jakiej kolejności odczytywane są wejścia konkatenacyjne: każde wejście zostanie ostatecznie w pełni przetworzone.

ograniczona obsługa w wersjach do SQL Server 2008 R2 ma zastosowanie tam, gdzie jest cel dokładnie jednego wiersza. W tej konkretnej sytuacji SQL Server zmieni kolejność danych konkatenacyjnych na podstawie oczekiwanych kosztów.

nie odbywa się to podczas optymalizacji opartej na kosztach (jak można się spodziewać), ale raczej w ostatniej chwili po optymalizacji przepisanie normalnego wyjścia optymalizatora. Ten układ ma tę zaletę, że nie zwiększa przestrzeni wyszukiwania planu opartego na kosztach (potencjalnie jedna alternatywa dla każdego możliwego ponownego zamówienia), a jednocześnie tworzy plan, który jest zoptymalizowany, aby szybko powrócić do pierwszego rzędu.

przykłady

poniższe przykłady wykorzystują dwie tabele o identycznej zawartości: milion wierszy liczb całkowitych od jednego do miliona. Jedna tabela jest stertą bez indeksów nieklustrowanych; druga ma unikalny indeks klastrowy:

CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);

Brak celu wiersza

poniższe zapytanie wyszukuje te same wiersze w każdej tabeli i zwraca konkatenację dwóch zestawy:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;

plan wykonania stworzony przez optymalizator zapytań to:

ostrzeżenie na głównymSELECT operator ostrzega nas o oczywistym brakującym indeksie w tabeli sterty. Ostrzeżenie na table scan operator jest dodawane przez Sentry One Plan Explorer. Zwraca to naszą uwagę na koszt We / Wy pozostałego predykatu ukrytego w skanie.

kolejność wejść do konkatenacji nie ma tu znaczenia, ponieważ nie ustawiliśmy celu wiersza. Oba wejścia zostaną w pełni odczytane, aby zwrócić wszystkie wiersze wyników. Interesujące (choć nie jest to gwarantowane) zauważ, że kolejność wejść jest następująca po kolejności tekstowej pierwotnego zapytania. Zauważ również, że kolejność wierszy wyniku końcowego również nie jest określona, ponieważ nie użyliśmy klauzuli ORDER BY najwyższego poziomu. Zakładamy, że jest to celowe i ostateczne zamówienie jest nieistotne dla danego zadania.

Jeśli odwrócimy zapisaną kolejność tabel w zapytaniu tak:

SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;

plan wykonania następuje po zmianie, uzyskując najpierw dostęp do tabeli klastrowej (ponownie nie jest to gwarantowane):

można oczekiwać, że oba zapytania będą miały tę samą charakterystykę wydajności, ponieważ wykonują te same operacje, tylko w innej kolejności.

z celem wiersza

wyraźnie brak indeksowania w tabeli sterty spowoduje, że znalezienie konkretnych wierszy będzie droższe w porównaniu z tą samą operacją w tabeli klastrowej. Jeśli poprosimy optymalizatora o plan, który szybko zwróci pierwszy wiersz, spodziewamy się, że SQL Server zmieni kolejność danych wejściowych konkatenacji, aby najpierw skonsultować tanią tabelę klastrową.

używając zapytania, które najpierw wspomina tabelę sterty, i używając szybkiej podpowiedzi 1 zapytania, aby określić cel wiersza:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);

szacunkowy plan wykonania stworzony na instancji SQL Server 2008 R2 wynosi:

zauważ, że nakłady konkatenacyjne zostały uporządkowane w celu zmniejszenia szacunkowych kosztów zwrotu pierwszego wiersza. Zauważ również, że brakujący indeks i pozostałe Ostrzeżenia We/Wy zniknęły. Żaden problem nie jest konsekwencją tego kształtu planu, gdy celem jest jak najszybsze zwrócenie jednego wiersza.

to samo zapytanie wykonywane na SQL Server 2016 (przy użyciu obu modeli szacowania cardinality) to:

SQL Server 2016 nie zmienił kolejności wejść konkatenacji. Ostrzeżenie Plan Explorer I / O powróciło, ale niestety tym razem optymalizator nie wygenerował brakującego ostrzeżenia o indeksie (choć jest to istotne).

ogólne zmiany kolejności

jak wspomniano, przepisanie po optymalizacji, które zmienia kolejność wejść konkatenacyjnych, jest skuteczne tylko dla:

• SQL Server 2008 R2 i wcześniejsze
• cel wiersza dokładnie jednego

Jeśli naprawdę chcemy zwrócić tylko jeden wiersz, zamiast planu zoptymalizowanego tak, aby szybko zwrócił pierwszy wiersz (ale który ostatecznie nadal zwróci wszystkie wiersze), możemy użyćTOP klauzuli z pochodną tabeli lub wspólnym wyrażeniem tabeli (CTE):

SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;

na SQL Server 2008 R2 lub wcześniejszym, tworzy to optymalny plan zmiany kolejności danych wejściowych:

Na SQL serverze 2012, 2014 i 2016 nie występuje zmiana kolejności po optymalizacji:

Jeśli chcemy zwrócić więcej niż jeden wiersz, na przykład używając TOP (2), żądane przepisanie nie będzie zastosowane w SQL Server 2008 R2, nawet jeśli zostanie użyta podpowiedź FAST 1. W takiej sytuacji musimy uciekać się do sztuczek, takich jak użycieTOP ze zmienną i podpowiedziąOPTIMIZE FOR :

DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint

podpowiedź do zapytania jest wystarczająca, aby ustawić cel wiersza jeden, podczas gdy wartość runtime zmiennej zapewnia, że zostanie zwrócona żądana liczba wierszy (2).

rzeczywisty plan wykonania na SQL Server 2008 R2 jest następujący:

oba wiersze zwrócone pochodzą z uporządkowanego wejścia seek, a Skanowanie tabeli w ogóle nie jest wykonywane. Plan Explorer pokazuje liczbę wierszy na czerwono, ponieważ oszacowanie dotyczyło jednego wiersza (ze względu na podpowiedź), podczas gdy w czasie uruchamiania napotkano dwa wiersze.

bez UNION ALL

ten problem nie ogranicza się również do zapytań pisanych jawnie zUNION ALL. Inne konstrukcje, takie jakEXISTS IOR, mogą również spowodować, że optymalizator wprowadzi operator konkatenacji, który może cierpieć z powodu braku zmiany kolejności wejścia. Niedawno pojawiło się pytanie dotyczące wymiany stosów administratorów baz danych z dokładnie tym problemem. Przekształcenie zapytania z tego pytania w nasze przykładowe tabele:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;

plan wykonania na SQL Server 2016 ma tabelę sterty na pierwszym wejściu:

na SQL Server 2008 R2 kolejność wejść jest zoptymalizowana tak, aby odzwierciedlała cel pojedynczego wiersza Semi join:

w bardziej optymalnym planie sterta skanowanie nigdy nie jest wykonywane.

obejścia

w niektórych przypadkach dla autora zapytań będzie oczywiste, że jedno z wejść konkatenacji będzie zawsze tańsze w uruchomieniu niż pozostałe. Jeśli to prawda, to poprawne jest przepisanie zapytania tak, aby tańsze wejścia konkatenacji pojawiły się jako pierwsze w kolejności pisemnej. Oczywiście oznacza to, że autor zapytań musi być świadomy tego ograniczenia optymalizatora i gotowy polegać na nieudokumentowanym zachowaniu.

trudniejszy problem pojawia się, gdy koszt wejść konkatenacyjnych zmienia się w zależności od okoliczności, być może w zależności od wartości parametrów. Użycie OPTION (RECOMPILE) nie pomoże NA SQL Server 2012 lub nowszym. Opcja ta może pomóc w SQL Server 2008 R2 lub wcześniejszym, ale tylko wtedy, gdy wymóg pojedynczego wiersza jest również spełniony.

Jeśli istnieją obawy dotyczące polegania na obserwowanym zachowaniu (dane wejściowe dotyczące konkatenacji planu kwerendy pasujące do kolejności tekstowej kwerendy), można użyć przewodnika planu, aby wymusić kształt planu. Tam, gdzie różne zamówienia wejściowe są optymalne dla różnych okoliczności, można użyć wielu przewodników planu, gdzie warunki mogą być dokładnie zakodowane z wyprzedzeniem. Nie jest to jednak idealne rozwiązanie.

myśli końcowe

optymalizator zapytań SQL Server w rzeczywistości zawiera regułę eksploracji opartej na kosztach, UNIAReorderInputs, która jest w stanie generować zmiany kolejności wprowadzania konkatenacji i eksplorować alternatywy podczas optymalizacji opartej na kosztach (NIE jako jednokrotne przepisanie po optymalizacji).

Ta reguła nie jest obecnie włączona do ogólnego użytku. O ile mogę powiedzieć, jest on aktywowany tylko wtedy, gdy istnieje przewodnik planu lub USE PLAN wskazówka. Pozwala to silnikowi z powodzeniem wymusić plan wygenerowany dla zapytania, które kwalifikuje się do przepisania kolejności wprowadzania, nawet jeśli bieżące zapytanie nie kwalifikuje się.

mam wrażenie, że ta zasada eksploracji jest celowo ograniczona do tego zastosowania, ponieważ zapytania, które skorzystałyby na ponownym zamówieniu danych wejściowych konkatenacji w ramach optymalizacji opartej na kosztach, są uważane za niewystarczająco powszechne, a może dlatego, że istnieje obawa, że dodatkowy wysiłek nie opłaci się. Moim zdaniem zmiana kolejności danych wejściowych operatora konkatenacji powinna być zawsze badana, gdy cel wiersza jest w mocy.

szkoda również, że (bardziej ograniczone) przepisanie po optymalizacji nie jest skuteczne w SQL Server 2012 lub nowszym. Mogło to być spowodowane subtelnym błędem, ale nie mogłem znaleźć nic na ten temat w dokumentacji, bazie wiedzy ani na Connect. Dodałem tutaj nowy element Connect.

aktualizacja 9 sierpnia 2017: jest to teraz naprawione pod flagą śledzenia 4199 dla SQL Server 2014 i 2016, patrz KB 4023419:

FIX: Zapytanie z UNION ALL i celem wiersza może działać wolniej w SQL Server 2014 lub nowszych wersjach, gdy jest porównywane do SQL Server 2008 R2