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

A Transact-SQL implementált és egyetlen adatértéket visszaadó felhasználó által definiált függvényeket (UDF-eket) T-SQL skaláris felhasználó által definiált függvényeknek 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 az összetett üzleti szabályok, könnyebben kifejezhetők imperatív UDF-formában. Az UDF-ek segítségével anélkül hozhat létre ilyen logikát, hogy szakértelemre van szüksége az 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 SQL Database Engine iteratívan hívja meg az UDF-eket, minden megfelelő rekord esetén egyszer. Ez a folyamat többletköltséggel jár a függvényhívások miatti ismétlődő környezetváltások miatt. A definíciójukban Transact-SQL lekérdezéseket végrehajtó UDF-ek súlyosan érintettek.
A költségszámítás hiánya. Az optimalizálás során az adatbázismotor csak a relációs operátorok költségeit fedezi, míg a skaláris operátorok költsége 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 esetek, amikor a tényleges költség jelentős, de az optimalizáló még mindig alulreprezentálja azt.
Értelmezett végrehajtás. Az adatbázismotor utasításkötegként értékeli ki az UDF-eket, és utasításonként végrehajtja őket. Minden utasítás lefordításra kerül, és a lefordított terv gyorsítótárba kerül. Bár ez a gyorsítótárazási stratégia időt takarít meg az újrafordítások elkerülésével, minden utasítás elszigetelten hajtódik végre. Az adatbázismotor nem végez utasítások közötti optimalizálást.
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.

Az UDF inlining funkcióval az adatbázismotor automatikusan skaláris kifejezésekké vagy skaláris alquerekké alakítja át a skaláris UDF-eket. Az adatbázismotor ezeket a kifejezéseket vagy al lekérdezéseket helyettesíti a hívó lekérdezésben az UDF-operátor helyett. A lekérdezésoptimalizáló ezután optimalizálja ezeket a kifejezéseket és al lekérdezéseket. Ennek eredményeképpen a lekérdezési terv már nem rendelkezik felhasználó által definiált függvényoperátorsal, de megfigyelheti annak hatásait a tervben, például nézetekben vagy beágyazott táblaértékű függvényekben (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ókért lásd: CREATE FUNCTION és skaláris UDF beágyazása.

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:

Query	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

Beágyazott skaláris UDF-eket is használhat több T-SQL-utasítással, például változó-hozzárendelésekkel és feltételes elágaztatásokkal. 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 első lépéseként 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 a következő alapstratégiát alkalmazza: 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 inlining használatával az ilyen UDF-eket egyenértékű skaláris allekérdezésekké alakíthatja, amelyeket a hívó lekérdezés az UDF helyére illeszt be.

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 most már láthatja annak hatásait 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. Mint látható, az UDF-ben lévő műveletek már nem átlátszatlanok, így a lekérdezésoptimalizáló költségessé és optimalizálhatóá teheti ezeket a műveleteket. 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-et hívó lekérdezés nem tartalmaz olyan változót, amelyet UDF-paraméterként használ a hozzárendeléshez (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-javításokról és a jogosultsági forgatókönyvek módosításáról a Tudásbázis következő cikkében talál további információt: JAVÍTÁS: skaláris UDF-problémák a 2019-es SQL Server-ben.

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

Az adatbázis, utasítás vagy UDF-hatókör skaláris UDF-tagolását letilthatja, miközben továbbra is fenntartja a 150-es és újabb adatbázis-kompatibilitási szintet. Ha le szeretné tiltani a skaláris UDF-et az adatbázis hatókörében, futtassa a következő utasítást a vonatkozó adatbázis kontextusában:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Az adatbázis skaláris UDF-jének újbóli engedélyezéséhez futtassa a következő utasítást az alkalmazandó adatbázis kontextusában:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Ha ezt a beállítást ON értékre állítja, akkor engedélyezettként jelenik meg a sys.database_scoped_configurations nézetben.

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

Az INLINE záradéknak a CREATE FUNCTION vagy ALTER FUNCTION utasításban való használatával egy adott UDF esetében a skaláris UDF-inlineolás is letiltható. 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 futtatása után ez az UDF soha nem lesz beágyazva egyetlen olyan lekérdezésbe sem, amely meghívja azt. Az ehhez az UDF-hez tartozó beágyazás újbóli engedélyezéséhez futtassa 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 nem adja meg a INLINE záradékot, az automatikusan arra van beállítva ON , vagy OFF attól függően, hogy az UDF beágyazott lehet-e. Ha megadja a(z) INLINE = ON értéket, de az UDF nem alkalmas a beágyazásra, hiba történik.

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), amelyek korábban rejtettek lehetnek, a beágyazottság 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 join hintet kell használnia.
Nem indexelheti a beágyazott skaláris UDF-ekre hivatkozó nézeteket. 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ónak egy beágyazott UDF eredményét adja értékül, és azt index_column_name is használja a FORCESEEKLekérdezési tippek (Transact-SQL) részben, az 8622-es hibát eredményez. Ez a hiba azt jelzi, hogy a lekérdezésfeldolgozó nem tudott lekérdezési tervet létrehozni a lekérdezésben meghatározott tippek miatt.

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2026-06-30