POŁĄCZ podzbiór tabeli docelowej

Próbuję użyć MERGEinstrukcji, aby wstawić lub usunąć wiersze z tabeli, ale chcę działać tylko na podzestawie tych wierszy. Dokumentacja MERGEzawiera dość mocno sformułowane ostrzeżenie:

Ważne jest, aby określić tylko kolumny z tabeli docelowej, które są używane do dopasowywania celów. To znaczy określ kolumny z tabeli docelowej, które są porównywane z odpowiednią kolumną tabeli źródłowej. Nie należy poprawiać wydajności kwerendy, odfiltrowując wiersze w tabeli docelowej w klauzuli ON, na przykład określając AND NOT target_table.column_x = wartość. Może to spowodować nieoczekiwane i niepoprawne wyniki.

ale wydaje się, że właśnie to muszę zrobić, aby wykonać swoją MERGEpracę.

Dane, które posiadam, są standardową tabelą łączenia wielu elementów w kategorie (np. Które elementy są zawarte w jakich kategoriach):

CategoryId   ItemId
==========   ======
1            1
1            2
1            3
2            1
2            3
3            5
3            6
4            5

Muszę skutecznie zastąpić wszystkie wiersze w określonej kategorii nową listą elementów. Moja pierwsza próba zrobienia tego wygląda następująco:

MERGE INTO CategoryItem AS TARGET
USING (
  SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2
) AS SOURCE
ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( CategoryId, ItemId )
    VALUES ( 2, ItemId )
WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN
    DELETE ;

To wydaje się działać w moich testach, ale robię dokładnie to, co MSDN wyraźnie ostrzega mnie nie do zrobienia. Niepokoi mnie to, że później napotkam nieoczekiwane problemy, ale nie widzę innego sposobu, aby wywrzeć MERGEwpływ tylko na wiersze o określonej wartości pola ( CategoryId = 2) i zignorować wiersze z innych kategorii.

Czy istnieje „bardziej poprawny” sposób na osiągnięcie tego samego wyniku? A jakie są „nieoczekiwane lub niepoprawne wyniki”, o których ostrzega mnie MSDN?

MERGEOświadczenie ma złożoną składnię i jeszcze bardziej skomplikowane wdrożenie, ale przede wszystkim chodzi o to, aby połączyć dwie tabele, filtr do wierszy, które muszą być zmienione (wstawione, zaktualizowane lub usunięte), a następnie wykonać żądane zmiany. Biorąc pod uwagę następujące przykładowe dane:

DECLARE @CategoryItem AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL,

    PRIMARY KEY (CategoryId, ItemId),
    UNIQUE (ItemId, CategoryId)
);

DECLARE @DataSource AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL

    PRIMARY KEY (CategoryId, ItemId)
);

INSERT @CategoryItem
    (CategoryId, ItemId)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 3),
    (3, 5),
    (3, 6),
    (4, 5);

INSERT @DataSource
    (CategoryId, ItemId)
VALUES
    (2, 2);

Cel

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          2 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      3 ║
║          3 ║      5 ║
║          4 ║      5 ║
║          3 ║      6 ║
╚════════════╩════════╝

Źródło

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

Pożądanym rezultatem jest zastąpienie danych w celu danymi ze źródła, ale tylko dla CategoryId = 2. Zgodnie z opisem MERGEpodanym powyżej powinniśmy napisać zapytanie, które łączy źródło i cel tylko na kluczach i filtrować wiersze tylko w WHENklauzulach:

MERGE INTO @CategoryItem AS TARGET
USING @DataSource AS SOURCE ON 
    SOURCE.ItemId = TARGET.ItemId 
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY SOURCE 
    AND TARGET.CategoryId = 2 
    THEN DELETE
WHEN NOT MATCHED BY TARGET 
    AND SOURCE.CategoryId = 2 
    THEN INSERT (CategoryId, ItemId)
        VALUES (CategoryId, ItemId)
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

Daje to następujące wyniki:

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
╚═════════╩════════════╩════════╝
╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      2 ║
║          3 ║      5 ║
║          3 ║      6 ║
║          4 ║      5 ║
╚════════════╩════════╝

Plan wykonania jest:

Zauważ, że obie tabele są w pełni skanowane. Możemy uważać to za nieefektywne, ponieważ tylko wiersze, na które CategoryId = 2wpłynie to w tabeli docelowej. W tym miejscu pojawiają się ostrzeżenia w Books Online. Jedna błędna próba optymalizacji, by dotknąć tylko niezbędnych wierszy w celu, to:

MERGE INTO @CategoryItem AS TARGET
USING 
(
    SELECT CategoryId, ItemId
    FROM @DataSource AS ds 
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

Logika w ONklauzuli jest stosowana jako część sprzężenia. W takim przypadku złączenie jest pełnym zewnętrznym złączeniem ( dlaczego w tej książce online ). Zastosowanie sprawdzania kategorii 2 w wierszach docelowych jako część złączenia zewnętrznego ostatecznie powoduje usunięcie wierszy z inną wartością (ponieważ nie pasują do źródła):

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          1 ║      1 ║
║ DELETE  ║          1 ║      2 ║
║ DELETE  ║          1 ║      3 ║
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
║ DELETE  ║          3 ║      5 ║
║ DELETE  ║          3 ║      6 ║
║ DELETE  ║          4 ║      5 ║
╚═════════╩════════════╩════════╝

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

Podstawową przyczyną jest ten sam powód, dla którego predykaty zachowują się inaczej w zewnętrznej ONklauzuli łączenia , niż mają to miejsce, jeśli są określone w tej WHEREklauzuli. MERGESkładnia (i realizacja przyłączenia w zależności od określonych klauzul) tylko utrudnić, aby zobaczyć, że tak jest.

Poradnictwo w Books Online (rozszerzony w Optymalizacja wydajności wejściu) daje wskazówki, które zapewnią prawidłową semantycznej jest wyrażona za pomocą MERGEskładni, bez użytkownik koniecznie konieczności zrozumieć wszystkie szczegóły implementacji lub konto dla sposobu, w jaki optymalizator może legalnie przestawiać rzeczy ze względu na wydajność wykonania.

Dokumentacja oferuje trzy potencjalne sposoby wdrożenia wczesnego filtrowania:

Określenie warunku filtrowania w WHENklauzuli gwarantuje prawidłowe wyniki, ale może oznaczać, że więcej wierszy jest odczytywanych i przetwarzanych z tabel źródłowej i docelowej, niż jest to absolutnie konieczne (jak widać w pierwszym przykładzie).

Aktualizacja za pomocą widoku zawierającego warunek filtrowania gwarantuje również prawidłowe wyniki (ponieważ zmienione wiersze muszą być dostępne do aktualizacji za pośrednictwem widoku), ale wymaga to dedykowanego widoku i takiego, który spełnia nieparzyste warunki aktualizacji widoków.

Stosowanie wspólnego wyrażenia tabelowego niesie podobne ryzyko do dodawania predykatów do ONklauzuli, ale z nieco innych powodów. W wielu przypadkach będzie to bezpieczne, ale wymaga to specjalistycznej analizy planu wykonania, aby to potwierdzić (i obszerne testy praktyczne). Na przykład:

WITH TARGET AS 
(
    SELECT * 
    FROM @CategoryItem
    WHERE CategoryId = 2
)
MERGE INTO TARGET
USING 
(
    SELECT CategoryId, ItemId
    FROM @DataSource
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

Daje to prawidłowe wyniki (nie powtarzane) z bardziej optymalnym planem:

Plan odczytuje tylko wiersze dla kategorii 2 z tabeli docelowej. Może to być ważnym czynnikiem wpływającym na wydajność, jeśli tabela docelowa jest duża, ale zbyt łatwo jest to zrobić źle, używając MERGEskładni.

Czasami łatwiej jest pisać MERGEjako osobne operacje DML. Takie podejście może nawet działać lepiej niż jeden MERGE, co często zaskakuje ludzi.

DELETE ci
FROM @CategoryItem AS ci
WHERE ci.CategoryId = 2
AND NOT EXISTS 
(
    SELECT 1 
    FROM @DataSource AS ds 
    WHERE 
        ds.ItemId = ci.ItemId
        AND ds.CategoryId = ci.CategoryId
);

INSERT @CategoryItem
SELECT 
    ds.CategoryId, 
    ds.ItemId
FROM @DataSource AS ds
WHERE
    ds.CategoryId = 2;