Dlaczego dodanie TOP 1 dramatycznie pogarsza wydajność?

Mam dość proste zapytanie

SELECT TOP 1 dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

To daje mi okropną wydajność (jakbym nigdy nie zadał sobie trudu, aby czekać na zakończenie). Plan zapytań wygląda następująco:

Jeśli jednak usunę TOP 1plan, otrzymam plan, który wygląda tak i działa w ciągu 1-2 sekund:

Prawidłowe PK i indeksowanie poniżej.

Fakt, że TOP 1zmieniony plan zapytań nie dziwi mnie, jestem tylko trochę zaskoczony, że to znacznie gorzej.

Uwaga: przeczytałem wyniki tego postu i rozumiem pojęcie Row Goalitd. Ciekawe, jak mogę zmienić zapytanie, aby korzystało z lepszego planu. Obecnie zrzucam dane do tabeli tymczasowej, a następnie wyciągam z niej pierwszy wiersz. Zastanawiam się, czy istnieje lepsza metoda.

Edytuj Dla osób czytających to po fakcie tutaj jest kilka dodatkowych informacji.

• Document_Queue - PK / CI to D_ID i ma ~ 5 tys. Wierszy.
• Correspondence_Journal - PK / CI to FILE_NUMBER, CORRESPONDENCE_ID i ma ~ 1,4 miliona wierszy.

Kiedy zaczynałem, nie było innych indeksów. Skończyłem z jednym na Correspondence_Journal (Document_Id, File_Number)

Spróbuj wymusić dołączenie skrótu *

SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

Optymalizator prawdopodobnie pomyślał, że pętla będzie lepsza z topem 1 i tego rodzaju ma sens, ale w rzeczywistości tutaj nie działała. Tylko zgadnij tutaj, ale być może szacunkowy koszt tej szpuli był wyłączony - używa TEMPDB - możesz mieć słabo działający TEMPDB.

* Uważaj na podpowiedzi dotyczące łączenia , ponieważ wymuszają porządek dostępu do tabeli w tabeli zgodnie z kolejnością zapisywania tabel w zapytaniu (tak, jakby OPTION (FORCE ORDER)podano). Z linku do dokumentacji:

Może to nie powodować żadnych niepożądanych efektów w tym przykładzie, ale ogólnie może bardzo dobrze. FORCE ORDER(domniemana lub wyraźna) to bardzo silna wskazówka, która wykracza poza egzekwowanie porządku; zapobiega stosowaniu szerokiego zakresu technik optymalizacyjnych, w tym częściowej agregacji i zmiany kolejności.

Wskazówka dotycząca OPTION (HASH JOIN) zapytania może być mniej inwazyjna w odpowiednich przypadkach, ponieważ nie oznacza to FORCE ORDER. Dotyczy to jednak wszystkich złączeń w zapytaniu. Dostępne są inne rozwiązania.

Skoro masz odpowiedni plan ORDER BY, może mógłbyś po prostu wyrzucić własnego TOPoperatora?

SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER
FROM (
    SELECT dc.DOCUMENT_ID,
           dc.COPIES,
           dc.REQUESTOR,
           dc.D_ID,
           cj.FILE_NUMBER,
           ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum
    FROM DOCUMENT_QUEUE dc
    INNER JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
    WHERE dc.QUEUE_DATE <= GETDATE()
      AND dc.PRINT_LOCATION = 2
) AS sub
WHERE _rownum=1;

Moim zdaniem plan zapytań dla ROW_NUMBER()powyższych powinien być taki sam, jak gdybyś miał ORDER BY. Plan zapytań powinien mieć teraz segment, projekt sekwencji i na koniec operator filtru, reszta powinna wyglądać jak twój dobry plan.

Edycja: +1 działa w tej sytuacji, ponieważ okazuje się, że FILE_NUMBERjest to liczba całkowita z zerową liczbą znaków. Lepszym rozwiązaniem tutaj dla ciągów jest dołączanie ''(pusty ciąg), ponieważ dodanie wartości może wpływać na kolejność lub dla liczb, aby dodać coś, co jest stałe, ale zawiera funkcję niedeterministyczną, np sign(rand()+1). Pomysł „przełamania sortowania” jest nadal aktualny, po prostu moja metoda nie była idealna.

+1

Nie, nie mam na myśli, że się z czymkolwiek zgadzam, mam na myśli to jako rozwiązanie. Jeśli zmienisz zapytanie na, ORDER BY cj.FILE_NUMBER + 1wówczas TOP 1będą się one zachowywać inaczej.

Widzisz, z celem małego wiersza dla zamówionego zapytania, system spróbuje wykorzystać dane w celu uniknięcia operatora sortowania. Pozwoli to również uniknąć budowania tabeli skrótów, zakładając, że prawdopodobnie nie będzie musiał wykonywać zbyt wiele pracy, aby znaleźć ten pierwszy wiersz. W twoim przypadku jest to błędne - z grubości tych strzałek wygląda na to, że trzeba zużyć dużo danych, aby znaleźć pojedyncze dopasowanie.

Grubość tych strzałek sugeruje, że twój DOCUMENT_QUEUE(DQ) stół jest znacznie mniejszy niż twój CORRESPONDENCE_JOURNAL(CJ) stół. I że najlepszym planem byłoby sprawdzenie wierszy DQ aż do znalezienia wiersza CJ. Rzeczywiście, to właśnie zrobiłby Optymalizator Kwerend (QO), gdyby nie miał tego nieznośnego ORDER BY, co jest ładnie wspierane przez indeks przykrywający CJ.

Więc jeśli ORDER BYcałkowicie upuściłeś , spodziewam się, że dostaniesz plan obejmujący zagnieżdżoną pętlę, iterującą po wierszach w DQ, szukającą w CJ, aby upewnić się, że wiersz istnieje. I z TOP 1tym skończy się po wyciągnięciu jednego rzędu.

Ale jeśli faktycznie potrzebujesz pierwszego rzędu FILE_NUMBER, możesz oszukać system, aby zignorował ten indeks, który wydaje się (niepoprawnie) tak pomocny, robiąc ORDER BY CJ.FILE_NUMBER+1- co, jak wiemy, zachowa taką samą kolejność jak poprzednio, ale co ważne, QO nie. QO skupi się na przygotowaniu całości, aby operator Top N Sort mógł być zadowolony. Ta metoda powinna stworzyć plan, który zawiera operator skalowania obliczeniowego, aby obliczyć wartość dla zamówienia, oraz operator sortowania Top N, aby uzyskać pierwszy wiersz. Ale na prawo od nich powinieneś zobaczyć ładną zagnieżdżoną pętlę, wykonującą wiele poszukiwań na CJ. I lepsza wydajność niż przeglądanie dużej tabeli wierszy, które nie pasują do niczego w DQ.

Mecz Hash niekoniecznie jest okropny, ale jeśli zestaw wierszy, które zwracasz z DQ, jest znacznie mniejszy niż CJ (tak bym się spodziewał), to Hash Match będzie skanował znacznie więcej CJ niż potrzebuje.

Uwaga: Użyłem +1 zamiast +0, ponieważ optymalizator zapytań prawdopodobnie rozpozna, że ​​+0 nic nie zmienia. Oczywiście to samo może dotyczyć +1, jeśli nie teraz, to w pewnym momencie w przyszłości.

Przeczytałem wyniki z tego postu i rozumiem pojęcie celu w wierszu itp. Ciekawe, jak mogę zmienić zapytanie, aby korzystało z lepszego planu

Dodanie OPTION (QUERYTRACEON 4138)wyłącza efekt celów wierszy tylko dla tego zapytania, bez nadmiernego nakazu dotyczącego ostatecznego planu, i prawdopodobnie będzie to najprostszy / najbardziej bezpośredni sposób.

Jeśli dodanie tej podpowiedzi powoduje błąd uprawnień (wymagany w przypadku DBCC TRACEON), możesz zastosować ją, korzystając z przewodnika planu:

Korzystanie QUERYTRACEONz przewodników planu przez spaghettidba

... lub po prostu użyj procedury składowanej:

Jakie uprawnienia są QUERYTRACEONpotrzebne? autor: Kendra Little

Nowsze wersje SQL Server oferują różne (i prawdopodobnie lepsze) opcje radzenia sobie z zapytaniami, które osiągają nieoptymalną wydajność, gdy optymalizator jest w stanie zastosować optymalizację celu wiersza. Dodatek SP1 dla programu SQL Server 2016 wprowadził taki DISABLE_OPTIMIZER_ROWGOAL USE HINTsam efekt, jak flaga śledzenia 4138. Jeśli nie korzystasz z tej wersji, możesz również rozważyć skorzystanie ze OPTIMIZE FORwskazówki dotyczącej zapytania, aby uzyskać plan zapytania zaprojektowany tak, aby zwracał wszystkie wiersze zamiast tylko 1. Zapytanie poniżej zwróci te same wyniki, co w pytaniu, ale nie zostanie utworzone w celu uzyskania tylko 1 wiersza.

DECLARE @top INT = 1;

SELECT TOP (@top) dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER
OPTION (OPTIMIZE FOR (@top = 987654321));

Ponieważ robisz to TOP(1), zalecam ORDER BYna początek deterministyczny. Przynajmniej zapewni to funkcjonalnie przewidywalne wyniki (zawsze przydatne w testach regresyjnych). Wygląda na to trzeba dodać DC.D_IDi CJ.CORRESPONDENCE_IDza to.

Przyglądając się planom zapytań, czasem uprościłem zapytanie: Być może wcześniej wybieram wszystkie odpowiednie wiersze DC w tabeli tymczasowej, aby wyeliminować problemy z oszacowaniem liczności na QUEUE_DATEi PRINT_LOCATION. Powinno to być szybkie, biorąc pod uwagę niską liczbę wierszy. Następnie możesz dodać indeksy do tej tabeli tymczasowej, jeśli to konieczne, bez zmiany stałej tabeli.