Buforowanie stałego skanowania

Mam stół z kilkadziesiąt rzędami. Poniżej przedstawiono uproszczoną konfigurację

CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

Mam zapytanie, które łączy tę tabelę z zestawem wierszy zbudowanych z wartości tabeli (wykonanych ze zmiennych i stałych)

DECLARE @id1 int = 101, @id2 int = 105;

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (@id1, 'A'),
        (@id2, 'B')
    ) p([Id], [Code])
    FULL JOIN #data d ON d.[Id] = p.[Id];

Plan wykonania zapytania pokazuje, że decyzja optymalizatora polega na zastosowaniu FULL LOOP JOINstrategii, co wydaje się właściwe, ponieważ oba dane wejściowe mają bardzo mało wierszy. Zauważyłem jednak (i ​​nie mogę się zgodzić), że wiersze TVC są buforowane (patrz obszar planu wykonania w czerwonym polu).

Dlaczego optymalizator wprowadza tutaj szpulę, jaki jest tego powód? Poza szpulą nie ma nic skomplikowanego. Wygląda na to, że nie jest to konieczne. Jak się go pozbyć w tym przypadku, jakie są możliwe sposoby?

Powyższy plan uzyskano dnia

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)

Dlaczego optymalizator wprowadza tutaj szpulę, jaki jest tego powód? Poza szpulą nie ma nic skomplikowanego.

Rzecz poza szpulą nie jest prostym odniesieniem do tabeli, którą można po prostu zduplikować, gdy zostanie wygenerowana alternatywa łączenia z lewej / anty pół-łączenia .

Może to wyglądać trochę jak tabela (skanowanie ciągłe), ale dla optymalizatora * jest to jeden UNION ALLz oddzielnych wierszy w VALUESklauzuli.

Dodatkowa złożoność wystarcza, aby optymalizator wybrał buforowanie i odtwarzanie wierszy źródłowych, a nie zastąpienie szpuli prostym poleceniem „pobierz tabelę” później. Na przykład początkowa transformacja z pełnego złączenia wygląda następująco:

Zwróć uwagę na dodatkowe szpule wprowadzone przez transformację ogólną. Szpule powyżej prostej tabeli są czyszczone później przez regułę SpoolGetToGet.

Jeśli optymalizator miał odpowiednią SpoolConstGetToConstGetregułę, w zasadzie mógłby działać tak, jak chcesz.

Jak się go pozbyć w tym przypadku, jakie są możliwe sposoby?

Użyj prawdziwej tabeli (tymczasowej lub zmiennej) lub napisz ręcznie transformację z pełnego łączenia, na przykład:

WITH 
    p([Id], [Code]) AS
    (
        SELECT @id1, 'A'
        UNION ALL
        SELECT @id2, 'B'
    ),
    FullJoin AS
    (
        SELECT
            p.Code,
            d.[Status]
        FROM p
        LEFT JOIN #data d 
            ON d.[Id] = p.[Id]
        UNION ALL
        SELECT
            NULL,
            D.[Status]
        FROM #data AS D
        WHERE NOT EXISTS
        (
            SELECT *
            FROM p
            WHERE p.Id = D.Id
        )
    )
SELECT
    COALESCE(FullJoin.Code, 'X') AS Code,
    COALESCE(FullJoin.Status, 0) AS [Status]
FROM FullJoin;

Zaplanuj ręczne przepisywanie:

Szacowany koszt to 0,0067201 sztuk, w porównaniu z 0,0203412 sztuk dla oryginału.

* Można to zaobserwować jako LogOp_UnionAllw konwertowanym drzewie (TF 8605). W drzewie wprowadzania (TF 8606) jest to LogOp_ConstTableGet. W Skonwertowane drzewa przedstawia drzewo elementów ekspresyjnych optymalizator po analizowania, normalizacja, algebrization, wiązania i innej pracy przygotowawczej. Wejście Drzewo przedstawia elementy po przeliczeniu na negacji Normal Form (NNF konwersji), stałą wykonawczego zawaleniem, a kilka innych drobiazgi. Konwersja NNF obejmuje logikę, aby zwinąć logiczne związki, a wspólna tabela dostaje, między innymi.

Szpula po prostu tworzy tabelę z dwóch zestawów krotek obecnych w VALUESklauzuli.

Możesz wyeliminować bufor, najpierw wstawiając te wartości do tabeli tymczasowej:

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

DROP TABLE IF EXISTS #p;
CREATE TABLE #p
(
    Id int NOT NULL
    , Code char(1) NOT NULL
);

DECLARE @id1 int = 101, @id2 int = 105;

INSERT INTO #p (Id, Code)
VALUES
        (@id1, 'A'),
        (@id2, 'B');


SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM #p p
    FULL JOIN #data d ON d.[Id] = p.[Id];

Patrząc na plan wykonania zapytania, widzimy, że lista wyjściowa zawiera dwie kolumny, które używają Unionprzedrostka; jest to wskazówka, że ​​szpula tworzy tabelę ze źródła unii:

FULL OUTER JOINWymaga SQL Server, aby uzyskać dostęp do wartości pdwukrotnie, raz dla każdej „stronie” przyłączyć. Utworzenie buforu pozwala wynikowym wewnętrznym pętlom połączyć się z buforowanymi danymi.

Co ciekawe, jeśli zastąpi FULL OUTER JOINz LEFT JOINa RIGHT JOINi UNIONwynikach razem, SQL Server nie używa szpulę.

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (101, 'A'),
        (105, 'B')
    ) p([Id], [Code])
    LEFT JOIN #data d ON d.[Id] = p.[Id]
UNION
SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (101, 'A'),
        (105, 'B')
    ) p([Id], [Code])
    RIGHT JOIN #data d ON d.[Id] = p.[Id];

Uwaga: nie sugeruję używania UNIONpowyższego zapytania; w przypadku większych zestawów danych wejściowych może nie być bardziej wydajny niż ten, FULL OUTER JOINktóry już masz.