Czy program SQL Server buforuje wynik funkcji o wartości wielu tablic?

Funkcja zawierająca wiele instrukcji o wartościach w tabeli zwraca wynik w zmiennej tabeli.

Czy wyniki te są kiedykolwiek ponownie wykorzystywane, czy też funkcja jest zawsze w pełni oceniana przy każdym wywołaniu?

Wyniki funkcji o wielu tabelach o wartości tabeli (msTVF) nigdy nie są buforowane ani ponownie wykorzystywane w instrukcjach (lub połączeniach), ale istnieje kilka sposobów ponownego wykorzystania wyniku msTVF w ramach tej samej instrukcji. W tym zakresie msTVF niekoniecznie jest ponownie wypełniany przy każdym wywołaniu.

Przykład msTVF

Ten (celowo nieefektywny) msTVF zwraca określony zakres liczb całkowitych ze znacznikiem czasu w każdym wierszu:

IF OBJECT_ID(N'dbo.IntegerRange', 'TF') IS NOT NULL
    DROP FUNCTION dbo.IntegerRange;
GO
CREATE FUNCTION dbo.IntegerRange (@From integer, @To integer)
RETURNS @T table 
(
    n integer PRIMARY KEY, 
    ts datetime DEFAULT CURRENT_TIMESTAMP
)
WITH SCHEMABINDING
AS
BEGIN
    WHILE @From <= @To
    BEGIN
        INSERT @T (n)
        VALUES (@From);

        SET @From = @From + 1;
    END;
    RETURN;
END;

Zmienna tabeli statycznej

Jeśli wszystkie parametry wywołania funkcji są stałymi (lub stałymi wykonawczymi), plan wykonania zapełni jeden raz wynik zmiennej tabeli. Pozostała część planu może uzyskiwać dostęp do zmiennej tabeli wiele razy. Statyczny charakter zmiennej tabeli można rozpoznać z planu wykonania. Na przykład:

SELECT
    IR.n,
    IR.ts 
FROM dbo.IntegerRange(1, 5) AS IR
ORDER BY
    IR.n;

Zwraca wynik podobny do:

Plan wykonania jest:

Operator Sequence najpierw wywołuje operator Table Valued Function, który zapełnia zmienną tabelową (zwróć uwagę, że ten operator nie zwraca żadnych wierszy). Następnie Sekwencja wywołuje drugie wejście, które zwraca zawartość zmiennej tabeli (w tym przypadku przy użyciu skanowania indeksu klastrowanego).

Podarunkiem, że plan wykorzystuje wynik „statycznej” zmiennej tabeli jest operator funkcji wycenionej tabeli poniżej sekwencji - zmienna tabeli musi zostać wypełniona jeden raz, zanim pozostała część planu będzie mogła zostać uruchomiona.

Wiele dostępów

Aby pokazać, że wynik zmiennej tabeli jest uzyskiwany więcej niż jeden raz, użyjemy drugiej tabeli z wierszami o numerach od 1 do 5:

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
    DROP TABLE dbo.T;

CREATE TABLE dbo.T (i integer NOT NULL);

INSERT dbo.T (i) 
VALUES (1), (2), (3), (4), (5);

I nowe zapytanie, które łączy tę tabelę z naszą funkcją (można to również zapisać jako APPLY):

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
JOIN dbo.IntegerRange(1, 5) AS IR
    ON IR.n = T.i;

Wynik to:

Plan wykonania:

Tak jak poprzednio, Sekwencja najpierw wypełnia wynik zmiennej msTVF tabeli. Następnie zagnieżdżone pętle są używane do łączenia każdego wiersza z tabeli Tdo wiersza z wyniku msTVF. Ponieważ definicja funkcji zawiera pomocny indeks zmiennej tabeli, można użyć wyszukiwania indeksu.

Kluczową kwestią jest to, że gdy parametry dla msTVF są stałymi (w tym zmiennymi i parametrami) lub są traktowane jako stałe środowiska wykonawczego dla instrukcji silnika wykonawczego, plan będzie zawierał dwa oddzielne operatory dla wyniku zmiennej tabeli msTVF: jeden do wypełnienia pola stół; inny, aby uzyskać dostęp do wyników, być może wielokrotnie uzyskując dostęp do tabeli i ewentualnie korzystając z indeksów zadeklarowanych w definicji funkcji.

Skorelowane parametry i parametry niestałe

Aby podkreślić różnice, gdy używane są parametry skorelowane (odwołania zewnętrzne) lub parametry funkcji stałych, zmienimy zawartość tabeli, Taby funkcja miała dużo więcej pracy:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i) 
VALUES (50001), (50002), (50003), (50004), (50005);

Następujące zmodyfikowane zapytanie używa teraz zewnętrznego odwołania do tabeli Tw jednym z parametrów funkcji:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

To zapytanie zajmuje około 8 sekund, aby zwrócić wyniki takie jak:

Zwróć uwagę na różnicę czasu między wierszami w kolumnie ts. WHEREPunkt ogranicza ostateczny wynik na wyjściu sen- rozmiarach, ale nieefektywne funkcje jeszcze trochę czasu, aby wypełnić zmiennej tabeli-50000 wierszy nieparzystych (w zależności od skorelowanych wartości iz tabeli T).

Plan wykonania jest:

Zwróć uwagę na brak operatora sekwencji. Obecnie istnieje jeden operator funkcji wycenionej w tabeli, który zapełnia zmienną tabeli i zwraca jej wiersze przy każdej iteracji łączenia zagnieżdżonych pętli.

Żeby było jasne: przy zaledwie 5 wierszach w tabeli T operator funkcji cenionej w tabeli działa 5 razy. Generuje 50,001 wierszy przy pierwszej iteracji, 50,002 przy drugiej ... i tak dalej. Zmienna tabeli jest „wyrzucana” (skracana) między iteracjami, więc każde z pięciu wywołań to pełna populacja. Dlatego jest tak wolny, a każdy wiersz pojawia się w tym samym czasie w wyniku.

Notatki dodatkowe:

Oczywiście powyższy scenariusz został celowo opracowany, aby pokazać, jak słaba może być wydajność, gdy msTVF zapełnia wiele wierszy podczas każdej iteracji.

Rozsądne wdrożenie powyższego kodu będzie ustawić oba parametry msTVF do ii usunąć zbędne WHEREklauzuli. Zmienna tabeli byłaby nadal obcinana i ponownie wypełniana przy każdej iteracji, ale tylko za jednym razem za każdym razem.

Możemy również pobrać wartości minimalne i maksymalne iz Ti przechowywać je w zmiennych w poprzednim kroku. Wywołanie funkcji ze zmiennymi zamiast skorelowanych parametrów pozwoliłoby na użycie wzorca zmiennej tabeli „statycznej”, jak wspomniano wcześniej.

Buforowanie niezmienionych skorelowanych parametrów

Wracając ponownie do pierwotnego pytania, w którym nie można użyć wzorca statycznego Sekwencji, SQL Server może uniknąć obcięcia i ponownego wypełnienia zmiennej tabeli msTVF, jeśli żaden z skorelowanych parametrów nie zmienił się od czasu poprzedniej iteracji łączenia zagnieżdżonej pętli.

Aby to wykazać, zastąpimy zawartość Tpięcioma identycznymi i wartościami:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i) 
VALUES (50005), (50005), (50005), (50005), (50005);

Zapytanie z korelowanym parametrem ponownie:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

Tym razem wyniki pojawiają się po około 1,5 sekundy :

Zwróć uwagę na identyczne znaczniki czasu w każdym wierszu. Wynik w pamięci podręcznej w zmiennej tabeli jest ponownie wykorzystywany do kolejnych iteracji, w których skorelowana wartość ipozostaje niezmieniona. Ponowne użycie wyniku jest znacznie szybsze niż wstawianie 50,005 wierszy za każdym razem.

Plan wykonania wygląda bardzo podobnie jak wcześniej:

Główna różnica polega na rzeczywiste ponowne powiązanie i Rzeczywiste REWINDS właściwości Tabela Ceniona operatora funkcji:

Gdy skorelowane parametry nie zmieniają się, SQL Server może odtwarzać (przewijać) bieżące wyniki w zmiennej tabeli. Gdy korelacja się zmienia, SQL Server musi obciąć i ponownie wypełnić zmienną tabelową (ponowne powiązanie). Jedno ponowne wiązanie ma miejsce przy pierwszej iteracji; cztery kolejne iteracje są przewijane do tyłu, ponieważ wartość parametru T.ijest niezmieniona.