• 08/04/2020
• 12 minut do przeczytania
• • s
  • M
  • M
  • p
  • j
  • +6

Zastosowanie do: SQL Server (wszystkie obsługiwane wersje)

Ten artykuł przedstawia Scalar UDF Inlining, funkcję wchodzącą w skład pakietu funkcji Intelligent Query Processing. Funkcja ta poprawia wydajność zapytań, które wywołują skalarne UDF w SQL Server (począwszy od SQL Server 2019 (15.x)).

T-SQL skalarne funkcje zdefiniowane przez użytkownika

Funkcje zdefiniowane przez użytkownika (UDF), które są zaimplementowane w Transact-SQL i zwracają pojedynczą wartość danych, są określane jako T-SQL skalarne funkcje zdefiniowane przez użytkownika. Funkcje UDF T-SQL są eleganckim sposobem na osiągnięcie ponownego użycia kodu i modularności w zapytaniach Transact-SQL. Niektóre obliczenia (takie jak złożone reguły biznesowe) są łatwiejsze do wyrażenia w imperatywnej formie UDF. UDF pomagają w budowaniu złożonej logiki bez konieczności posiadania specjalistycznej wiedzy w zakresie pisania skomplikowanych zapytań SQL. Aby uzyskać więcej informacji na temat UDF, zobacz Tworzenie funkcji zdefiniowanych przez użytkownika (Database Engine).

Wydajność skalarnych UDF

Skalarne UDF zazwyczaj mają niską wydajność z następujących powodów:

• Iteracyjne wywoływanie: UDFy są wywoływane w sposób iteracyjny, raz na każdą kwalifikującą się krotkę. Powoduje to dodatkowe koszty związane z wielokrotnym przełączaniem kontekstu z powodu wywoływania funkcji. Szczególnie dotkliwie odczuwają to FROM-y, które w swojej definicji wykonują zapytania Transact-SQL.

• Brak kalkulacji kosztów: Podczas optymalizacji kosztowane są tylko operatory relacyjne, natomiast operatory skalarne nie. Przed wprowadzeniem skalarnych FROM-ów, inne operatory skalarne były generalnie tanie i nie wymagały naliczania kosztów. Wystarczył niewielki koszt procesora dodany dla operacji skalarnej. Istnieją scenariusze, w których rzeczywisty koszt jest znaczący, a mimo to wciąż pozostaje niedoreprezentowany.

• Wykonanie interpretowane: FROM-y są oceniane jako partia instrukcji, wykonywanych instrukcja po instrukcji. Każda instrukcja jest kompilowana, a skompilowany plan jest buforowany. Chociaż ta strategia buforowania oszczędza trochę czasu, ponieważ pozwala uniknąć rekompilacji, każde wyrażenie wykonywane jest w izolacji. Nie są wykonywane żadne optymalizacje międzypowłokowe.

• Wykonanie seryjne: SQL Server nie zezwala na paralelizm wewnątrz zapytań w zapytaniach, które wywołują FROM.

Automatyczne inlinowanie skalarnych FROM

Celem funkcji inlinowania skalarnych FROM jest poprawa wydajności zapytań, które wywołują skalarne FROM T-SQL, gdzie wykonanie FROM jest głównym wąskim gardłem.

Definicje skalarne UDF są automatycznie przekształcane w wyrażenia skalarne lub podzapytania skalarne, które są zastępowane w wywołaniu zapytania w miejsce operatora UDF. Te wyrażenia i podzapytania są następnie optymalizowane. W rezultacie, plan zapytania nie będzie już posiadał operatora funkcji zdefiniowanej przez użytkownika, ale jego efekty będą widoczne w planie, tak jak widoki lub inline TVF.

Przykład 1 – Pojedyncze wyrażenie skalarne FROM

Rozważmy poniższe zapytanie.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE;

To zapytanie oblicza sumę zdyskontowanych cen dla pozycji linii i prezentuje wyniki pogrupowane według daty wysyłki i priorytetu wysyłki. Wyrażenie L_EXTENDEDPRICE *(1 - L_DISCOUNT) jest formułą na cenę rabatową dla danej pozycji asortymentowej. Takie formuły mogą być wyodrębnione do funkcji dla korzyści modularności i ponownego użycia.

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2)) RETURNS DECIMAL (12,2) ASBEGIN RETURN @price * (1 - @discount);END

Teraz zapytanie może zostać zmodyfikowane, aby wywołać ten UDF.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

Z powodów przedstawionych wcześniej, zapytanie z UDF działa słabo. Teraz, dzięki inliningowi skalarnego FROM, wyrażenie skalarne w ciele FROM jest zastępowane bezpośrednio w zapytaniu. Wyniki uruchomienia tego zapytania są pokazane w poniższej tabeli:

Zapytanie:	Zapytanie bez UDF	Zapytanie z UDF (bez inliningu)	Zapytanie z inliningiem skalarnego UDF
Czas wykonania:	1.6 sekund	29 minut 11 sekund	1,6 sekundy

Te liczby są oparte na 10-GB bazie danych CCI (przy użyciu schematu TPC-H), uruchomionej na maszynie z podwójnym procesorem (12 rdzeni), 96-GB RAM, wspartej przez SSD. Liczby obejmują czas kompilacji i wykonania z zimnym cache’m procedur i pulą buforów.

Przykład 2 – wielostanowiskowy skalarny UDF

Skalarne UDF, które są implementowane przy użyciu wielu instrukcji T-SQL, takich jak przypisywanie zmiennych i warunkowe rozgałęzienia, mogą być również inlined. Rozważmy poniższy skalarny UDF, który, biorąc pod uwagę klucz klienta, określa kategorię usług dla tego klienta. Aby określić kategorię, należy najpierw obliczyć całkowitą cenę wszystkich zamówień złożonych przez klienta za pomocą zapytania SQL. Następnie używa logiki IF (...) ELSE, aby zdecydować o kategorii na podstawie całkowitej ceny.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) RETURNS CHAR(10) ASBEGIN DECLARE @total_price DECIMAL(18,2); DECLARE @category CHAR(10); SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey; IF @total_price < 500000 SET @category = 'REGULAR'; ELSE IF @total_price < 1000000 SET @category = 'GOLD'; ELSE SET @category = 'PLATINUM'; RETURN @category;END

Rozważmy teraz zapytanie, które wywołuje ten FROM.

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

Plan wykonania tego zapytania w SQL Server 2017 (14.x) (poziom kompatybilności 140 i wcześniejsze) jest następujący:

Jak pokazuje plan, SQL Server przyjmuje tutaj prostą strategię: dla każdej krotki w tabeli CUSTOMER wywołaj UDF i wypisz wyniki. Ta strategia jest naiwna i nieefektywna. Dzięki inliningowi, takie UDF są przekształcane w równoważne podzapytania skalarne, które są zastępowane w wywołaniu zapytania w miejsce UDF.

Dla tego samego zapytania, plan z inlined UDF wygląda jak poniżej.

Jak wspomniałem wcześniej, plan zapytania nie posiada już operatora funkcji zdefiniowanej przez użytkownika, ale jego efekty są teraz obserwowalne w planie, tak jak widoki czy inline TVF. Oto kilka kluczowych spostrzeżeń z powyższego planu:

• SQL Server wywnioskował niejawne złączenie pomiędzy CUSTOMER i ORDERS i uczynił to jawnym poprzez operator złączenia.
• SerwerSQL wywnioskował również niejawny GROUP BY O_CUSTKEY on ORDERS i użył IndexSpool + StreamAggregate do jego implementacji.
• SerwerSQL używa teraz równoległości we wszystkich operatorach.

W zależności od złożoności logiki w UDF, wynikowy plan zapytania może również stać się większy i bardziej złożony. Jak widzimy, operacje wewnątrz UDF nie są już nieprzezroczyste, a co za tym idzie, optymalizator zapytań jest w stanie oszacować koszty i zoptymalizować te operacje. Ponadto, ponieważ UDF nie jest już w planie, iteracyjne wywoływanie UDF jest zastąpione przez plan, który całkowicie unika narzutu wywołań funkcji.

Wymagania dotyczące skalarnych UDF

Skalarny T-SQL UDF może być inlined, jeśli wszystkie poniższe warunki są prawdziwe:

• UDF jest napisany przy użyciu następujących konstrukcji:
  • DECLARESET: Deklaracja zmiennych i przypisanie zmiennych.
  • SELECT: Zapytanie SQL z przypisaniem jednej/wielu zmiennych 1.
  • IFELSE: Rozgałęzienia z dowolnymi poziomami zagnieżdżenia.
  • RETURN: Pojedyncze lub wielokrotne instrukcje return.
  • UDF: Zagnieżdżone/rekursywne wywołania funkcji 2.
  • Inne: Operacje relacyjne takie jak EXISTSISNULL.
• UDF nie wywołuje żadnej funkcji wewnętrznej, która jest zależna od czasu (np. GETDATE()) lub ma efekty uboczne 3 (np. NEWSEQUENTIALID()).
• UDF używa klauzuli EXECUTE AS CALLER (zachowanie domyślne, jeśli klauzula EXECUTE AS nie została określona).
• Funkcja FROM nie odwołuje się do zmiennych tabelarycznych ani parametrów wartości tabelarycznych.
• Zapytanie wywołujące skalarny FROM nie odwołuje się do skalarnego wywołania FROM w swojej klauzuli GROUP BY.
• Zapytanie wywołujące skalarny FROM w swojej liście select z klauzulą DISTINCT nie posiada klauzuli ORDER BY.
• Dane UDF nie są używane w ORDER BY clause.
• Dane UDF nie są natywnie skompilowane (interop jest obsługiwany).
• Dane UDF nie są używane w kolumnie obliczeniowej lub definicji ograniczenia sprawdzającego.
• DF nie odwołuje się do typów zdefiniowanych przez użytkownika.
• Do FROM nie są dodawane żadne sygnatury.
• DF nie jest funkcją partycji.
• Funkcja FROM nie zawiera odwołań do wspólnych wyrażeń tabelarycznych (CTE).
• Funkcja FROM nie zawiera odwołań do funkcji wewnętrznych, które mogą zmienić wyniki, gdy są inlined (takie jak @@ROWCOUNT) 4.
• UDF nie zawiera funkcji agregujących przekazywanych jako parametry do skalarnego UDF 4.
• UDF nie zawiera odwołań do wbudowanych widoków (takich jak OBJECT_ID) 4.
• UDF nie odwołuje się do metod XML 5.
• UDF nie zawiera SELECT z ORDER BY bez klauzuli TOP 1 5.
• UDF nie zawiera zapytania SELECT, które wykonuje przypisanie w połączeniu z klauzulą ORDER BY (taką jak SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• UDF nie zawiera wielu instrukcji RETURN 6.
• UDF nie jest wywoływany z instrukcji RETURN 6.
• UDF nie odwołuje się do funkcji STRING_AGG 6.
• Funkcja FROM nie odwołuje się do zdalnych tabel 7.
• Kwerenda wywołująca UDF nie używa GROUPING SETSCUBE, lub ROLLUP
• Zapytanie wywołujące UDF nie zawiera zmiennej, która jest używana jako parametr UDF do przypisania (na przykład SELECT @y = 2@x = UDF(@y))7.

1 SELECT ze zmienną akumulacją/agregacją nie jest obsługiwany do inliningu (np. SELECT @val += col1 FROM table1).

2 Rekursywne UDF będą inlinowane tylko do pewnej głębokości.

3 Funkcje wewnętrzne, których wyniki zależą od bieżącego czasu systemowego, są zależne od czasu. Funkcja wewnętrzna, która może aktualizować jakiś wewnętrzny stan globalny jest przykładem funkcji z efektami ubocznymi. Takie funkcje zwracają różne wyniki za każdym razem, gdy są wywoływane, w oparciu o stan wewnętrzny.

4 Ograniczenie dodane w SQL Server 2019 (15.x) CU2

5 Ograniczenie dodane w SQL Server 2019 (15.x) CU4

6 Ograniczenie dodane w SQL Server 2019 (15.x) CU5

7 Ograniczenie dodane w SQL Server 2019 (15.x) CU6

Sprawdzanie, czy dany UDF może być inlinowany

Dla każdego skalarnego UDF T-SQL, widok katalogu sys.sql_modules zawiera właściwość o nazwie is_inlineable, która wskazuje, czy UDF jest inlineable czy nie.

Wartość 1 wskazuje, że jest inlineable, a 0 oznacza inaczej. Ta właściwość będzie miała wartość 1 dla wszystkich inline TVF, jak również. Dla wszystkich innych modułów wartość ta będzie wynosić 0.

Jeśli skalarny FROM jest inline, nie oznacza to, że zawsze będzie inline. SQL Server zdecyduje (na podstawie zapytania i FROM) czy inline’ować FROM czy nie. Kilka przykładów, kiedy UDF może nie być inline’owany to:

• Jeżeli definicja UDF zajmuje tysiące linii kodu, SQL Server może nie zdecydować się na inline’owanie.

• Wywołanie UDF w klauzuli GROUP BY nie będzie inline’owane. Decyzja ta jest podejmowana podczas kompilacji zapytania odwołującego się do skalarnego FROM.

• Jeśli FROM jest podpisany certyfikatem. Ponieważ podpisy mogą być dodawane i usuwane po utworzeniu UDF, decyzja o tym, czy inline’ować czy nie, jest podejmowana podczas kompilacji zapytania odwołującego się do skalarnego UDF. Na przykład, funkcje systemowe są zazwyczaj podpisywane za pomocą certyfikatu. Można użyć sys.crypt_properties, aby dowiedzieć się, które obiekty są podpisane.

  SELECT * FROM sys.crypt_properties AS cpINNER JOIN sys.objects AS o ON cp.major_id = o.object_id;

Sprawdzenie, czy inlining nastąpił czy nie

Jeśli wszystkie warunki wstępne są spełnione i SQL Server zdecyduje się na inlining, przekształca FROM w wyrażenie relacyjne. Na podstawie planu zapytania można łatwo stwierdzić, czy inlining został przeprowadzony czy nie:

• Plan xml nie będzie posiadał węzła <UserDefinedFunction> xml dla FROM, który został pomyślnie inlinowany.
• Pewne XEvents są emitowane.

Enabling Scalar UDF Inlining

Możesz sprawić, że obciążenia będą automatycznie kwalifikowały się do Scalar UDF Inlining poprzez włączenie poziomu zgodności 150 dla bazy danych. Można to ustawić za pomocą Transact-SQL. Na przykład:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

Poza tym, nie ma potrzeby wprowadzania żadnych innych zmian w UDF-ach lub zapytaniach, aby skorzystać z tej funkcji.

Wyłączenie Scalar UDF Inlining bez zmiany poziomu kompatybilności

Inlining Scalar UDF może zostać wyłączony na poziomie bazy danych, instrukcji lub UDF-a, zachowując poziom kompatybilności 150 i wyższy. Aby wyłączyć skalarny inlining FROM na poziomie bazy danych, wykonaj poniższe polecenie w kontekście odpowiedniej bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Aby ponownie włączyć Scalar UDF Inlining dla bazy danych, wykonaj następujące polecenie w kontekście odpowiedniej bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Gdy ustawienie jest włączone, pojawi się jako włączone w sys.database_scoped_configurations.Możesz także wyłączyć Scalar UDF Inlining dla konkretnego zapytania poprzez oznaczenie DISABLE_TSQL_SCALAR_UDF_INLINING jako USE HINT podpowiedź do zapytania. Na przykład:

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATEOPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Podpowiedź

Podpowiedź do zapytania USE HINT ma pierwszeństwo przed konfiguracją dotyczącą zakresu bazy danych lub ustawieniami poziomu zgodności.

Scalar UDF Inlining może być również wyłączony dla konkretnego UDF za pomocą klauzuli INLINE w instrukcji CREATE FUNCTION lub ALTER FUNCTION.Na przykład:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))RETURNS DECIMAL (12,2)WITH INLINE = OFFASBEGIN RETURN @price * (1 - @discount);END;

Po wykonaniu powyższego polecenia, tenDF nie będzie już nigdy inlined w żadnym zapytaniu, które go wywołuje. Aby ponownie włączyć inlining dla tego FROM, wykonaj poniższe polecenie:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))RETURNS DECIMAL (12,2)WITH INLINE = ONASBEGIN RETURN @price * (1 - @discount);END

Ważne uwagi

Jak opisano w tym artykule, inlineding skalarnych FROM przekształca zapytanie ze skalarnymi FROM w zapytanie z równoważnym skalarnym podzapytaniem. Ze względu na tę transformację, użytkownicy mogą zauważyć pewne różnice w zachowaniu w następujących scenariuszach:

1. Inlining spowoduje, że zapytanie będzie miało inny hash dla tego samego tekstu zapytania.
2. Pewne ostrzeżenia w instrukcjach wewnątrz FROM (takie jak dzielenie przez zero itp.), które mogły być ukryte wcześniej, mogą się pojawić ze względu na inlining.
3. Podpowiedzi złączenia na poziomie zapytania mogą nie być już ważne, ponieważ inlining może wprowadzić nowe złączenia. Zamiast tego trzeba będzie użyć lokalnych podpowiedzi złączenia.
4. Widoki, które odwołują się do skalarnych FROMów inline nie mogą być indeksowane. Jeśli potrzebujesz utworzyć indeks na takich widokach, wyłącz inlining dla odwołujących się FROM.
5. Mogą wystąpić pewne różnice w zachowaniu maskowania danych dynamicznych z inliningiem FROM. W pewnych sytuacjach (w zależności od logiki FROM) inlining może być bardziej konserwatywny w stosunku do maskowania kolumn wyjściowych. W sytuacjach, w których kolumny, do których odwołuje się FROM nie są kolumnami wyjściowymi, nie będą one maskowane.
6. Jeśli FROM odwołuje się do wbudowanych funkcji takich jak SCOPE_IDENTITY()@@ROWCOUNT, lub @@ERROR, wartość zwracana przez wbudowaną funkcję zmieni się po inliningu. Ta zmiana zachowania wynika z tego, że inlining zmienia zakres instrukcji wewnątrz UDF. Począwszy od SQL Server 2019 (15.x) CU2, inlining jest blokowany, jeśli UDF odwołuje się do pewnych funkcji wbudowanych (na przykład @@ROWCOUNT).

See Also