Scalaire UDF-inlining

Van toepassing op: SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL Analytics-eindpunt in Microsoft FabricWarehouse in Microsoft FabricSQL-database in Microsoft Fabric

In dit artikel maakt u kennis met scalaire UDF-functies, een functie onder de Intelligente queryverwerking in SQL-databases suite met functies. Deze functie verbetert de prestaties van query's die scalaire UDF's aanroepen in SQL Server 2019 (15.x) en latere versies.

Door de gebruiker gedefinieerde T-SQL scalaire functies

Door de gebruiker gedefinieerde functies (UDF's) die zijn geïmplementeerd in Transact-SQL en die één gegevenswaarde retourneren, worden door de gebruiker gedefinieerde T-SQL-scalaire functies genoemd. T-SQL UDF's zijn een elegante manier om code opnieuw te gebruiken en modulariteit te realiseren in Transact-SQL query's. Sommige berekeningen, zoals complexe bedrijfsregels, zijn gemakkelijker uit te drukken in imperatieve UDF-vorm. UDF's helpen u bij het bouwen van dergelijke logica zonder dat u expertise nodig hebt bij het schrijven van SQL-query's. Zie Door de gebruiker gedefinieerde functies (Database Engine) makenvoor meer informatie over UDF's.

Prestaties van scalaire UDF's

Scalaire UDF's presteren meestal slecht om de volgende redenen:

  • Iteratieve aanroep. De SQL-Database Engine roept iteratief UDF's aan, eenmaal per in aanmerking komende tuple. Dit proces voegt extra kosten toe vanwege herhaalde contextwisselingen vanwege functie-aanroep. UDF's die Transact-SQL query's in hun definitie uitvoeren, worden ernstig beïnvloed.

  • Gebrek aan kosten. Tijdens optimalisatie kost de database-engine alleen relationele operators, terwijl dit geen scalaire operators kost. Vóór de introductie van scalaire UDF's waren andere scalaire operators over het algemeen goedkoop en hadden ze geen kosten nodig. Een kleine CPU-kost voor een scalaire bewerking was voldoende. Er zijn scenario's waarin de werkelijke kosten aanzienlijk zijn, maar de optimizer nog steeds ondervertegenwoordigd is.

  • Geïnterpreteerde uitvoering. De database-engine evalueert UDF's als een batch met instructies en voert deze instructie per instructie uit. Elke instructie wordt gecompileerd en het gecompileerde plan wordt in de cache opgeslagen. Hoewel deze cachingstrategie enige tijd bespaart door hercompilaties te voorkomen, wordt elke instructie geïsoleerd uitgevoerd. De database-engine voert geen optimalisaties tussen instructies uit.

  • Seriële uitvoering. SQL Server staat geen intra-query parallellisme toe in query's die UDF's aanroepen.

Automatisch inlijnen van scalaire UDF's

Het doel van de scalaire UDF-functie inlining is het verbeteren van de prestaties van query's die T-SQL scalaire UDF's aanroepen, waarbij UDF-uitvoering het belangrijkste knelpunt is.

Met behulp van de functie UDF-inlining transformeert de database-engine automatisch scalaire UDF's naar scalaire expressies of scalaire subquery's. De database-engine vervangt deze expressies of subquery's in de aanroepende query in plaats van de UDF-operator. De queryoptimalisatie optimaliseert vervolgens deze expressies en subquery's. Als gevolg hiervan heeft het queryplan geen door de gebruiker gedefinieerde functieoperator meer, maar u kunt de effecten ervan in het plan bekijken, zoals weergaven of inline-functies met tabelwaarden (TVF's).

Automatisch inlijnen van scalaire UDF's in Microsoft Fabric Data Warehouse

In Microsoft Fabric Data Warehouse worden scalaire UDF's (momenteel in preview) automatisch inline weergegeven tijdens het compileren wanneer de hoofdtekst van de functie en de aanroepende query voldoen aan de vereisten voor inlineing. Zie CREATE FUNCTION en Scalar UDF inlining voor meer informatie.

Voorbeelden

In de voorbeelden in deze sectie wordt de TPC-H benchmarkdatabase gebruikt. Zie de TPC-H Homepagevoor meer informatie.

Een. Scalaire UDF met één uitdrukking

Houd rekening met de volgende query.

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;

Met deze query wordt de som berekend van kortingsprijzen voor regelitems en worden de resultaten gegroepeerd op de verzenddatum en verzendprioriteit weergegeven. De expressie L_EXTENDEDPRICE *(1 - L_DISCOUNT) is de formule voor de kortingsprijs voor een bepaald regelitem. Dergelijke formules kunnen worden geëxtraheerd in functies ten behoeve van modulariteit en hergebruik.

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

De query kan nu worden gewijzigd om deze UDF aan te roepen.

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;

De query met de UDF presteert slecht vanwege de redenen die eerder zijn beschreven. Met scalaire UDF-inlining wordt de scalaire expressie in de hoofdtekst van de UDF rechtstreeks in de query vervangen. De resultaten van het uitvoeren van deze query worden weergegeven in de volgende tabel:

Query Query uitvoeren zonder UDF Query uitvoeren met UDF (zonder inlining) Query uitvoeren met scalaire UDF-inlining
Execution time 1,6 seconden 29 minuten 11 seconden 1,6 seconden

Deze getallen zijn gebaseerd op een CCI-database van 10 GB (met behulp van het TPC-H schema), die wordt uitgevoerd op een computer met dubbele processor (12 kernen), 96 GB RAM, ondersteund door SSD. De getallen omvatten compilatie- en uitvoeringstijd met een koude procedurecache en buffergroep. De standaardconfiguratie is gebruikt en er zijn geen andere indexen gemaakt.

B. Scalaire UDF met meerdere instructies

U kunt ook scalaire UDF's met meerdere T-SQL-instructies, zoals variabeletoewijzingen en voorwaardelijke vertakkingen, als inline-functies opnemen. Houd rekening met de volgende scalaire UDF die, gezien een klantsleutel, de servicecategorie voor die klant bepaalt. Het komt bij de categorie aan door eerst de totale prijs te berekenen van alle orders die door de klant zijn geplaatst met behulp van een SQL-query. Vervolgens wordt een IF (...) ELSE logica gebruikt om de categorie te bepalen op basis van de totale prijs.

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

Overweeg nu een query die deze UDF aanroept.

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

Het uitvoeringsplan voor deze query in SQL Server 2017 (14.x) (compatibiliteitsniveau 140 en eerder) is als volgt:

schermopname van query plan zonder inlining.

Zoals het plan laat zien, gebruikt SQL Server de volgende basisstrategie: voor elke tuple in de CUSTOMER tabel roept u de UDF aan en voert u de resultaten uit. Deze strategie is naïef en inefficiënt. Door inlining te gebruiken, kunt u dergelijke UDF's omzetten in equivalente scalaire subquery's, die door de aanroepende query op de plaats van de UDF worden ingevoegd.

Voor dezelfde query ziet het plan met de inlined UDF er als volgt uit.

Schermopname van queryplan met inlining.

Zoals eerder vermeld, heeft het queryplan niet langer een door de gebruiker gedefinieerde functieoperator, maar u kunt de effecten ervan nu wel in het plan zien, net als bij weergaven of inline TVF's. Hier volgen enkele belangrijke opmerkingen uit het vorige plan:

  • SQL Server leidt de impliciete koppeling tussen CUSTOMER en ORDERS af en maakt dit expliciet via een koppeloperator.

  • SQL Server leidt ook de impliciete GROUP BY O_CUSTKEY on ORDERS af en gebruikt IndexSpool + StreamAggregate om de implementatie uit te voeren.

  • SQL Server maakt nu gebruik van parallelle uitvoering voor alle operators.

Afhankelijk van de complexiteit van de logica in de UDF, kan het resulterende queryplan ook groter en complexer worden. Zoals u ziet, zijn de bewerkingen in de UDF niet langer ondoorzichtig, zodat de queryoptimalisator de kosten ervan kan bepalen en deze bewerkingen kan optimaliseren. Omdat de UDF zich niet meer in het plan bevindt, wordt de iteratieve UDF-aanroep vervangen door een plan dat de overhead van functieoproepen volledig elimineert.

Inlineable scalaire UDF-vereisten

Een scalaire T-SQL UDF kan inline worden geplaatst als de functiedefinitie toegestane constructies gebruikt en de functie wordt gebruikt in een context die inlining mogelijk maakt:

Alle volgende voorwaarden van de UDF-definitie moeten waar zijn:

  • De UDF wordt geschreven met behulp van de volgende constructies:
    • DECLARE, SET: Variabelen declaratie en toewijzingen.
    • SELECT: SQL-query met toewijzingen van één/meerdere variabelen 1.
    • IF / ELSE: Vertakking met willekeurige niveaus van nesting.
    • RETURN: Enkele of meerdere return statements. Vanaf SQL Server 2019 (15.x) CU5 kan de UDF slechts één RETURN-instructie bevatten die moet worden overwogen voor het inlijnen van 6.
    • UDF: Geneste/recursieve functie-aanroepen 2.
    • Andere: Relationele bewerkingen zoals EXISTS, IS NULL.
  • De UDF roept geen intrinsieke functie aan die tijdafhankelijk is (zoals GETDATE()) of bijwerkingen heeft 3 (zoals NEWSEQUENTIALID()).
  • De UDF maakt gebruik van de EXECUTE AS CALLER component (standaardgedrag als de EXECUTE AS component niet is opgegeven).
  • De UDF verwijst niet naar tabelvariabelen of parameters met tabelwaarden.
  • De UDF is niet systeemeigen gecompileerd (interop wordt ondersteund).
  • De UDF verwijst niet naar door de gebruiker gedefinieerde typen.
  • Er zijn geen handtekeningen toegevoegd aan de UDF 9.
  • De UDF is geen partitiefunctie.
  • De UDF bevat geen verwijzingen naar Common Table Expressions (CTE's).
  • De UDF bevat geen verwijzingen naar intrinsieke functies die de resultaten kunnen wijzigen wanneer ze inline zijn (zoals @@ROWCOUNT) 4.
  • De UDF bevat geen statistische functies die als parameters worden doorgegeven aan een scalaire UDF 4.
  • De UDF verwijst niet naar ingebouwde weergaven (zoals OBJECT_ID) 4.
  • De UDF verwijst niet naar XML-methoden 5.
  • De UDF bevat geen SELECT met ORDER BY zonder een TOP 1 component 5.
  • De UDF bevat geen SELECT-query waarmee een toewijzing wordt uitgevoerd met de ORDER BY component (zoals SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
  • De UDF bevat niet meerdere RETURN-verklaringen 6.
  • De UDF verwijst niet naar de functie STRING_AGG6.
  • De UDF verwijst niet naar externe tabellen 7.
  • De UDF verwijst niet naar versleutelde kolommen 8.
  • De UDF bevat geen verwijzingen naar WITH XMLNAMESPACES8.
  • Als de UDF-definitie duizenden regels code bevat, kan SQL Server ervoor kiezen om deze niet inline te zetten.

1SELECT met variabele accumulatie/aggregatie wordt niet ondersteund voor inlineing (zoals SELECT @val += col1 FROM table1).

2 recursieve UDF's worden alleen in een bepaalde diepte geplaatst.

3 Intrinsieke functies waarvan de resultaten afhankelijk zijn van de huidige systeemtijd, tijdafhankelijk zijn. Een intrinsieke functie die een interne globale status kan bijwerken, is een voorbeeld van een functie met bijwerkingen. Dergelijke functies retourneren verschillende resultaten telkens wanneer ze worden aangeroepen, op basis van de interne status.

4 Beperking toegevoegd in SQL Server 2019 (15.x) CU 2

5 Beperking toegevoegd in SQL Server 2019 (15.x) CU 4

6 Beperking toegevoegd in SQL Server 2019 (15.x) CU 5

7 Beperking toegevoegd in SQL Server 2019 (15.x) CU 6

8 Beperking toegevoegd in SQL Server 2019 (15.x) CU 11

9 Omdat handtekeningen kunnen worden toegevoegd en verwijderd nadat een UDF is gemaakt, wordt de beslissing of inline moet worden uitgevoerd wanneer de query naar een scalaire UDF verwijst. Systeemfuncties worden bijvoorbeeld meestal ondertekend met een certificaat. U kunt sys.crypt_properties gebruiken om te zoeken welke objecten zijn ondertekend.

Alle volgende vereisten van de uitvoeringscontext moeten waar zijn:

  • De UDF wordt niet gebruikt in ORDER BY clausule.
  • De query die een scalaire UDF aanroept, verwijst niet naar een scalaire UDF-aanroep in de GROUP BY-clausule.
  • De query die een scalaire UDF aanroept in zijn selectielijst met DISTINCT clausule heeft geen ORDER BY clausule.
  • De UDF wordt niet aangeroepen vanuit een RETURN-instructie 1.
  • De query die de UDF aanroept, heeft geen algemene tabelexpressies (CTE's) 3.
  • De UDF-aanroepende query gebruikt geen GROUPING SETS, CUBEof ROLLUP2.
  • De aanroepende UDF-query bevat geen variabele die u gebruikt als UDF-parameter voor toewijzing (bijvoorbeeld SELECT @y = 2@x = UDF(@y)) 2.
  • U gebruikt de UDF niet in een berekende kolom of een definitie van een controlebeperking.

1 Beperking toegevoegd in SQL Server 2019 (15.x) CU 5

2 Beperking toegevoegd in SQL Server 2019 (15.x) CU 6

3 Beperking toegevoegd in SQL Server 2019 (15.x) CU 11

Zie het Knowledge Base-artikel: FIX: scalar UDF inlining issues in SQL Server 2019 voor informatie over de nieuwste scalaire UDF-oplossingen en wijzigingen in het inlijnen van geschiktheidsscenario's voor T-SQL.

Controleren of een UDF inline kan zijn

Voor elke scalaire T-SQL-UDF bevat de sys.sql_modules catalogusweergave een eigenschap met de naam is_inlineable, die aangeeft of een UDF inlineable is.

De eigenschap is_inlineable is afgeleid van de constructies in de UDF-definitie. Er wordt niet gecontroleerd of de UDF in feite inlineable is tijdens het compileren. Zie de voorwaarden voor het inlijnen vanvoor meer informatie.

Een waarde van 1 geeft aan dat de UDF inlineable is en 0 anders aangeeft. Deze eigenschap heeft ook een waarde van 1 voor alle inline-TVF's. Voor alle andere modules is de waarde 0.

Als een scalaire UDF inlineable is, betekent dit niet dat deze altijd inline is. SQL Server bepaalt (per query, per UDF) of een UDF inline moet worden geplaatst. Raadpleeg de lijsten met vereisten eerder in dit artikel.

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

Controleren of de inlining heeft plaatsgevonden

Als aan alle voorwaarden wordt voldaan en SQL Server besluit om inlining uit te voeren, wordt de UDF omgezet in een relationele expressie. In het queryplan kunt u achterhalen of inlining heeft plaatsgevonden.

  • De XML van het plan heeft geen <UserDefinedFunction> XML-knooppunt voor een UDF die succesvol is ingelijnd.
  • Bepaalde uitgebreide gebeurtenissen worden uitgezonden.

Scalaire UDF-inlining inschakelen

U kunt ervoor zorgen dat workloads automatisch in aanmerking komen voor scalaire UDF-inlining door compatibiliteitsniveau 150 in te schakelen voor de database. U kunt dit instellen met Behulp van Transact-SQL. Bijvoorbeeld:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Naast deze stap zijn er geen andere wijzigingen vereist voor UDF's of query's om te profiteren van deze functie.

Scalaire UDF-inlining uitschakelen zonder het compatibiliteitsniveau te wijzigen

U kunt scalaire UDF-inlining uitschakelen op het database-, instructie- of UDF-bereik, terwijl u nog steeds databasecompatibiliteitsniveau 150 en hoger onderhoudt. Als u scalaire UDF-inlining wilt uitschakelen voor het databasebereik, voert u de volgende instructie uit binnen de context van de toepasselijke database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Als u het inline uitvoeren van scalaire UDF's voor de database opnieuw wilt inschakelen, voert u de volgende instructie uit in de context van de betreffende database:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Wanneer u deze optie ONinstelt op , wordt deze weergegeven als ingeschakeld in sys.database_scoped_configurations.

U kunt scalaire UDF-inlining voor een specifieke query ook uitschakelen door DISABLE_TSQL_SCALAR_UDF_INLINING aan te wijzen als USE HINT queryhint.

Een USE HINT query hint heeft voorrang boven de configuratie-instelling van het databasebereik of de compatibiliteitsniveau-instelling.

Bijvoorbeeld:

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

U kunt voor een specifieke UDF ook scalaire UDF-inlining uitschakelen met behulp van de INLINE-clausule in de CREATE FUNCTION- of ALTER FUNCTION-instructie. Bijvoorbeeld:

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

Nadat u de vorige instructie hebt uitgevoerd, wordt deze UDF nooit geïntegreerd in query's die de functie aanroepen. Voer de volgende instructie uit om inlining voor deze UDF opnieuw in te schakelen:

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

De INLINE-component is niet verplicht. Als u de INLINE-clausule niet opgeeft, wordt deze automatisch ingesteld op ON of OFF, afhankelijk van of de UDF kan worden geïnlined. Als u INLINE = ON opgeeft maar de UDF ongeschikt blijkt voor inlining, wordt een fout gegenereerd.

Opmerkingen

Zoals beschreven in dit artikel, transformeert scalaire UDF-inlining een query met scalaire UDF's in een query met een equivalente scalaire subquery. Vanwege deze transformatie ziet u mogelijk enkele verschillen in gedrag in de volgende scenario's:

  • Het inlijnen van resultaten in een andere query-hash voor dezelfde querytekst.

  • Bepaalde waarschuwingen in instructies binnen de UDF (zoals delen door nul) die voorheen mogelijk verborgen waren, kunnen zichtbaar worden door inlining.

  • Hints voor join op queryniveau zijn mogelijk niet meer geldig, omdat inlining nieuwe joins kan introduceren. In plaats daarvan moet u lokale joinhints gebruiken.

  • U kunt geen weergaven indexeren die verwijzen naar inline scalaire UDF's. Als u een index voor dergelijke weergaven wilt maken, schakelt u de inlining uit voor de UDF's waarnaar wordt verwezen.

  • Er zijn mogelijk enkele verschillen in het gedrag van dynamische gegevensmaskering met UDF-inlining.

    In bepaalde situaties (afhankelijk van de logica in de UDF) kan inlinen meer conservatief zijn met betrekking tot het maskeren van uitvoerkolommen. In scenario's waarin de kolommen waarnaar wordt verwezen in een UDF geen uitvoerkolommen zijn, worden ze niet gemaskeerd.

  • Als een UDF verwijst naar ingebouwde functies zoals SCOPE_IDENTITY(), @@ROWCOUNTof @@ERROR, verandert de waarde die wordt geretourneerd door de ingebouwde functie met inlijning. Deze wijziging in gedrag komt doordat het inlijnen de reikwijdte van instructies binnen de UDF verandert. Vanaf SQL Server 2019 (15.x) CU2 wordt het inlijnen geblokkeerd als de UDF verwijst naar bepaalde intrinsieke functies (bijvoorbeeld @@ROWCOUNT).

  • Als u een variabele toewijst met het resultaat van een inline UDF en deze ook gebruikt als index_column_name in FORCESEEKqueryhints (Transact-SQL), resulteert deze in fout 8622. Deze fout geeft aan dat de queryprocessor geen queryplan kan produceren vanwege de hints die zijn gedefinieerd in de query.

Verwante inhoud

  • Last updated on