Zazwyczaj odradzam używanie wskazówek dołączania ze wszystkich standardowych powodów. Ostatnio jednak znalazłem wzór, w którym prawie zawsze znajduję wymuszone łączenie pętli, aby uzyskać lepszą wydajność. W rzeczywistości zaczynam używać i polecam tak bardzo, że chciałem uzyskać drugą opinię, aby upewnić się, że coś nie umknie. Oto reprezentatywny scenariusz (bardzo konkretny kod do wygenerowania przykładu znajduje się na końcu):

--Case 1: NO HINT
SELECT S.*
INTO #Results
FROM #Driver AS D
JOIN SampleTable AS S ON S.ID = D.ID

--Case 2: LOOP JOIN HINT
SELECT S.*
INTO #Results
FROM #Driver AS D
INNER LOOP JOIN SampleTable AS S ON S.ID = D.ID

SampleTable ma 1 milion wierszy, a jego PK to ID.
Tabela temperatur #Driver ma tylko jedną kolumnę, identyfikator, brak indeksów i 50 000 wierszy.

To, co konsekwentnie znajduję, to:

Przypadek 1: śladu
Index Skanowanie na SampleTable
Hash Dołącz
Wyższą Duration (średnio 333ms)
Wyższa CPU (średnio 331ms)
Dolna logicznych odczytów (4714)

Przypadek 2:
WSKAŹNIK POŁĄCZENIA PĘTLI Indeks Szukaj na próbce
Pętla Dołącz do krótszego czasu
trwania (średnio 204 ms, 39% mniej)
Niższy procesor (średnio 206, 38% mniej)
Znacznie wyższe odczyty logiczne (160015, 34X więcej)

Na początku znacznie wyższe odczyty drugiego przypadku nieco mnie przeraziły, ponieważ obniżenie odczytów jest często uważane za przyzwoitą miarę wydajności. Ale im więcej myślę o tym, co się właściwie dzieje, nie dotyczy mnie to. Oto moje myślenie:

SampleTable znajduje się na 4714 stronach, zajmuje około 36 MB. Przypadek 1 skanuje je wszystkie, dlatego otrzymujemy 4714 odczytów. Co więcej, musi wykonać 1 milion skrótów, które intensywnie obciążają procesor i które ostatecznie wydłużają czas proporcjonalnie. To wszystko to mieszanie, które wydaje się zwiększać czas w przypadku 1.

Rozważmy teraz przypadek 2. Nie robi żadnego haszowania, ale zamiast tego robi 50000 oddzielnych poszukiwań, co napędza odczyty. Ale jak drogie są odczyty porównawcze? Można powiedzieć, że jeśli są to odczyty fizyczne, może być dość drogie. Pamiętaj jednak, że 1) tylko pierwszy odczyt danej strony może być fizyczny, a 2) nawet przypadek 1 miałby ten sam lub gorszy problem, ponieważ gwarantuje to trafienie każdej strony.

Biorąc pod uwagę fakt, że oba przypadki muszą uzyskać dostęp do każdej strony co najmniej raz, wydaje się, że pytanie jest szybsze, 1 milion skrótów lub około 155000 odczytów w stosunku do pamięci? Moje testy zdają się mówić o tym drugim, ale SQL Server konsekwentnie wybiera ten drugi.

Pytanie

Wracając do mojego pytania: czy powinienem ciągle wymuszać tę wskazówkę LOOP JOIN, gdy testy pokazują tego rodzaju wyniki, czy też brakuje mi czegoś w mojej analizie? Waham się przed optymalizacją programu SQL Server, ale wydaje się, że przełącza się na użycie sprzężenia mieszającego znacznie wcześniej niż powinno w takich przypadkach.

Aktualizacja 2014-04-28

Zrobiłem trochę więcej testów i odkryłem, że wyniki, które otrzymałem powyżej (na maszynie wirtualnej z 2 procesorami) nie mogłem replikować w innych środowiskach (próbowałem na 2 różnych fizycznych maszynach z 8 i 12 procesorami). Optymalizator radził sobie znacznie lepiej w tych ostatnich przypadkach do tego stopnia, że ​​nie było tak wyraźnego problemu. Wydaje mi się, że wyciągnięta lekcja, która wydaje się oczywista z perspektywy czasu, polega na tym, że środowisko może znacząco wpłynąć na to, jak działa optymalizator.

Plany wykonania

Plan wykonania Przypadek 1 Plan wykonania Przypadek 2

Kod generujący przykładowy przypadek

------------------------------------------------------------
-- 1. Create SampleTable with 1,000,000 rows
------------------------------------------------------------    

CREATE TABLE SampleTable
    (  
       ID         INT NOT NULL PRIMARY KEY CLUSTERED
     , Number1    INT NOT NULL
     , Number2    INT NOT NULL
     , Number3    INT NOT NULL
     , Number4    INT NOT NULL
     , Number5    INT NOT NULL
    )

--Add 1 million rows
;WITH  
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows  
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows  
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows 
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows 
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows 
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows 
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)
INSERT INTO SampleTable
SELECT Number, Number, Number, Number, Number, Number
FROM  FinalCte
WHERE Number <= 1000000

------------------------------------------------------------
-- Create 2 SPs that join from #Driver to SampleTable.
------------------------------------------------------------    
GO
IF OBJECT_ID('JoinTest_NoHint') IS NOT NULL DROP PROCEDURE JoinTest_NoHint
GO
CREATE PROC JoinTest_NoHint
AS
    SELECT S.*
    INTO #Results
    FROM #Driver AS D
    JOIN SampleTable AS S ON S.ID = D.ID
GO
IF OBJECT_ID('JoinTest_LoopHint') IS NOT NULL DROP PROCEDURE JoinTest_LoopHint
GO
CREATE PROC JoinTest_LoopHint
AS
    SELECT S.*
    INTO #Results
    FROM #Driver AS D
    INNER LOOP JOIN SampleTable AS S ON S.ID = D.ID
GO

------------------------------------------------------------
-- Create driver table with 50K rows
------------------------------------------------------------    
GO
IF OBJECT_ID('tempdb..#Driver') IS NOT NULL DROP TABLE #Driver
SELECT ID
INTO #Driver
FROM SampleTable
WHERE ID % 20 = 0

------------------------------------------------------------
-- Run each test and run Profiler
------------------------------------------------------------    

GO
/*Reg*/  EXEC JoinTest_NoHint
GO
/*Loop*/ EXEC JoinTest_LoopHint


------------------------------------------------------------
-- Results
------------------------------------------------------------    

/*

Duration CPU   Reads    TextData
315      313   4714     /*Reg*/  EXEC JoinTest_NoHint
309      296   4713     /*Reg*/  EXEC JoinTest_NoHint
327      329   4713     /*Reg*/  EXEC JoinTest_NoHint
398      406   4715     /*Reg*/  EXEC JoinTest_NoHint
316      312   4714     /*Reg*/  EXEC JoinTest_NoHint
217      219   160017   /*Loop*/ EXEC JoinTest_LoopHint
211      219   160014   /*Loop*/ EXEC JoinTest_LoopHint
217      219   160013   /*Loop*/ EXEC JoinTest_LoopHint
190      188   160013   /*Loop*/ EXEC JoinTest_LoopHint
187      187   160015   /*Loop*/ EXEC JoinTest_LoopHint

*/

SampleTable znajduje się na 4714 stronach, zajmuje około 36 MB. Przypadek 1 skanuje je wszystkie, dlatego otrzymujemy 4714 odczytów. Co więcej, musi wykonać 1 milion skrótów, które intensywnie obciążają procesor i które ostatecznie wydłużają czas proporcjonalnie. To wszystko to mieszanie, które wydaje się zwiększać czas w przypadku 1.

Łączenie mieszające ma koszt początkowy (budowanie tabeli skrótów, co jest również operacją blokującą), ale łączenie skrótowe ma ostatecznie najniższy teoretyczny koszt w wierszu trzech fizycznych typów łączenia obsługiwanych przez SQL Server, oba w warunki IO i CPU. Łączenie Hash naprawdę wchodzi w grę ze stosunkowo niewielkim wejściem kompilacji i dużym wejściem sondy. To powiedziawszy, żaden fizyczny typ złączenia nie jest „lepszy” we wszystkich scenariuszach.

Rozważmy teraz przypadek 2. Nie robi żadnego haszowania, ale zamiast tego robi 50000 oddzielnych poszukiwań, co napędza odczyty. Ale jak drogie są odczyty w porównaniu? Można powiedzieć, że jeśli są to odczyty fizyczne, może być dość drogie. Pamiętaj jednak, że 1) tylko pierwszy odczyt danej strony może być fizyczny, a 2) nawet przypadek 1 miałby ten sam lub gorszy problem, ponieważ gwarantuje to trafienie każdej strony.

Każde wyszukiwanie wymaga nawigacji b-drzewa do katalogu głównego, co jest kosztowne obliczeniowo w porównaniu z pojedynczą sondą mieszającą. Ponadto ogólny wzór IO dla wewnętrznej strony zagnieżdżonego sprzężenia pętli jest losowy, w porównaniu z wzorcem sekwencyjnego dostępu wejścia skanowania po stronie sondy do sprzężenia mieszającego. W zależności od bazowego fizycznego podsystemu we / wy sekwencyjne odczyty mogą być szybsze niż odczyty losowe. Ponadto mechanizm odczytu z wyprzedzeniem SQL Server działa lepiej z sekwencyjnym We / Wy, generując większe odczyty.

Biorąc pod uwagę fakt, że oba przypadki muszą uzyskać dostęp do każdej strony co najmniej raz, wydaje się, że pytanie jest szybsze, 1 milion skrótów lub około 155000 odczytów w stosunku do pamięci? Moje testy zdają się mówić o tym drugim, ale SQL Server konsekwentnie wybiera ten drugi.

Optymalizator zapytań programu SQL Server przyjmuje wiele założeń. Jednym z nich jest to, że pierwszy dostęp do strony utworzony przez zapytanie spowoduje fizyczne we / wy („założenie zimnej pamięci podręcznej”). Szansa, że ​​późniejszy odczyt nadejdzie ze strony już odczytanej do pamięci za pomocą tego samego zapytania, jest modelowana, ale nie jest to nic więcej niż wyuczone przypuszczenie.

Powodem, dla którego model optymalizatora działa w ten sposób, jest to, że generalnie lepiej jest zoptymalizować w najgorszym przypadku (potrzebne jest fizyczne IO). Wiele niedociągnięć można zlikwidować przez równoległość i zarządzanie pamięcią. Plany zapytań, które optymalizator stworzyłby, gdyby zakładał, że wszystkie dane są w pamięci, mogłyby działać bardzo słabo, gdyby założenie to okazało się nieprawidłowe.

Plan utworzony przy użyciu założenia zimnej pamięci podręcznej może nie działać tak dobrze, jak gdyby zamiast tego przyjęto ciepłą pamięć podręczną, ale jej najgorsza wydajność zwykle będzie lepsza.

Czy powinienem ciągle wymuszać tę wskazówkę LOOP JOIN, gdy testy pokazują tego rodzaju wyniki, czy też brakuje mi czegoś w mojej analizie? Waham się przed optymalizacją programu SQL Server, ale wydaje się, że przełącza się na użycie sprzężenia mieszającego znacznie wcześniej niż powinno w takich przypadkach.

Powinieneś być bardzo ostrożny z tego powodu z dwóch powodów. Po pierwsze, wskazówki dotyczące łączenia również po cichu wymuszają fizyczną kolejność łączenia zgodną z pisemną kolejnością zapytania (tak, jakbyś również to określił OPTION (FORCE ORDER). To poważnie ogranicza alternatywy dostępne dla optymalizatora i nie zawsze może być tym, czego chcesz.OPTION (LOOP JOIN) Siłom zagnieżdżone pętle łączy się dla zapytania, ale nie wymusza zapisanej kolejności łączenia.

Po drugie, zakładasz, że rozmiar zestawu danych pozostanie niewielki, a większość odczytów logicznych będzie pochodzić z pamięci podręcznej. Jeśli te założenia staną się nieważne (być może z czasem), wydajność ulegnie pogorszeniu. Wbudowany optymalizator zapytań jest dość dobry w reagowaniu na zmieniające się okoliczności; usunięcie tej wolności jest czymś, o czym powinieneś mocno się zastanowić.

Ogólnie rzecz biorąc, chyba że istnieje ważny powód, aby wymuszać łączenie się pętli, unikałbym tego. Domyślne plany są zazwyczaj dość zbliżone do optymalnych i wydają się być bardziej odporne w obliczu zmieniających się okoliczności.

50 000 wierszy połączonych z tabelą z milionami wierszy wydaje się dużo dla każdej tabeli bez indeksu.

Trudno powiedzieć dokładnie, co należy zrobić w tym przypadku, ponieważ jest on tak odizolowany od problemu, że próbujesz go rozwiązać. Mam nadzieję, że nie jest to ogólny wzorzec w kodzie, w którym łączysz się z wieloma nieindeksowanymi tabelami tymczasowymi ze znaczną ilością wierszy.

Biorąc przykład tylko z tego, co mówi, dlaczego nie po prostu umieścić indeksu na #Driver? Czy D.ID jest naprawdę wyjątkowy? Jeśli tak, jest to semantycznie równoważne instrukcji EXISTS, która przynajmniej poinformuje SQL Server, że nie chcesz kontynuować wyszukiwania S w poszukiwaniu zduplikowanych wartości D:

SELECT S.*
INTO #Results
FROM SampleTable S
WHERE EXISTS (SELECT * #Driver D WHERE S.ID = D.ID);

Krótko mówiąc, dla tego wzoru nie użyłbym podpowiedzi PĘTLI. Po prostu nie użyłbym tego wzoru. Zrobiłbym jedną z następujących czynności, w kolejności priorytetowej, jeśli jest to wykonalne:

• Jeśli to możliwe, użyj CTE zamiast tabeli tymczasowej dla #Driver
• Użyj unikalnego indeksu nieklastrowanego na #Driver na ID, jeśli jest unikalny (zakładając, że to jedyny raz, gdy używasz #Driver i nie chcesz żadnych danych z samej tabeli - jeśli faktycznie potrzebujesz danych z tej tabeli, może również zrobić indeks klastrowany)