Sdílet prostřednictvím

Vložení skalárních uživatelsky definovaných funkcí

  • Článek

platí pro: SQL Server 2019 (15.x) Azure SQL Databaseazure SQL Managed Instance

Tento článek představuje inlinování skalárních UDF, což je funkce v rámci sady funkcí Inteligentní zpracování dotazů v databázích SQL. Tato funkce zlepšuje výkon dotazů, které volají uživatelsky definovanou skalární funkci v SQL Serveru 2019 (15.x) a novějších verzích.

Skalární funkce definované uživatelem jazyka T-SQL

User-Defined Functions (UDF), které jsou implementovány v Transact-SQL a vrací jednu datovou hodnotu, se označují jako skalární User-Defined funkce T-SQL. UDF T-SQL představují elegantní způsob, jak dosáhnout opakovaného použití kódu a modularity napříč dotazy Transact-SQL. Některé výpočty (například složitá obchodní pravidla) se snadněji vyjadřují v imperativní podobě UDF (uživatelsky definovaná funkce). Funkce definované uživatelem pomáhají při vytváření složitějších logických struktur, aniž by bylo nutné mít odborné znalosti pro psaní komplexních SQL dotazů. Další informace o uživatelsky definovaných funkcích najdete v tématu Vytvoření uživatelsky definovaných funkcí (databázový stroj).

Výkon skalárních funkcí definovaných uživatelem

Skalární uživatelsky definované funkce obvykle mají špatný výkon z následujících důvodů:

  • Iterativní vyvolání. Funkce definované uživatelem se vyvolávají iterativním způsobem, jednou za způsobilou n-tici. To způsobuje dodatečné náklady na opakované přepínání kontextu kvůli vyvolání funkce. Zejména uživatelem definované funkce, které ve své definici provádějí dotazy Transact-SQL, jsou silně ovlivněné.

  • Nedostatek kalkulací nákladů. Během optimalizace jsou náklady vypočítávány pouze pro relační operátory, zatímco skalární operátory nejsou zpoplatněny. Před zavedením skalárních UDF byly ostatní skalární operátory obecně levné a nevyžadují náklady. Malý náklad na procesor pro skalární operaci stačil. Existují scénáře, ve kterých jsou skutečné náklady významné a přesto zůstávají nedostatečně reprezentované.

  • Interpretované spuštění. Funkce definované uživatelem se vyhodnocují jako dávka příkazů spouštěných příkazem po příkazu. Každý samotný příkaz je zkompilován a zkompilovaný plán se ukládá do mezipaměti. I když tato strategie ukládání do mezipaměti šetří čas, protože se vyhne rekompilace, každý příkaz se provádí izolovaně. Neprovádějí se žádné optimalizace mezi příkazy.

  • Sériové spuštění. SQL Server neumožňuje paralelní zpracování v dotazech, které volají uživatelsky definované funkce.

Automatické vkládání uživatelsky definovaných skalárních funkcí

Cílem funkce pro vložení skalárních UDF je zlepšit výkon dotazů, které volají skalární UDF v T-SQL, kde je provádění UDF hlavním kritickým bodem.

Díky této nové funkci se skalární funkce definované uživatelem automaticky transformují na skalární výrazy nebo skalární poddotazy, které jsou nahrazeny ve volajícím dotazu místo operátoru UDF. Tyto výrazy a poddotazy se pak optimalizují. V důsledku toho už plán dotazu nemá operátor uživatelem definované funkce, ale jeho účinky jsou v plánu pozorovány, jako jsou zobrazení nebo vložené funkce s hodnotami tabulky (TVF).

Příklady

Příklady v této části používají databázi srovnávacích testů TPC-H. Další informace naleznete na TPC-H domovské stránce.

A. Skalární UDF s jedním příkazem

Zvažte následující dotaz.

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

Tento dotaz vypočítá součet zvýhodněných cen pro řádkové položky a zobrazí výsledky seskupené podle data expedice a priority expedice. Výraz L_EXTENDEDPRICE *(1 - L_DISCOUNT) je vzorec pro zlevněnou cenu dané řádkové položky. Tyto vzorce lze extrahovat do funkcí, které mají výhodu modularity a opětovného použití.

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

Nyní je možné dotaz upravit tak, aby vyvolal tuto uživatelsky definovanou funkci.

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

Dotaz s uživatelem definovanou funkcí funguje špatně, a to z důvodů popsaných dříve. Při inlinování skalární UDF se skalární výraz v jejím těle přímo nahradí v dotazu. Výsledky spuštění tohoto dotazu jsou uvedené v následující tabulce:

Dotaz: Dotaz bez funkce definované uživatelem Dotazování pomocí UDF (bez vkládání) Použití dotazu s vložením skalární funkce definované uživatelem
Doba vykonávání 1,6 sekundy 29 minut 11 sekund 1,6 sekundy

Tato čísla jsou založená na databázi CCI o velikosti 10 GB (pomocí schématu TPC-H), která běží na počítači se dvěma procesory (12 jádry), 96 GB paměti RAM s podporou SSD. Tato čísla zahrnují dobu kompilace a spuštění s mezipamětí studené procedury a fondem vyrovnávací paměti. Použili jsme výchozí konfiguraci a nevytvořili se žádné další indexy.

B. Vícepříkazový skalární UDF

Skalární uživatelsky definované funkce implementované pomocí více příkazů T-SQL, jako jsou přiřazení proměnných a podmíněné větvení, mohou být také vloženy přímo do kódu. Zvažte následující skalární uživatelsky definovanou funkci, která vzhledem k zadanému klíči zákazníka určuje kategorii služeb. Dorazí do kategorie tak, že nejprve vy computinge celkovou cenu všech objednávek zadaných zákazníkem pomocí dotazu SQL. Pak použije logiku IF (...) ELSE k rozhodnutí kategorie na základě celkové ceny.

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS 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

Teď zvažte dotaz, který vyvolá tuto uživatelsky definovanou funkci.

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

Plán provádění pro tento dotaz v SQL Serveru 2017 (14.x) (úroveň kompatibility 140 a starší) je následující:

snímek obrazovky s plánem dotazů bez vložení

Jak ukazuje plán, SQL Server zde přijímá jednoduchou strategii: pro každý řádek v tabulce CUSTOMER je zavolána UDF a jsou vypsány výsledky. Tato strategie je naivní a neefektivní. Při inlinování se tyto uživatelem definované funkce transformují na ekvivalentní skalární poddotazy, které se nahrazují ve volajícím dotazu místo uživatelem definované funkce.

Pro stejný dotaz vypadá plán s vloženou uživatelsky definovanou funkcí následujícím způsobem.

Snímek obrazovky s plánem dotazu s vložením.

Jak už bylo zmíněno dříve, plán dotazu už nemá operátor uživatelem definované funkce, ale jeho účinky jsou nyní pozorovatelné v plánu, jako jsou zobrazení nebo vložené TVF. Tady je několik klíčových pozorování z předchozího plánu:

  • SQL Server odvodí implicitní spojení mezi CUSTOMER a ORDERS a explicitně ho provede operátorem spojení.

  • SQL Server také odvodí implicitní GROUP BY O_CUSTKEY on ORDERS a k implementaci používá IndexSpool + StreamAggregate.

  • SQL Server teď používá paralelismus napříč všemi operátory.

V závislosti na složitosti logiky v UDF se výsledný plán dotazů může také zvětšit a být složitější. Jak vidíme, operace uvnitř uživatelem definované funkce už nejsou neprůhledné, takže optimalizátor dotazů dokáže tyto operace ohodnotit a optimalizovat. Navíc vzhledem k tomu, že UDF již není v plánu, je iterativní vyvolání UDF nahrazeno plánem, který zcela eliminuje režii spojovanou s voláním funkce.

Požadavky na inlinovatelné skalární uživatelem definované funkce

Skalární funkce T-SQL UDF může být inlinována, pokud definice funkce používá povolené konstrukty a tato funkce se používá v kontextu, který umožňuje inlinování.

Všechny následující podmínky definice definované uživatelem musí být splněny:

  • Funkce definovaná uživatelem (UDF) se zapisuje pomocí následujících konstrukcí:
    • DECLARE, SET: Deklarace proměnných a přiřazení.
    • SELECT: Dotaz SQL s přiřazením jedné nebo více proměnných 1.
    • IF / ELSE: Větvení s libovolnými úrovněmi vnoření
    • RETURN: Jeden nebo více návratových příkazů. Počínaje SQL Serverem 2019 (15.x) CU5 může UDF obsahovat pouze jeden příkaz RETURN, aby byl považován za způsobilý pro vkládání 6.
    • UDF: Vnořená nebo rekurzivní volání funkcí 2.
    • Jiné: Relační operace, jako jsou EXISTS, IS NULL.
  • Funkce definovaná uživatelem (UDF) nevolá žádnou vnitřní funkci, která je závislá na čase (například GETDATE()) nebo má vedlejší účinky (například , #B2,, například NEWSEQUENTIALID()).
  • Funkce definovaná uživatelem používá podmínku EXECUTE AS CALLER (výchozí chování, pokud není zadána podmínka EXECUTE AS).
  • UDF neodkazuje na proměnné tabulky ani parametry s hodnotou tabulky.
  • UDF (funkce definovaná uživatelem) není nativně kompilovaná (podporováno je propojení).
  • UDF neodkazuje na uživatelem definované typy.
  • Do 9UDF nejsou přidány žádné podpisy.
  • UDF není partitionovací funkce.
  • Uživatelsky definovaná funkce (UDF) neobsahuje odkazy na běžné tabulkové výrazy (CTE).
  • Uživatelsky definovaná funkce neobsahuje odkazy na vnitřní funkce, které by mohly změnit výsledky při vkládání (například @@ROWCOUNT) 4.
  • UDF neobsahuje agregační funkce, které jsou předávány jako parametry skalární UDF 4.
  • UDF neodkazuje na předdefinovaná zobrazení (například OBJECT_ID) 4.
  • UDF neodkazuje na metody XML 5.
  • UDF neobsahuje SELECT s ORDER BY bez klauzule TOP 15.
  • UDF neobsahuje dotaz SELECT, který provádí přiřazení pomocí klauzule ORDER BY (například SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
  • UDF neobsahuje více příkazů RETURN 6.
  • UDF neodkazuje na funkci STRING_AGG6.
  • UDF neodkazuje na vzdálené tabulky 7.
  • UDF neodkazuje na šifrované sloupce 8.
  • Funkce UDF neobsahuje odkazy na WITH XMLNAMESPACES8.
  • Pokud funkce definovaná uživatelem má tisíce řádků kódu, SQL Server se může rozhodnout, že ji nepoužije přímo.

1SELECT s proměnlivou akumulací/agregací není podporováno pro vložení (například SELECT @val += col1 FROM table1).

2 Rekurzivní uživatelsky definované funkce jsou vloženy jen do určité hloubky.

3 Vnitřní funkce, jejichž výsledky závisí na aktuálním systémovém čase, jsou závislé na čase. Vnitřní funkce, která může aktualizovat nějaký vnitřní globální stav, je příkladem funkce s vedlejšími účinky. Tyto funkce vracejí různé výsledky pokaždé, když jsou volány, na základě interního stavu.

V SQL Serveru 2019 (15.x) CU 2 bylo přidáno omezení 4

5 omezení přidané v SQL Serveru 2019 (15.x) CU 4

6 omezení přidané v SQL Serveru 2019 (15.x) CU 5

7 Omezení přidané v SQL Serveru 2019 (15.x) CU 6

8 Omezení přidané v SQL Serveru 2019 (15.x) CU 11

9 Vzhledem k tomu, že po vytvoření uživatelské funkce mohou být přidány a odebrány parametry, rozhodnutí o vložení je učiněno při kompilaci dotazu, který se odkazuje na tuto skalární uživatelskou funkci. Systémové funkce jsou například obvykle podepsané certifikátem. Pomocí sys.crypt_properties můžete zjistit, které objekty jsou podepsané.

Musí platit všechny následující požadavky kontextu spuštění :

  • UDF se v klauzuli ORDER BY nepoužívá.
  • Dotaz, který volá skalární UDF, neodkazuje na skalární volání UDF v jeho klauzuli GROUP BY.
  • Dotaz, který ve výběrovém seznamu vyvolává skalární UDF s klauzulí DISTINCT, nemá klauzuli ORDER BY.
  • Funkce definovaná uživatelem (UDF) není volána z příkazu RETURN 1.
  • Dotaz, který vyvolá UDF, nemá běžné tabulkové výrazy (CTEs) 3.
  • UDF volající dotaz nepoužívá GROUPING SETS, CUBEani ROLLUP2.
  • UDF volající dotaz neobsahuje proměnnou, která se používá jako parametr UDF pro přiřazení (například SELECT @y = 2, @x = UDF(@y)) 2.
  • Funkce definovaná uživatelem (UDF) se nepoužívá ve vypočítaném sloupci ani v definici kontrolního omezení.

1 Omezení přidané v SQL Serveru 2019 (15.x) CU 5

2 Omezení přidané v SQL Serveru 2019 (15.x) CU 6

3 omezení přidané v SQL Serveru 2019 (15.x) CU 11

Informace o nejnovějších opravách inlinování skalárních UDF T-SQL a změnách scénářů způsobilosti pro inlinování najdete v článku znalostní báze Knowledge Base: OPRAVA: problémy s inlinováním skalárních UDF v SQL Serveru 2019.

Zkontrolujte, zda lze uživatelsky definovanou funkci vložit

Zobrazení katalogu sys.sql_modules obsahuje pro každý T-SQL skalární UDF vlastnost s názvem is_inlineable, která označuje, zda je UDF inlinovatelný.

Vlastnost is_inlineable je odvozena z konstruktorů nalezených v definici UDF. Nekontroluje, jestli je uživatelsky definovaná funkce skutečně inlinovatelná v době kompilace. Pro další informace si přečtěte podmínky vkládání v.

Hodnota 1 označuje, že funkce definovaná uživatelem je vložená, a 0 označuje jinak. Tato vlastnost má také hodnotu 1 pro všechny vložené TVFs. Pro všechny ostatní moduly je hodnota 0.

Pokud je skalární UDF vhodný pro vložení, neznamená to, že je vždy vložen. SQL Server rozhoduje (podle jednotlivých dotazů a na základě jednotlivých uživatelsky definovaných funkcí), zda se má funkce vložit inline. Projděte si seznamy požadavků uvedených výše v tomto článku.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Zkontrolujte, zda došlo k inliningu.

Pokud jsou splněny všechny předpoklady a SQL Server se rozhodne provést inlining, transformuje UDF na relační výraz. V plánu dotazu můžete zjistit, jestli došlo k vložení:

  • Kód XML plánu nemá uzel XML <UserDefinedFunction> pro uživatelem definovanou funkci, která je úspěšně vložená.
  • Některé rozšířené události se vysílají.

Povolení inliningu skalárního definovaného uživatelem

Úlohy můžete automaticky nastavit tak, aby byly pro skalární vkládání definované uživatelem způsobilé, a to povolením úrovně kompatibility 150 pro databázi. Můžete to nastavit pomocí jazyka Transact-SQL. Například:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Kromě tohoto kroku nejsou potřeba žádné další změny, které by se měly provádět v uživatelem definovaných funkcích nebo dotazech, aby bylo možné tuto funkci využít.

Zakázání inliningu skalárního UDF beze změny úrovně kompatibility

Skalární vkládání uživatelsky definované funkce je možné zakázat na úrovni databáze, příkazu nebo oboru funkce, přičemž je zachována úroveň kompatibility databáze 150 a vyšší. Pokud chcete zakázat inlining skalárních UDF v rámci databáze, spusťte následující příkaz v kontextu příslušné databáze:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Pokud chcete pro databázi znovu povolit inlining skalárních UDF (definovaných uživatelem), spusťte v kontextu příslušné databáze následující příkaz:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Při ONse toto nastavení zobrazí jako povolené v sys.database_scoped_configurations.

Skalární vkládání UDF pro konkrétní dotaz můžete také zakázat tak, že DISABLE_TSQL_SCALAR_UDF_INLINING označíte jako nápovědu k dotazu USE HINT.

Tip dotazu USE HINT má přednost před konfigurací v rámci databáze nebo nastavením úrovně kompatibility.

Například:

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

Vkládání skalárních UDF lze také zakázat pro konkrétní uživatelem definované funkce pomocí příkazu INLINE v příkazu CREATE FUNCTION nebo ALTER FUNCTION. Například:

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

Po provedení předchozího příkazu se tato funkce definovaná uživatelem (UDF) nikdy nevloží do žádného dotazu, který ji vyvolá. Pokud chcete pro tento UDF znovu povolit vkládání, spusťte následující příkaz:

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

Klauzule INLINE není povinná. Pokud není zadaná klauzule INLINE, nastaví se automaticky na ON/OFF na základě toho, jestli se dá UDF vkládat. Pokud je určen INLINE = ON, ale funkce definovaná uživatelem není způsobilá pro vložení, vyvolá se chyba.

Poznámky

Jak je popsáno v tomto článku, skalární UDF inlining transformuje dotaz s skalárními funkcemi definovanými uživatelem na dotaz s ekvivalentním skalárním poddotazem. Z důvodu této transformace si můžete všimnout některých rozdílů v chování v následujících scénářích:

  • Inlining vede k jiné hodnotě hash dotazu pro stejný text dotazu.

  • Některá upozornění v příkazech v UDF (například dělit nulou atd.), která mohou být dříve skrytá, se můžou zobrazit kvůli vkládání.

  • Nápovědy pro spojení na dotazové úrovni už nemusí být platné, protože inlining může zavádět nová spojení. Místo toho je nutné použít rady pro místní spojení.

  • Zobrazení, která odkazují na vložené skalární funkce definované uživatelem, nelze indexovat. Pokud potřebujete pro taková zobrazení vytvořit index, zakažte vkládání pro odkazované funkce definované uživatelem.

  • V chování dynamického maskování dat při vkládání uživatelsky definovaných funkcí může existovat určitý rozdíl.

    V určitých situacích (v závislosti na logice v UDF) může být vkládání konzervativnější s ohledem na maskování výstupních sloupců. Ve scénářích, kdy sloupce odkazované v UDF nejsou výstupní sloupce, nejsou maskované.

  • Pokud funkce definované uživatelem odkazují na předdefinované funkce, jako jsou SCOPE_IDENTITY(), @@ROWCOUNTnebo @@ERROR, změní se hodnota vrácená předdefinované funkce vložením. Tato změna chování je způsobena tím, že proces vložení změní rozsah definice příkazů uvnitř UDF. Počínaje SQL Serverem 2019 (15.x) CU2 je vkládání zablokováno, pokud UDF odkazuje na určité vnitřní funkce (například @@ROWCOUNT).

  • Pokud je proměnná přiřazena s výsledkem vložené uživatelem definované funkce a také se používá jako index_column_name v označení dotazu FORCESEEK, výsledkem je chyba 8622, která značí, že procesor dotazů nemohl vytvořit plán dotazu kvůli nápovědě definované v dotazu.

Související obsah