W jaki sposób (i dlaczego) TOP wpływa na plan realizacji?

W przypadku średnio złożonego zapytania, które próbuję zoptymalizować, zauważyłem, że usunięcie TOP nklauzuli zmienia plan wykonania. Zgadłbym, że gdy zapytanie zawiera TOP nsilnik bazy danych, uruchomiłoby zapytanie ignorując TOPklauzulę, a następnie na koniec zmniejszyłem zestaw wyników do n żądanej liczby wierszy. Graficzny plan wykonania wydaje się wskazywać, że tak jest - TOPto „ostatni” krok. Ale wydaje się, że dzieje się więcej.

Moje pytanie brzmi: w jaki sposób (i dlaczego) klauzula TOP n wpływa na plan wykonania zapytania?

Oto uproszczona wersja tego, co dzieje się w moim przypadku:

Zapytanie pasuje do wierszy z dwóch tabel, A i B.

Bez TOPklauzuli optymalizator szacuje, że będzie 19 tys. Wierszy z tabeli A i 46 tys. Wierszy z tabeli B. Rzeczywista liczba zwróconych wierszy wynosi 16 tys. Dla A i 13 tys. Dla B. Dopasowanie mieszania jest używane do połączenia tych dwóch zestawów wyników dla łącznie 69 wierszy (następnie stosowane jest sortowanie). To zapytanie dzieje się bardzo szybko.

Po dodaniu TOP 1001optymalizator nie używa dopasowania mieszania; zamiast tego najpierw sortuje wyniki z tabeli A (ta sama wartość szacunkowa / rzeczywista 19k / 16k) i wykonuje zagnieżdżoną pętlę względem tabeli B. Szacowana liczba wierszy dla tabeli B wynosi teraz 1, a dziwne jest to, że TOP nbezpośrednio wpływa na szacunkowa liczba egzekucji (szukanie indeksu) w stosunku do B - wydaje się, że zawsze wynosi 2n + 1 , lub w moim przypadku 2003. To oszacowanie zmienia się odpowiednio, jeśli zmienię TOP n. Oczywiście, ponieważ jest to łączenie zagnieżdżone, faktyczna liczba wykonań wynosi 16k (liczba wierszy z tabeli A), co spowalnia zapytanie.

Rzeczywisty scenariusz jest nieco bardziej złożony, ale odzwierciedla on podstawowy pomysł / zachowanie. Obie tabele są przeszukiwane za pomocą wyszukiwania indeksowego. To jest wersja SQL Server 2008 R2 Enterprise.

Zgadłbym, że gdy zapytanie zawiera TOP n, silnik bazy danych uruchomiłby zapytanie ignorując klauzulę TOP, a następnie na koniec zmniejszyłem wynik ustawiony do n żądanej liczby wierszy. Graficzny plan wykonania wydaje się wskazywać, że tak jest - TOP jest „ostatnim” krokiem. Ale wydaje się, że dzieje się więcej.

Sposób wyrażenia powyższego sprawia, że ​​myślę, że możesz mieć niepoprawny obraz mentalny tego, jak wykonuje się zapytanie. Operator w planie zapytań nie jest krokiem (w którym pełny zestaw wyników poprzedniego kroku jest oceniany przez następny.

SQL Server używa potokowego modelu wykonania, w którym każdy operator ujawnia metody takie jak Init () , GetRow () i Close () . Jak sugeruje nazwa GetRow () , operator generuje jeden wiersz na żądanie na żądanie (zgodnie z wymaganiami operatora nadrzędnego). Jest to udokumentowane w podręczniku Books Online Logical and Physical Operators , a bardziej szczegółowo w moim poście na blogu Dlaczego plany zapytań działają wstecz . Ten model w rzędzie jest niezbędny do sformułowania rozsądnej intuicji do wykonywania zapytań.

Moje pytanie brzmi: w jaki sposób (i dlaczego) TOPklauzula n wpływa na plan wykonania zapytania?

Niektóre operacje logiczne, takie jak TOPpołączenia częściowe i FAST n wskazówka dotycząca zapytania, wpływają na sposób, w jaki optymalizator kwerend kosztuje alternatywne plany wykonania. Podstawową ideą jest to, że jeden możliwy kształt planu może zwrócić pierwsze n wierszy szybciej niż inny plan, który został zoptymalizowany pod kątem zwrócenia wszystkich rzędów.

Na przykład indeksowane łączenie zagnieżdżonych pętli jest często najszybszym sposobem na zwrócenie niewielkiej liczby wierszy, chociaż łączenie mieszające lub scalające ze skanami może być bardziej wydajne na większych zestawach. Sposób, w jaki optymalizator zapytań uzasadnia te wybory, polega na ustawieniu celu wiersza w określonym punkcie w logicznym drzewie operacji.

Cel wiersza zmienia sposób wyceny alternatywnych planów zapytań. Istotą tego jest to, że optymalizator zaczyna od wyceny każdego operatora tak, jakby był wymagany pełny zestaw wyników, ustawia cel wiersza w odpowiednim punkcie, a następnie pracuje z powrotem w drzewie planu, szacując liczbę wierszy, które spodziewa się zbadać aby osiągnąć cel rzędu.

Na przykład wartość logiczna TOP(10)ustawia cel wiersza na 10 w określonym punkcie logicznego drzewa zapytań. Koszty operatorów prowadzących do celu wiersza są modyfikowane, aby oszacować, ile wierszy muszą wyprodukować, aby zrealizować cel wiersza. Obliczenia te mogą stać się skomplikowane, dlatego łatwiej jest to wszystko zrozumieć, korzystając z w pełni działającego przykładu i opatrzonych adnotacjami planów wykonania. Cele wiersza mogą wpływać bardziej niż na wybór typu łączenia lub na to, czy preferowane są wyszukiwania i wyszukiwania niż skanowanie. Więcej informacji na ten temat tutaj .

Jak zawsze, plan wykonania wybrany na podstawie celu wiersza zależy od możliwości rozumowania optymalizatora i jakości dostarczanych mu informacji. Nie każdy plan z celem rzędów będzie generował wymaganą liczbę rzędów szybciej w praktyce, ale zgodnie z modelem wyceny będzie.

Tam, gdzie okazuje się, że plan bramkowy nie jest szybszy, zwykle istnieją sposoby modyfikacji zapytania lub dostarczenia optymalizatorowi lepszych informacji, tak aby naturalnie wybrany plan był najlepszy. To, która opcja jest odpowiednia w twoim przypadku, zależy oczywiście od szczegółów. Funkcja celu wiersza jest na ogół bardzo skuteczna (choć istnieje błąd, na który należy uważać, gdy jest używana w równoległych planach wykonania).

Twoje konkretne zapytanie i plan mogą nie być odpowiednie do szczegółowej analizy tutaj (za wszelką cenę zapewnij rzeczywisty plan wykonania, jeśli chcesz), ale mam nadzieję, że przedstawione tu pomysły pozwolą ci poczynić postępy.

Kiedy korzystasz z TOP, Optymalizator widzi okazję do wykonania mniejszej pracy. Jeśli poprosisz o 10 wierszy, istnieje duża szansa, że ​​nie trzeba zużywać całego zestawu. Dzięki temu operator TOP może zostać przesunięty znacznie dalej w prawo. Będzie żądał wierszy od następnego operatora (po prawej), dopóki nie otrzyma wystarczającej ilości.

Wskazujesz, że bez TOP kwerenda sortuje dane na samym końcu. Gdyby silnik mógł z góry wiedzieć, ile rzędów ma zadowolić złączenie, może równie dobrze zastosować podobny plan, ustawiając GÓRĘ po lewej stronie. Jednak z uwagi na stosunkowo wysoki wysiłek dopasowania dopasowania mieszania i przypuszczalnie brak opcji łączenia ze złączem optymalizator może zdecydować się na filtrowanie GÓRA bardziej po prawej stronie.

Gdy sprawdzana jest tabela B, pobiera ona pojedynczy wiersz na raz. Dlatego oszacowanie wynosi 1. Zakłada się również, że znajdzie ten wiersz tylko w 50% przypadków. Zgaduje, że będzie potrzebował 2n + 1 szuka, aby go znaleźć.