Pokonaj MERGE JOIN (INDEX SCAN) dzięki jednoznacznej wartości jednego KLUCZA na KLUCZU OBCYM

Dodano 7/11 Problem polega na tym, że wystąpiły zakleszczenia spowodowane skanowaniem indeksu podczas ŁĄCZENIA POŁĄCZENIA. W tym przypadku transakcja próbuje uzyskać blokadę S dla całego indeksu w tabeli nadrzędnej FK, ale poprzednio inna transakcja nakłada blokadę X na kluczową wartość indeksu.

Zacznę od małego przykładu (użyto TSQL2012 DB z 70-461 cource):

CREATE TABLE [Sales].[Orders](
[orderid] [int] IDENTITY(1,1) NOT NULL,
[custid] [int] NULL,
[empid] [int] NOT NULL,
[shipperid] [int] NOT NULL,
... )

Kolumny [custid], [empid], [shipperid]są odpowiednio skorelowanymi parametrami [Sales].[Customers], [HR].[Employees], [Sales].[Shippers]. W każdym przypadku mamy klastrowany indeks w określonej kolumnie w tabeli bieżącej.

ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([custid]) REFERENCES [Sales].[Customers] ([custid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([empid]) REFERENCES [HR].[Employees] ([empid])
ALTER TABLE [Sales].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([shipperid])REFERENCES [Sales].[Shippers] ([shipperid])

Próbuję do INSERT [Sales].[Orders] SELECT ... FROMinnej tabeli o nazwie, [Sales].[OrdersCache]która ma taką samą strukturę jak [Sales].[Orders]oprócz kluczy obcych. Inna rzecz, która może być ważna, aby wspomnieć o tabeli, [Sales].[OrdersCache]to indeks klastrowy.

CREATE CLUSTERED INDEX idx_c_OrdersCache ON Sales.OrdersCache ( custid, empid )

Zgodnie z oczekiwaniami, gdy próbuję wstawić niewielką ilość danych, funkcja LOOP JOIN działa dobrze, dzięki czemu indeksowanie kluczy obcych jest możliwe.

Przy dużych ilościach danych MERGE JOIN jest wykorzystywany przez optymalizator zapytań jako najbardziej efektywny sposób na utrzymanie klucza foregn w zapytaniu.

I nie ma to nic wspólnego z wyjątkiem opcji OPTION (LOOP JOIN) w naszym przypadku z kluczami obcymi lub INNER LOOP JOIN w jawnej JOIN.

Poniżej znajduje się zapytanie, które próbuję uruchomić w moim środowisku:

INSERT Sales.Orders (
        custid, empid, shipperid, ... )
SELECT  custid, empid, 2, ...
FROM Sales.OrdersCache

Patrząc na plan, widzimy, że wszystkie 3 klucze prognostyczne sprawdzone za pomocą MERGE JOIN. Nie jest to dla mnie odpowiedni sposób, ponieważ używa SKANOWANIA INDEKSU z blokowaniem całego indeksu.

Użycie OPCJI (ŁĄCZENIE PĘTLI) nie jest odpowiednie, ponieważ kosztuje prawie 15% więcej niż ŁĄCZENIE ŁĄCZENIA (myślę, że regresja będzie większa przy rosnącym wolumenie danych).

W instrukcji SELECT możesz zobaczyć jedną wartość shipperidatrybutu dla całego wstawionego zestawu. Moim zdaniem musi istnieć sposób na przyspieszenie fazy walidacji wstawionego zestawu przynajmniej dla atrybutu niezmiennego. Coś jak:

• wykonaj LOOP JOIN, MERGE JOIN, HASH JOIN, jeśli nie zdefiniowaliśmy podzbioru do sprawdzania JOIN
• jeśli istnieje tylko jedna wyraźna wartość zweryfikowanej kolumny, sprawdzanie poprawności przeprowadzamy tylko raz (INDEKS SEEK).

Czy istnieje jakiś wspólny wzorzec pozwalający na obejście powyższej sytuacji przy użyciu struktur kodu, dodatkowych obiektów DDL itp.?

Dodano 20/07. Rozwiązanie. Query Optimizer już dokonuje optymalizacji sprawdzania poprawności „jednego klucza - klucza obcego” za pomocą MERGE JOIN. I dotyczy tylko tabeli Sales.Shippers, pozostawiając LOOP JOIN dla innych połączeń w zapytaniu w tym samym czasie. Ponieważ mam kilka wierszy w tabeli nadrzędnej, Optymalizator zapytań używa algorytmu łączenia sortowania i porównuje każdy wiersz w tabeli wewnętrznej z tabelą nadrzędną tylko raz. To jest odpowiedź na moje pytanie, czy istnieje jakiś konkretny mechanizm skutecznego przetwarzania pojedynczych wartości w zestawie podczas sprawdzania poprawności pojedynczego klucza. To nie jest tak idealna decyzja, ale w ten sposób SQL Server optymalizuje sprawę.

Badanie wpływu na wydajność ujawniło, że w moim przypadku instrukcja wstawiania MERGE JOIN i LOOP JOIN stała się w przybliżeniu równa 750 jednocześnie wstawionym wierszom z następującą wyższością MERGE JOIN (w zasobach czasu procesora). Dlatego użycie OPTION (LOOP JOIN) jest odpowiednim rozwiązaniem dla mojego procesu biznesowego.

Korzystanie z OPCJI (ŁĄCZENIE PĘTLI) nie jest odpowiednie, ponieważ kosztuje prawie 15% więcej niż ŁĄCZENIE ŁĄCZENIA

Procenty kosztów wyświetlane w danych wyjściowych showplan są zawsze szacunkami modelu optymalizatora, nawet w planie (wykonawczym) po wykonaniu. Koszty te prawdopodobnie nie odzwierciedlają faktycznej wydajności środowiska uruchomieniowego na konkretnym sprzęcie. Jedynym sposobem, aby się upewnić, jest przetestowanie rozwiązań alternatywnych pod kątem obciążenia pracą i zmierzenie tych, które są dla Ciebie najważniejsze (czas, zużycie procesora i tak dalej).

Z mojego doświadczenia wynika, że ​​optymalizator zbyt wcześnie przełącza się z łączenia pętli, aby połączyć złączenie w celu weryfikacji klucza obcego. We wszystkich operacjach oprócz największych odkryłem, że lepsze są pętle. I przez „duże” rozumiem co najmniej dziesiątki milionów wierszy, na pewno nie tysiące, które wskazujesz w komentarzach do twojego pytania.

Moim zdaniem musi istnieć sposób na przyspieszenie fazy walidacji wstawionego zestawu przynajmniej dla atrybutu niezmiennego.

Zasadniczo ma to sens, ale obecnie nie ma takiej logiki w logice budowania planu, z której korzysta walidacja klucza obcego. Obecna logika jest celowo bardzo ogólna i prostopadła do szerszego zapytania; specyficzne optymalizacje komplikują testowanie i zwiększają prawdopodobieństwo wystąpienia błędów na krawędziach.

Czy istnieje jakiś wspólny wzorzec pozwalający na obejście powyższej sytuacji przy użyciu struktur kodu, dodatkowych obiektów DDL itp.?

Nie, że jestem tego świadomy. Ryzyko zakleszczenia przy scalaniu weryfikacji klucza obcego jest dobrze znane , a najczęściej stosowanym obejściem jest OPTION (LOOP JOIN)podpowiedź. Nie ma sposobu, aby uniknąć blokowania współużytkowanego podczas sprawdzania klucza obcego, ponieważ są one wymagane do poprawności , nawet przy poziomach izolacji wersji wiersza.

Nie ma lepszej ogólnej odpowiedzi (niż wskazówka dotycząca łączenia w pętli), jeśli chcesz zachować możliwość wielokrotnego równoczesnego dodawania wierszy do tabel nadrzędnych i podrzędnych. Ale jeśli chcesz serializować modyfikacje danych (nie czyta), użycie sp_getapplock jest niezawodną i prostą techniką.