Inlining di funzioni scalari definite dall'utente

Si applica a: SQL Server 2019 (15.x) Database SQL di AzureIstanza gestita di Azure SQLEndpoint di analisi SQL in Microsoft FabricWarehouse in Microsoft FabricDatabase SQL in Microsoft Fabric

Questo articolo presenta l'inlining delle UDF scalari, una funzionalità della suite di funzionalità Intelligent query processing nei database SQL. Questa funzionalità migliora le prestazioni delle query che richiamano funzioni scalari definite dall'utente in SQL Server 2019 (15.x) e versioni successive.

Funzioni scalari definite dall'utente T-SQL

Le funzioni definite dall'utente implementate in Transact-SQL che restituiscono un unico valore di dati sono dette funzioni definite dall'utente scalari T-SQL. Le UDF T-SQL sono un modo elegante per ottenere il riutilizzo e la modularità del codice tra query Transact-SQL diverse. Alcuni calcoli (come regole di business complesse) sono più facili da esprimere in forma UDF imperativa. Le funzioni definite dall'utente aiutano a sviluppare logiche complesse senza richiedere competenze nella scrittura di query SQL complesse. Per altre informazioni sulle funzioni definite dall'utente, vedere Creare funzioni definite dall'utente (motore di database).

Prestazioni delle UDF scalari

Le funzioni scalari definite dall'utente tendono in genere a offrire prestazioni scarse per i motivi seguenti:

  • Chiamata iterativa. Le funzioni definite dall'utente vengono invocate iterativamente, una volta per ogni tupla che soddisfa i criteri. Ciò comporta costi aggiuntivi a causa del cambio di contesto ripetuto dovuto alla chiamata di funzione. In particolare, le UDF che eseguono query Transact-SQL nella propria definizione sono gravemente colpite.

  • Mancanza di calcolo dei costi. Durante l'ottimizzazione vengono definiti i costi dei soli operatori relazionali, non degli operatori scalari. Prima dell'introduzione delle UDF scalari, gli altri operatori scalari erano generalmente poco costosi e non richiedevano una stima dei costi. Per un'operazione scalare era sufficiente aggiungere un costo ridotto per la CPU. In alcuni scenari, il costo effettivo è significativo ma rimane comunque sottorappresentato.

  • Esecuzione interpretata. Le funzioni definite dall'utente vengono valutate come batch di istruzioni e vengono eseguite istruzione per istruzione. Ogni istruzione viene compilata e il piano compilato viene memorizzato nella cache. Questa strategia di memorizzazione nella cache consente di risparmiare tempo perché consente di evitare le ricompilazioni, ma ogni istruzione viene eseguita in modo isolato. Non vengono eseguite ottimizzazioni tra istruzioni diverse.

  • Esecuzione seriale. SQL Server non consente il parallelismo all'interno della query nelle query che richiamano funzioni definite dall'utente.

Inline automatico delle UDF scalari

L'obiettivo della funzionalità di inline delle UDF scalari è migliorare le prestazioni delle query che richiamano UDF scalari T-SQL, in cui l'esecuzione delle UDF rappresenta il principale collo di bottiglia.

Con questa nuova funzionalità, le funzioni scalari definite dall'utente vengono trasformate automaticamente in espressioni scalari o sottoquery scalari, che vengono sostituite nella query chiamante al posto dell'operatore UDF. Queste espressioni e sottoquery vengono quindi ottimizzate. Di conseguenza, il piano di query non ha più un operatore di funzione definito dall'utente, ma i relativi effetti vengono osservati nel piano, ad esempio viste o funzioni con valori di tabella inline (TVFS).

Inlining automatico delle funzioni scalari definite dall'utente in Microsoft Fabric Data Warehouse

Nel Microsoft Fabric Data Warehouse, le funzioni definite dall'utente scalari (attualmente in anteprima) vengono automaticamente inlineate nella fase di compilazione quando il corpo della funzione e la query chiamante soddisfano i requisiti per l'inlining. Per altre informazioni, vedere CREATE FUNCTION e inlining delle funzioni scalari definite dall'utente.

Esempi

Gli esempi in questa sezione usano il database di benchmark TPC-H. Per altre informazioni, vedere la home page di TPC-H.

A. Funzione scalare definita dall'utente a singola istruzione

Si consideri la query seguente.

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;

Questa query calcola la somma dei prezzi scontati per le linee e presenta i risultati raggruppati per data e priorità di spedizione. L'espressione L_EXTENDEDPRICE *(1 - L_DISCOUNT) è la formula del prezzo scontato per una linea specifica. Tali formule possono essere estratte in funzioni a scopo di modularità e riutilizzo.

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

Ora è possibile modificare la query per invocare questa funzione definita dall'utente.

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;

Per i motivi descritti in precedenza, la query con la funzione definita dall'utente offre prestazioni scarse. Con l'inlining delle UDF scalari, l'espressione scalare nel corpo della UDF viene sostituita direttamente nella query. I risultati dell’esecuzione di questa query sono riportati nella tabella seguente:

Query Query senza UDF Query con UDF (senza inlining) Query con inlining di funzione scalare definita dall'utente
Execution time 1,6 secondi 29 minuti 11 secondi 1,6 secondi

Queste cifre si basano su un database CCI di 10 GB (con schema TPC-H) in esecuzione in un computer a doppio processore (12 core), 96 GB di RAM e unità SSD. Le cifre includono il tempo di compilazione ed esecuzione con cache di routine a freddo e pool di buffer. È stata usata la configurazione predefinita e non sono stati creati altri indici.

B. Funzione definita dall'utente scalare a più istruzioni

Le funzioni scalari definite dall'utente implementate usando più istruzioni T-SQL, ad esempio assegnazioni di variabili e ramificazioni condizionali, possono anche essere inlineate. Si consideri la seguente funzione scalare definita dall'utente che, data una chiave del cliente, determina la categoria di servizio per il cliente. Per arrivare alla categoria, prima calcola il prezzo totale di tutti gli ordini effettuati dal cliente tramite una query SQL. Usa quindi la logica IF (...) ELSE per stabilire la categoria in base al prezzo totale.

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

Si consideri ora una query che richiama questa funzione definita dall'utente.

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

Il piano di esecuzione per questa query in SQL Server 2017 (14.x) (livello di compatibilità 140 e versioni precedenti) è il seguente:

Schermata del piano di esecuzione della query senza inlining.

Come illustrato nel piano, SQL Server adotta la seguente strategia di base: per ogni tupla della tabella CUSTOMER, invocare la funzione definita dall'utente e restituire i risultati. Questa strategia è semplicistica e inefficiente. Con l'inlining, una funzione definita dall'utente di questo tipo viene trasformata in una sottoquery scalare equivalente, che viene inserita nella query chiamante al posto della funzione definita dall'utente.

Per la stessa query, il piano con l'UDF inline si presenta come segue.

Schermata del piano di esecuzione della query con inlining.

Come detto in precedenza, il piano di esecuzione della query non contiene più un operatore di funzione definita dall'utente, ma i suoi effetti sono ora osservabili nel piano, come nel caso delle viste o delle TVF inline. Ecco alcune osservazioni chiave del piano precedente:

  • SQL Server deduce il join implicito tra CUSTOMER e ORDERS e lo rende esplicito tramite un operatore join.

  • SQL Server ha anche dedotto la clausola GROUP BY O_CUSTKEY on ORDERS implicita e ha usato IndexSpool + StreamAggregate per implementarla.

  • SQL Server usa ora il parallelismo tra tutti gli operatori.

A seconda della complessità della logica nell'UDF, anche il piano di query risultante potrebbe diventare più grande e più complesso. Come possiamo vedere, le operazioni all'interno dell'UDF non sono più opache, e quindi l'ottimizzatore di query è in grado di stimare i costi di tali operazioni e di ottimizzarle. Inoltre, poiché la UDF non è più presente nel piano, l'invocazione iterativa della UDF viene sostituita da un piano che evita completamente l'overhead delle chiamate di funzione.

Requisiti per le UDF scalari che possono essere inline

Una funzione scalare definita dall'utente in T-SQL può essere inline se la definizione della funzione utilizza costrutti consentiti e la funzione viene usata in un contesto che consente l'inlining:

Tutte le seguenti condizioni della definizione UDF devono essere soddisfatte:

  • La UDF è scritta utilizzando i seguenti costrutti:
    • DECLARE, SET: dichiarazione e assegnazione di variabili.
    • SELECT: query SQL con assegnazione di una o più variabili 1.
    • IF / ELSE: diramazione con livelli di annidamento arbitrari.
    • RETURN: istruzione return singola o istruzioni return multiple. A partire da SQL Server 2019 (15.x) CU5, l'UDF può contenere solo una singola istruzione RETURN da considerare per l'inlining 6.
    • UDF: chiamate di funzione annidate/ricorsive 2.
    • Altro: operazioni relazionali, ad esempio EXISTS, IS NULL.
  • L'UDF non invoca alcuna funzione intrinseca che sia dipendente dal tempo (ad esempio GETDATE()) o abbia effetti collaterali 3 (ad esempio NEWSEQUENTIALID()).
  • La UDF utilizza la clausola EXECUTE AS CALLER (comportamento predefinito se la clausola EXECUTE AS non è specificata).
  • La funzione definita dall'utente (UDF) non fa riferimento a variabili di tabella o a parametri con valori di tipo tabella.
  • La UDF non è compilata in modo nativo (l'interoperabilità è supportata).
  • La UDF non fa riferimento a tipi definiti dall'utente.
  • Non sono state aggiunte firme alla UDF 9.
  • La UDF non è una funzione di partizione.
  • L'UDF non contiene riferimenti a espressioni di tabella comuni (CTE).
  • La funzione definita dall'utente non contiene riferimenti a funzioni intrinseche che potrebbero alterare i risultati quando vengono inserite inline (ad esempio, @@ROWCOUNT) 4.
  • L'UDF non contiene funzioni di aggregazione passate come parametri a un'UDF scalare 4.
  • L'UDF non fa riferimento a viste predefinite (ad esempio, OBJECT_ID) 4.
  • L'UDF non fa riferimento ai metodi XML 5.
  • La UDF non contiene un'istruzione SELECT con ORDER BY senza una clausola TOP 15.
  • La UDF non contiene una query SELECT che esegue un'assegnazione con la clausola ORDER BY (ad esempio SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
  • L'UDF non contiene istruzioni RETURN multiple 6.
  • L'UDF non fa riferimento alla funzione STRING_AGG6.
  • La UDF non fa riferimento a tabelle remote 7.
  • La UDF non fa riferimento alle colonne crittografate 8.
  • L'UDF non contiene riferimenti a WITH XMLNAMESPACES8.
  • Se la definizione della UDF arriva a migliaia di righe di codice, SQL Server potrebbe scegliere di non eseguirne l'inlining.

1SELECT con accumulazione/aggregazione di variabili non supporta l'inlining (ad esempio SELECT @val += col1 FROM table1).

2 Le UDF ricorsive vengono espanse inline solo fino a una certa profondità.

3 Le funzioni intrinseche i cui risultati dipendono dall'ora di sistema corrente sono dipendenti dall'ora. Un esempio di funzione con effetti collaterali può essere costituito da una funzione intrinseca in grado di aggiornare uno stato globale interno. Tali funzioni restituiscono risultati diversi ogni volta che vengono chiamate, a seconda dello stato interno.

4 Restrizione aggiunta in SQL Server 2019 (15.x) CU 2

5 Restrizione aggiunta in SQL Server 2019 (15.x) CU 4

6 Restrizione aggiunta in SQL Server 2019 (15.x) CU 5

7 Restrizione aggiunta in SQL Server 2019 (15.x) CU 6

8 Restrizione aggiunta in SQL Server 2019 (15.x) CU 11

9 Poiché è possibile aggiungere o rimuovere signature dopo la creazione di una UDF, la decisione se applicare l'inlining viene presa quando viene compilata la query che fa riferimento a una UDF scalare. Le funzioni di sistema, ad esempio, vengono in genere firmate con un certificato. È possibile usare sys.crypt_properties per individuare gli oggetti firmati.

Tutti i seguenti requisiti del contesto di esecuzione devono essere soddisfatti:

  • La UDF non è utilizzata nella clausola ORDER BY.
  • La query che chiama una funzione definita dall'utente scalare non fa riferimento a una chiamata di funzione definita dall'utente scalare nella relativa clausola GROUP BY.
  • La query che richiama una UDF scalare nel relativo elenco SELECT con la clausola DISTINCT non ha una clausola ORDER BY.
  • La UDF non viene chiamata in un'istruzione RETURN 1.
  • La query che richiama l'UDF non contiene espressioni di tabella comuni (CTEs) 3.
  • La query di chiamata alla funzione definita dall'utente non usa GROUPING SETS, CUBEo ROLLUP2.
  • La query che richiama la UDF non contiene una variabile utilizzata come parametro UDF per un'assegnazione (ad esempio, SELECT @y = 2, @x = UDF(@y)) 2.
  • La UDF non è usata in una colonna calcolata o nella definizione di un vincolo CHECK.

1 Restrizione aggiunta in SQL Server 2019 (15.x) CU 5

2 Restrizione aggiunta in SQL Server 2019 (15.x) CU 6

3 Restrizione aggiunta in SQL Server 2019 (15.x) CU 11

Per informazioni sulle correzioni più recenti relative all'inlining delle UDF scalari T-SQL e sulle modifiche agli scenari di idoneità per l'inlining, vedere l'articolo della Knowledge Base: FIX: Problemi di inlining delle UDF scalari in SQL Server 2019.

Verificare se una funzione definita dall'utente può essere inserita inline

Per ogni funzione definita dall'utente scalare T-SQL, la vista del catalogo sys.sql_modules include una proprietà denominata is_inlineable, che indica se una funzione definita dall'utente è inline.

La proprietà is_inlineable deriva dai costrutti contenuti nella definizione UDF. Non verifica se la funzione definita dall'utente (UDF) sia effettivamente inseribile inline in fase di compilazione. Per ulteriori informazioni, vedere le condizioni per l'inline.

Un valore di 1 indica che la UDF è inlineabile, mentre un valore di 0 indica il contrario. Questa proprietà ha un valore pari a 1 anche per tutte le funzioni con valori di tabella inline. Per tutti gli altri moduli, il valore è 0.

Se una funzione scalare definita dall'utente è idonea all'inlining, non implica che venga sempre resa inline. SQL Server decide, in base a ogni singola query e a ogni singola UDF, se eseguire l'inlining di una UDF. Fare riferimento agli elenchi dei requisiti descritti in precedenza in questo articolo.

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

Controllare se l'inlining è avvenuto

Se tutti i prerequisiti sono soddisfatti e SQL Server decide di effettuare l'inlining, trasforma la UDF in un'espressione relazionale. Dal piano di query è possibile determinare se si è verificata l'inlining:

  • Il piano XML non contiene un nodo XML <UserDefinedFunction> per una UDF il cui inlining è stato eseguito correttamente.
  • Vengono emessi alcuni Extended Events.

Attivare l'inlining di funzioni scalari definite dall'utente

È possibile rendere automaticamente idonei i carichi di lavoro per l'inlining delle funzioni scalari definite dall'utente abilitando il livello di compatibilità 150 per il database. Questa opzione è impostabile con Transact-SQL. Ad esempio:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Oltre a questo passaggio, non sono necessarie altre modifiche agli UDF o alle query per usufruire di questa funzionalità.

Disabilitare l'inlining delle UDF scalari senza modificare il livello di compatibilità

È possibile disabilitare l'inlining delle funzioni scalari definite dall'utente a livello di database, istruzione o UDF, pur mantenendo il livello di compatibilità del database pari o superiore a 150. Per disabilitare l'inlining delle funzioni scalari definite dall'utente a livello di database, eseguire l'istruzione seguente nel contesto del database interessato:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Per riattivare l'inlining delle UDF scalari per il database, eseguire l'istruzione seguente nel contesto del database interessato:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando ON, questa impostazione viene visualizzata come abilitata in sys.database_scoped_configurations.

È anche possibile disabilitare l'inlining delle funzioni scalari definite dall'utente per una query specifica specificando DISABLE_TSQL_SCALAR_UDF_INLINING come suggerimento di query USE HINT.

Un hint di query USE HINT ha la precedenza sulla configurazione con ambito del database o sull'impostazione del livello di compatibilità.

Ad esempio:

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

È anche possibile disabilitare l'inlining delle UDF scalari per una UDF specifica tramite la clausola INLINE nell'istruzione CREATE FUNCTION o ALTER FUNCTION. Ad esempio:

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

Una volta eseguita l'istruzione precedente, questa funzione definita dall'utente (UDF) non viene mai incorporata in alcuna query che la richiama. Per riattivare l'inlining per questo UDF, eseguire l'istruzione seguente:

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

La clausola INLINE non è obbligatoria. Se la clausola INLINE non è specificata, assume automaticamente il valore ON/OFF a seconda che la UDF possa essere inline. Se è specificato INLINE = ON, ma l'UDF risulta non idonea all'inlining, viene generato un errore.

Osservazioni:

Come descritto in questo articolo, l'inlining delle UDF scalari trasforma una query con UDF scalari in una query con una sottoquery scalare equivalente. A causa di questa trasformazione, si possono notare alcune differenze di comportamento negli scenari seguenti:

  • L'inlining ha come risultato un hash di query diverso per lo stesso testo della query.

  • Alcuni avvisi nelle istruzioni all'interno della UDF (ad esempio divisione per zero e così via), che in precedenza potevano essere nascosti, possono comparire a causa dell'inlining.

  • Gli hint di join a livello di query possono non essere più validi, poiché l'inlining può introdurre nuovi join. Devono essere usati invece hint di join locali.

  • Non è possibile indicizzare le viste che fanno riferimento a funzioni scalari inline definite dall'utente. Se è necessario creare un indice su tali viste, disabilita l'inlining per le UDF a cui si fa riferimento.

  • Con l'inlining di funzioni definite dall'utente possono presentarsi alcune differenze nel comportamento del Dynamic Data Masking.

    In determinate circostanze (a seconda della logica nell'UDF), l'inlining potrebbe essere più conservativo per quanto riguarda il mascheramento delle colonne di output. Negli scenari in cui le colonne a cui si fa riferimento in una funzione definita dall'utente non sono colonne di output, queste non vengono mascherate.

  • Se una UDF fa riferimento a funzioni incorporate come SCOPE_IDENTITY(), @@ROWCOUNT o @@ERROR, il valore restituito dalla funzione incorporata cambia con l'inlining. Questa modifica del comportamento è dovuta al fatto che l'espansione inline modifica l'ambito delle istruzioni all'interno della funzione definita dall'utente (UDF). A partire da SQL Server 2019 (15.x) CU2, l'inlining viene bloccato se l'UDF fa riferimento a determinate funzioni intrinseche (ad esempio, @@ROWCOUNT).

  • Se a una variabile è assegnato il risultato di una funzione definita dall'utente incorporata e viene usata anche come index_column_name negli FORCESEEKhint della query (Transact-SQL), produce l'errore 8622, indicando che il processore di query non è riuscito a produrre un piano di query a causa degli hint definiti nella query.

Contenuto correlato

  • Last updated on