Skaláris felhasználói függvény befűzése

A következőkre vonatkozik: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance SQL elemzési végpont a Microsoft Fabricben Tárház a Microsoft Fabricben SQL adatbázis a Microsoft Fabricben

Ez a cikk bemutatja a skaláris UDF-tagolást, amely az Intelligens lekérdezésfeldolgozás az SQL-adatbázisokban funkciók csomagjában található. Ez a funkció javítja az SQL Server 2019 (15.x) és újabb verzióiban skaláris UDF-eket meghívó lekérdezések teljesítményét.

T-SQL skaláris felhasználó által definiált függvények

User-Defined Transact-SQL-ben implementált és egyetlen adatértéket visszaadó függvényeket T-SQL Skaláris User-Defined Functionsnek nevezzük. A T-SQL UDF-ek elegáns módot jelentenek a kód újrafelhasználására és modularitására Transact-SQL lekérdezések között. Egyes számítások (például összetett üzleti szabályok) könnyebben kifejezhetők imperatív UDF-formában. Az UDF-ek segítenek összetett logika kialakításában anélkül, hogy szakértelemre van szükségük az összetett SQL-lekérdezések írásában. További információ az UDF-ekről: Felhasználó által definiált függvények (adatbázismotor) létrehozása.

Skaláris UDF-ek teljesítménye

A skaláris UDF-ek általában rosszul teljesítenek a következő okok miatt:

Iteratív meghívás. Az UDF-eket iteratív módon hívjuk meg, egy-egy feljogosító rekordonként. Ez a függvényhívás miatt ismétlődő környezetváltások többletköltséget okoznak. Különösen azok a UDF-ek vannak súlyosan érintve, amelyek a definíciójukban Transact-SQL lekérdezéseket hajtanak végre.
A költségszámítás hiánya. Az optimalizálás során csak a relációs operátorok kerülnek költségre, míg a skaláris operátorok nem. A skaláris UDF-ek bevezetése előtt más skaláris operátorok általában olcsók voltak, és nem igényeltek költségszámítást. A skaláris művelethez hozzáadott kis CPU-költség elegendő volt. Vannak olyan forgatókönyvek, amelyekben a tényleges költség jelentős, de továbbra is alulreprezentált marad.
Értelmezett végrehajtás. Az UDF-ek kiértékelése utasításkötegként történik, utasításonként végrehajtva. Minden utasítást külön-külön fordítanak le, és a fordítási terv gyorsítótárazva van. Bár ez a gyorsítótárazási eljárás időt takarít meg, mivel elkerüli az újrafordításokat, minden utasítás önállóan fut le. Nincs utasítások közötti optimalizálás.
Soros végrehajtás. Az SQL Server nem engedélyezi a lekérdezésen belüli párhuzamosságot az UDF-eket meghívó lekérdezésekben.

Automatikus skaláris UDF-ek beillesztése

A skaláris UDF-lining funkció célja a T-SQL skaláris UDF-eket meghívó lekérdezések teljesítményének javítása, ahol az UDF végrehajtása a fő szűk keresztmetszet.

Ezzel az új funkcióval a skaláris UDF-ek automatikusan skaláris kifejezésekké vagy skaláris alquerykké alakulnak, amelyeket a hívó lekérdezés helyettesít az UDF-operátor helyett. Ezek a kifejezések és al lekérdezések ezután optimalizálva lesznek. Ennek eredményeképpen a lekérdezési terv már nem rendelkezik felhasználó által definiált függvényoperátorsal, de a hatása a tervben figyelhető meg, például nézetek vagy beágyazott táblaértékű függvények (TVF-ek).

Skaláris UDF-ek automatikus felvázolása a Microsoft Fabric Data Warehouse-ban

A Microsoft Fabric Data Warehouse-ban a skaláris UDF-ek (jelenleg előzetes verzióban) automatikusan beágyazódnak a fordításkor, amikor a függvény törzse és a hívó lekérdezés megfelel a beágyazási követelményeknek. További információért lásd: CREATE FUNCTION és Skaláris UDF beágyazás.

Példák

Az ebben a szakaszban szereplő példák a TPC-H referenciaadatbázist használják. További információ: TPC-H Kezdőlap.

Egy. Egyetlen utasítás skaláris UDF

Fontolja meg a következő lekérdezést.

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;

Ez a lekérdezés kiszámítja a sorelemek kedvezményes árainak összegét, és megjeleníti az eredményeket a szállítási dátum és a szállítási prioritás szerint csoportosítva. A L_EXTENDEDPRICE *(1 - L_DISCOUNT) kifejezés egy adott sorelem kedvezményes árának képlete. Ezek a képletek a modularitás és az újrafelhasználás érdekében kinyerhetők függvényekké.

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

Most a lekérdezést úgy lehet módosítani, hogy az a UDF-et meghívja.

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;

Az UDF-et tartalmazó lekérdezés a korábban ismertetett okok miatt rosszul teljesít. Skaláris UDF-inlining esetén az UDF törzsében lévő skaláris kifejezés közvetlenül a lekérdezésben lesz helyettesítve. A lekérdezés futtatásának eredményei az alábbi táblázatban láthatók:

Lekérdezés:	Lekérdezés UDF nélkül	Lekérdezés UDF-fel (inlining nélkül)	Lekérdezés skaláris UDF-tagolással
`Execution time:`	1,6 másodperc	29 perc 11 másodperc	1,6 másodperc

Ezek a számok egy 10 GB-os CCI-adatbázison alapulnak (a TPC-H sémát használva), amely kettős processzorral (12 magos), 96 GB RAM-mal és SSD-vel támogatott gépen fut. A számok közé tartozik a fordítási és végrehajtási idő egy hideg eljárási gyorsítótárral és puffertárral. A rendszer az alapértelmezett konfigurációt használta, és nem hozott létre más indexeket.

B. Többutasításos skaláris UDF

A több T-SQL-utasítással implementált skaláris UDF-ek, például a változó-hozzárendelések és a feltételes elágaztatások is beágyazottak lehetnek. Vegye figyelembe az alábbi skaláris UDF-t, amely egy ügyfélkulcs alapján meghatározza az adott ügyfél szolgáltatáskategóriáját. A kategória úgy érkezik meg, hogy először egy SQL-lekérdezés használatával számítja ki az ügyfél által leadott megrendelések teljes árát. Ezután egy IF (...) ELSE logikával dönti el a kategóriát a teljes ár alapján.

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

Most fontolja meg azt a lekérdezést, amely meghívja ezt az UDF-et.

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

A lekérdezés végrehajtási terve az SQL Server 2017-ben (14.x) (140-es és korábbi kompatibilitási szint) a következő:

Ahogy a terv is mutatja, az SQL Server itt egy egyszerű stratégiát alkalmaz: a CUSTOMER tábla minden rekordjához hívja meg a UDF-et, és adja ki az eredményeket. Ez a stratégia naiv és nem hatékony. Az ilyen UDF-ek inlining alkalmazásával egyenértékű skaláris al-lekérdezésekké alakulnak át, amelyeket a hívó lekérdezés helyettesít az UDF helyett.

A szóban forgó lekérdezéshez az UDF beillesztésével a terv a következőképpen néz ki.

Ahogy korábban említettük, a lekérdezési terv már nem rendelkezik felhasználó által definiált függvényoperátorsal, de a hatása már megfigyelhető a tervben, például nézetekben vagy beágyazott TVF-ekben. Íme néhány fontos megfigyelés az előző tervből:

Az SQL Server a CUSTOMER és a ORDERS közötti implicit illesztésre következtet, és egy illesztő operátoron keresztül teszi explicitvé.
Az SQL Server az "implicit GROUP BY O_CUSTKEY on ORDERS"-t is következteti, és az IndexSpool + StreamAggregate használatával implementálja azt.
Az SQL Server mostantól minden operátoron párhuzamosságot használ.

Az UDF logikájának összetettségétől függően az eredményül kapott lekérdezési terv is nagyobb és összetettebb lehet. Amint látható, az UDF-ben lévő műveletek már nem átlátszatlanok, így a lekérdezésoptimalizáló képes a műveletek költségére és optimalizálására. Mivel az UDF már nem szerepel a tervben, az iteratív UDF-meghívást egy olyan terv váltja fel, amely teljesen elkerüli a függvényhívások többletterhelését.

Inline-olható skaláris UDF-követelmények

A skaláris T-SQL UDF beágyazott lehet, ha a függvénydefiníció engedélyezett szerkezeteket használ, és a függvényt olyan kontextusban használja, amely lehetővé teszi a beágyazottságot:

A UDF-definíciós összes alábbi feltételének igaznak kell lennie:

Az UDF a következő szerkezetekkel íródott:
- DECLARE, SET: Változók deklarációja és hozzárendelései.
- SELECT: SQL-lekérdezés egy-vagy többváltozós hozzárendeléssel ¹.
- IF / ELSE: Elágaztatás tetszőleges szintű beágyazással.
- RETURN: Egy vagy több visszatérési utasítás. SQL Server 2019-től (15.x) CU5-től kezdve az UDF csak egyetlen RETURN utasítást tartalmazhat, hogy figyelembe vehessék az inline feldolgozáshoz ⁶.
- UDF: Beágyazott/rekurzív függvényhívások ².
- Egyéb: Relációs műveletek, például EXISTS, IS NULL.
Az UDF nem hív meg olyan belső függvényt, amely időfüggő (például GETDATE()) vagy amelynek mellékhatásai vannak ³ (például NEWSEQUENTIALID()).
Az UDF a EXECUTE AS CALLER záradékot használja (alapértelmezett viselkedés, ha a EXECUTE AS záradék nincs megadva).
Az UDF nem hivatkozik táblaváltozókra vagy táblaértékű paraméterekre.
Az UDF nincs natívan lefordítva (az interop támogatott).
Az UDF nem hivatkozik felhasználó által definiált típusokra.
Nincs aláírás hozzáadva a UDF ⁹.
Az UDF nem partíciófüggvény.
Az UDF nem tartalmaz hivatkozásokat a közös táblázat kifejezésekre (CTE-k).
Az UDF nem tartalmaz olyan belső függvényekre mutató hivatkozásokat, amelyek megváltoztathatják az eredményeket, amikor beillesztettek (például @@ROWCOUNT), ⁴.
Az UDF nem tartalmazza a skaláris UDF-⁴paraméterként átadott összesítő függvényeket.
Az UDF nem hivatkozik a beépített nézetekre (például OBJECT_ID) ⁴.
Az UDF nem hivatkozik az 5 XML-metódusra.
Az UDF nem tartalmaz SELECT-et ORDER BY-val TOP 1 záradék nélkül ⁵.
Az UDF nem tartalmaz olyan SELECT lekérdezést, amely a ORDER BY záradékkal (például SELECT @x = @x + 1 FROM table1 ORDER BY col1) hozzárendelést végezne, ⁵.
Az UDF nem tartalmaz több RETURN utasítást ⁶.
Az UDF nem hivatkozik a 6 STRING_AGG függvényre.
Az UDF nem hivatkozik távoli táblákra ⁷.
Az UDF nem hivatkozik a 8 titkosított oszlopokra.
Az UDF nem tartalmaz hivatkozásokat WITH XMLNAMESPACES⁸.
Ha az UDF-definíció több ezer sorból áll, az SQL Server dönthet úgy, hogy nem inline-olja azt.

¹SELECT változófelhalmozással/aggregációval nem támogatott a formázás (például SELECT @val += col1 FROM table1).

² A rekurzív UDF-ek csak egy meghatározott mélységig lesznek beágyazva.

³ belső függvény, amelyek eredményei az aktuális rendszeridőtől függenek, időfüggők. Egy belső, globális állapotot frissítő belső függvény a mellékhatásokkal rendelkező függvények példája. Az ilyen függvények a belső állapottól függően minden alkalommal különböző eredményeket adnak vissza, amikor meghívják őket.

⁴ korlátozás hozzáadva az SQL Server 2019 CU 2-ben (15.x)

⁵ KORLÁTOZÁS hozzáadva az SQL Server 2019 -ben (15.x) CU 4

⁶ korlátozás hozzáadva az SQL Server 2019 -ben (15.x) CU 5

⁷ korlátozás hozzáadva az SQL Server 2019 -ben (15.x) CU 6

⁸ Korlátozás hozzáadva az SQL Server 2019 (15.x) CU 11-ben

⁹ Mivel az aláírások hozzáadhatók és elvethetők egy UDF létrehozása után, a skaláris UDF-re hivatkozó lekérdezés fordításakor történik meg a döntés, hogy inline kialakításra kerüljön-e. Például a rendszerfunkciók általában tanúsítvánnyal vannak aláírva. A sys.crypt_properties segítségével megkeresheti, hogy mely objektumok vannak aláírva.

A végrehajtási környezet alábbi követelményének teljesülnie kell:

Az UDF nincs használatban a ORDER BY záradékban.
A skaláris UDF-et invesztő lekérdezés nem hivatkozik skaláris UDF-hívásra a GROUP BY záradékában.
A skaláris UDF-et DISTINCT záradékkal rendelkező választólistában invokáló lekérdezés nem rendelkezik ORDER BY záradékkal.
Az UDF-et nem egy RETURN utasításból hívják meg, ¹.
Az UDF-et invesztő lekérdezés nem rendelkezik közös táblakifejezésekkel (CTE-kkel) ³.
Az UDF-hívó lekérdezés nem használja GROUPING SETS, CUBEvagy ROLLUP².
Az UDF-hívó lekérdezés nem tartalmaz olyan változót, amelyet A hozzárendelés UDF-paramétereként használnak (például SELECT @y = 2, @x = UDF(@y)) ².
Az UDF nem használható számított oszlopban vagy ellenőrzési kényszerdefinícióban.

¹ korlátozás hozzáadva az SQL Server 2019 -ben (15.x) CU 5

A ² korlátozást az SQL Server 2019 (15.x) CU 6-ban adták hozzá

³ korlátozás hozzáadva az SQL Server 2019 -ben (15.x) CU 11

A legújabb T-SQL skaláris UDF inline javításokról és az inline jogosultsági forgatókönyvek változásairól a Tudásbázis következő cikkében talál további információt: JAVÍTÁS: skaláris UDF inlining problémák az SQL Server 2019-ban.

Ellenőrizze, hogy egy UDF beágyazott-e

Minden T-SQL skaláris UDF esetében a sys.sql_modules katalógusnézet tartalmaz egy is_inlineablenevű tulajdonságot, amely jelzi, hogy egy UDF beágyazott-e.

A is_inlineable tulajdonság az UDF-definícióban található szerkezetekből származik. Nem ellenőrzi, hogy az UDF valóban beilleszthető-e fordításkor. Ha többet szeretne megtudni, tekintse meg a beillesztési feltételeit.

A 1 értéke azt jelzi, hogy az UDF inline-olható, míg a 0 az ellenkezőjét jelzi. Ez a tulajdonság 1 értékkel rendelkezik az összes beágyazott TVF-hez is. Az összes többi modul esetében az érték 0.

Ha egy skaláris UDF beágyazható, az nem jelenti azt, hogy mindig be lesz ágyazva. Az SQL Server (lekérdezésenként és UDF-enként) dönti el, hogy inline-olja-e az UDF-et. Tekintse meg a cikk korábbi követelményeit.

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');

Ellenőrizze, hogy történt-e aláhúzás

Ha az összes előfeltétel teljesül, és az SQL Server úgy dönt, hogy inlininget végez, az átalakítja az UDF-et relációs kifejezéssé. A lekérdezési tervből megtudhatja, hogy történt-e beágyazás:

A terv XML-jének nincs <UserDefinedFunction> XML-csomópontja a sikeresen beágyazott UDF-hez.
Bizonyos kiterjesztett események kerülnek kibocsátásra.

Skaláris UDF-inlining engedélyezése

Az adatbázis 150-es kompatibilitási szintjének engedélyezésével a számítási feladatokat automatikusan jogosulttá teheti a skaláris UDF-formázásra. Ezt a Transact-SQL használatával állíthatja be. Például:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Ettől a lépéstől eltekintve az UDF-ek vagy lekérdezések nem igényelnek további módosításokat a funkció előnyeinek kihasználásához.

Skaláris UDF-vázlatok letiltása a kompatibilitási szint módosítása nélkül

A skaláris UDF-inlining le van tiltva az adatbázis, az utasítás vagy az UDF hatókörében, miközben továbbra is fenntartja a 150-es és újabb adatbázis-kompatibilitási szintet. Ha le szeretné tiltani a skaláris UDF-utasításokat az adatbázis hatókörében, hajtsa végre a következő utasítást az alkalmazandó adatbázis kontextusában:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Az adatbázis skaláris UDF-formázásának újbóli engedélyezéséhez hajtsa végre a következő utasítást az alkalmazandó adatbázis kontextusában:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Ha ON, akkor ez a beállítás bekapcsoltként jelenik meg a sys.database_scoped_configurationsrendszerben.

A skaláris UDF beillesztését egy adott lekérdezésnél úgy is letilthatja, hogy a DISABLE_TSQL_SCALAR_UDF_INLINING-t USE HINT lekérdezési tippként jelöl meg.

A USE HINT lekérdezési tipp elsőbbséget élvez az adatbázis hatókörébe tartozó konfigurációval vagy kompatibilitási szinttel szemben.

Például:

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'));

A skaláris UDF-formázás egy adott UDF-hez is letiltható a CREATE FUNCTION vagy ALTER FUNCTION utasítás INLINE záradékával. Például:

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

Az előző utasítás végrehajtása után ez az UDF soha nem lesz beágyazva egyetlen olyan lekérdezésbe sem, amely meghívja azt. Ha újra engedélyezze az inlininget ehhez az UDF-hez, hajtsa végre a következő utasítást:

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

A INLINE záradék nem kötelező. Ha a INLINE záradék nincs megadva, az automatikusan ON/OFF értékre van állítva attól függően, hogy az UDF beágyazható-e. Ha INLINE = ON van megadva, de az UDF nem megfelelő a formázáshoz, a rendszer hibát jelez.

Megjegyzések

A jelen cikkben leírtak szerint a skaláris UDF-ek a skaláris UDF-ekkel rendelkező lekérdezéseket egy velük egyenértékű skaláris al-lekérdezéssel rendelkező lekérdezéssé alakítják át. Az átalakítás miatt a következő forgatókönyvekben a viselkedésbeli eltéréseket tapasztalhatja:

Az eredmények formázása egy másik lekérdezési kivonatot eredményez ugyanahhoz a lekérdezési szöveghez.
Az UDF-ben található utasítások bizonyos figyelmeztetései (például a nullával való osztás stb.), amelyek korábban rejtettek lehetnek, a feliratozás miatt megjelenhetnek.
Előfordulhat, hogy a lekérdezési szintű illesztési tippek már nem érvényesek, mivel a vázlat új illesztéseket eredményezhet. Ehelyett helyi illesztésmutatókat kell használni.
A beágyazott skaláris UDF-ekre hivatkozó nézetek nem indexelhetők. Ha indexet kell létrehoznia az ilyen nézeteken, tiltsa le a hivatkozott UDF-ek aláhúzását.
Az dinamikus adatmaszkolás UDF-aláhúzással való viselkedésében lehetnek eltérések.

Bizonyos helyzetekben (az UDF logikájától függően) az inline-olás konzervatívabb lehet a kimeneti oszlopok maszkolásának tekintetében. Olyan esetekben, amikor a UDF-ben hivatkozott oszlopok nem kimeneti oszlopok, nincsenek maszkolva.
Ha egy UDF beépített függvényekre, például SCOPE_IDENTITY(), @@ROWCOUNTvagy @@ERRORhivatkozik, a beépített függvény által visszaadott érték inline-olással megváltozik. Ennek a viselkedésbeli változásnak az az oka, hogy az inlining módosítja az utasítások hatókörét az UDF-en belül. Az SQL Server 2019 (15.x) CU2-től kezdve a rendszer letiltja a formázást, ha az UDF bizonyos belső függvényekre hivatkozik (például @@ROWCOUNT).
Ha egy változót egy beágyazott UDF eredményével rendelnek hozzá, és a index_column_nameFORCESEEK (Transact-SQL) is használják, az 8622-es hibát eredményez, ami azt jelzi, hogy a lekérdezésfeldolgozó nem tudott lekérdezéstervet létrehozni a lekérdezésben meghatározott tippek miatt.

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2025-11-18