Ostatecznie nie można zmusić SQL Server do oceny skalarnej UDF tylko raz w zapytaniu. Można jednak podjąć pewne kroki, aby go zachęcić. Dzięki testom uważam, że można uzyskać coś, co działa z bieżącą wersją SQL Server, ale możliwe jest, że przyszłe zmiany będą wymagać ponownego sprawdzenia kodu.
Jeśli możliwa jest edycja kodu, dobrym pomysłem jest uczynienie funkcji deterministyczną, jeśli to możliwe. Paul White wskazuje tutaj, że funkcja musi zostać utworzona z SCHEMABINDINGopcją, a sam kod funkcji musi być deterministyczny.
Po wprowadzeniu następującej zmiany:
CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @tbl TABLE (VAL VARCHAR(5));
-- make the function expensive to call
INSERT INTO @tbl
SELECT [VALUE]
FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');
RETURN 1;
END;
Zapytanie z pytania jest wykonywane w 64 ms:
SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();
W planie zapytań nie ma już operatora filtru:
Aby mieć pewność, że wykonano go tylko raz, możemy użyć nowego DMV sys.dm_exec_function_stats wydanego w SQL Server 2016:
SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('EXPENSIVE_UDF', 'FN');
Wystawienie funkcji ALTERprzeciw spowoduje zresetowanie execution_countdla tego obiektu. Powyższe zapytanie zwraca 1, co oznacza, że funkcja została wykonana tylko raz.
Zauważ, że tylko dlatego, że funkcja jest deterministyczna, nie oznacza, że będzie oceniana tylko raz dla każdego zapytania. W rzeczywistości w przypadku niektórych zapytań dodanie SCHEMABINDINGmoże obniżyć wydajność. Rozważ następujące zapytanie:
WITH cte (UDF_VALUE) AS
(
SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;
Nadmiar DISTINCTzostał dodany, aby pozbyć się operatora filtru. Plan wygląda obiecująco:
Na tej podstawie można oczekiwać, że UDF zostanie raz oceniony i użyty jako tabela zewnętrzna w łączeniu w zagnieżdżonej pętli. Jednak uruchomienie zapytania na moim komputerze zajmuje 6446 ms. Zgodnie sys.dm_exec_function_statsz funkcją została wykonana 100 razy. Jak to możliwe? W White Compal Scalars, Expressions and Execution Plan Performance Paul White wskazuje, że operator Compute Scalar można odroczyć:
Skalar obliczeniowy najczęściej definiuje wyrażenie; faktyczne obliczenia są odraczane do momentu, gdy coś później w planie wykonania będzie wymagało wyniku.
W przypadku tego zapytania wygląda na to, że wywołanie UDF zostało odroczone do momentu, gdy było potrzebne, w którym to momencie zostało ocenione 100 razy.
Co ciekawe, przykład CTE jest wykonywany w 71 ms na mojej maszynie, gdy UDF nie jest zdefiniowany za pomocą SCHEMABINDING, jak w pierwotnym pytaniu. Funkcja jest wykonywana tylko raz po uruchomieniu zapytania. Oto plan zapytań:
Nie jest jasne, dlaczego skalar obliczeniowy nie jest odraczany. Może być tak, ponieważ niedeterminizm funkcji ogranicza rearanżację operatorów, co może zrobić optymalizator zapytań.
Alternatywnym podejściem jest dodanie małej tabeli do CTE i wysłanie zapytania do jedynego wiersza w tej tabeli. Zrobi to każdy mały stolik, ale użyjmy następujących:
CREATE TABLE dbo.X_ONE_ROW_TABLE (ID INT NOT NULL);
INSERT INTO dbo.X_ONE_ROW_TABLE VALUES (1);
Zapytanie staje się następnie:
WITH cte (UDF_VALUE) AS
(
SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
FROM dbo.X_ONE_ROW_TABLE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;
Dodanie dbo.X_ONE_ROW_TABLEniepewności zwiększa optymalizator. Jeśli tabela ma zero wierszy, CTE zwróci 0 wierszy. W każdym razie optymalizator nie może zagwarantować, że CTE zwróci jeden wiersz, jeśli UDF nie jest deterministyczny, więc wydaje się prawdopodobne, że UDF zostanie oceniony przed złączeniem. Oczekiwałbym, że optymalizator skanuje dbo.X_ONE_ROW_TABLE, użyje agregatu strumienia, aby uzyskać maksymalną wartość zwróconego jednego wiersza (co wymaga oceny funkcji), i użyje go jako zewnętrznej tabeli dla sprzężonej pętli zagnieżdżonej dbo.X_100_INTEGERSw głównym zapytaniu . Wydaje się, że tak się dzieje :
Zapytanie wykonuje się na moim komputerze w około 110 ms, a UDF jest oceniany tylko raz, zgodnie z sys.dm_exec_function_stats. Błędem byłoby twierdzenie, że optymalizator zapytań jest zmuszony oceniać UDF tylko raz. Trudno jednak wyobrazić sobie przepisanie optymalizatora, które prowadziłoby do zapytania o niższych kosztach, nawet przy ograniczeniach dotyczących UDF i obliczania kosztów skalarnych.
Podsumowując, dla funkcji deterministycznych (które muszą zawierać SCHEMABINDINGopcję) spróbuj napisać zapytanie w możliwie najprostszy sposób. Jeśli na SQL Server 2016 lub nowszej wersji, potwierdź, że funkcja została wykonana tylko raz przy użyciu sys.dm_exec_function_stats. Plany wykonania mogą w tym względzie wprowadzać w błąd.
Aby funkcje, które nie są uważane przez SQL Server za deterministyczne, w tym wszystko bez tej SCHEMABINDINGopcji, jednym z podejść jest umieszczenie UDF w starannie spreparowanym CTE lub tabeli pochodnej. Wymaga to nieco uwagi, ale ta sama CTE może działać zarówno dla funkcji deterministycznych, jak i niedeterministycznych.
