Voglio ringraziare per le numerose visite a questo blog.
Grazie inoltre per le domande che mi sono pervenute, cercherò di dare un risposta a tutti quelli che mi hanno scritto. Promesso!
Anche oggi volevo mostrarvi una miglioria ed un altro motivo in più per upgradare a SQL Server 2019.
Questo volta parliamo di User Defined function conosciute anche con l'acronimo di UDF.
Diciamo subito che in SQL Server possiamo avere tipi diversi di UDF:
- Le funzioni scalari ovvero quello che ritornano un solo valore
- Le funzioni multi-statement table valued dette TVF che ritornano più valori
- Le funzioni inline table valued che sono ottimizzate per le prestazioni.
Oggi parleremo solo delle funzioni scalari.
Una esempio può essere la seguente funzione scalare:
CREATE OR ALTER FUNCTION F_GET_PREZZO(@ARTID INTEGER)
RETURNS FLOAT
AS
BEGIN
DECLARE @Prezzo FLOAT
SELECT @Prezzo = PREZZO FROM LISTINO WHERE ARTID=@ARTID;
RETURN(@Prezzo)
END;
Dall'esempio potete vedere la definizione.
In quanto funzione accetta in input uno o più parametri.
Ritorna in output un solo parametro.
Dopo aver definito una funzione la possiamo utilizzare all'interno delle nostre Query:
SELECT F_GET_PREZZO(ARTID) FROM LISTINO
Adesso vi mostrerò la logica delle miglioria che va sotto il nome di inline scalar function introdotta con il nuovo SQL Server 2019.
Facciamo qualche test pratico per vedere che cosa cambia!
Per effetturare i test andiamo ad agire sul compatibility level usando il livello 140 ed il livello nativo di SQL Server 2019 che è il 150.
Inline scalar function
Vediamo prima come si comporta SQL Server 2017 con Compatibilty level 140
Se eseguiamo la nostra SELECT vediamo che l'operazione dura 120 ms.
Tabella 'ARTLIST'. Conteggio analisi 1, letture logiche 5, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
(1 riga interessata)
Tempo di esecuzione SQL Server:
tempo di CPU = 78 ms, tempo trascorso = 120 ms.
Tempo di analisi e compilazione SQL Server:
tempo di CPU = 0 ms, tempo trascorso = 0 ms.
Dal piano di esecuzione si nota che è presente un operatore di calcolo scalare che non è altro che la nostra funzione. Dalle proprietà vediamo questo operatore che viene eseguito per ogni riga della tabella ARTLIST.
Passiamo ora al compatibility level 150, livello nativo di SQL Server 2019.
Eseguiamo nuovamente la nostra SELECT e vediamo che il tempo di esecuzione si è ridotto a soli 39 ms.
(1 riga interessata)
Tabella 'LISTINO'. Conteggio analisi 1, letture logiche 2, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
Tabella 'ARTLIST'. Conteggio analisi 1, letture logiche 5, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
(1 riga interessata)
Tempo di esecuzione SQL Server:
tempo di CPU = 0 ms, tempo trascorso = 39 ms.
Tempo di analisi e compilazione SQL Server:
tempo di CPU = 0 ms, tempo trascorso = 0 ms.
Per capire come mai il tempo di esecuzione si è così ridotto occorre osservare il piano di esecuzione:
Ed ecco qua la novità!
Il piano di esecuzione si è modificato ed ora l'optimizer di SQL Server riesce ad inglobare anche la nostra funzione.
La select contenuta dentro alla funzione si è trasformata nella parte evidenziata in verde.
(La SELECT PREZZO FROM LISTINO contenuta all'interno della funzione si è trasformata in una SELECT sulla tabella listino ed il risultato del blocco evidenziato che ritorna il campo PREZZO viene infine messo in JOIN con la tabella ARTLIST tramite l'operatore Cicli annidati)
Non male vero? Questo è inlining!
Purtroppo questa ottimizzazione non viene applicata per tutte le UDF scalari perchè sono presenti delle limitazioni che sono:
- La funzione non deve essere di tipo partizione.
- La funzione non deve far riferimento a table variables
- La funzione non deve utilizzare delle colonne calcolate
- Non si vede chiamare la funzione nella clausola group by
- La funzione deve utilizzare solamente questi costrutti: SELECT, IF, ELSE, RETURN, EXISTS,ISNULL.
- Per ultimo la funzione non deve contenere delle funzioni time-dependent.
Anche per oggi è tutto! Ciao!
Luca Biondi @ SQLServerPerformance blog!
Next post: SQL Server 2019 ed il Batch Mode on Rowstore
Previous post: SQL Server 2019 ed il Memory-Optimized TempDB Metadata