Inlinare le funzioni valutate in tabella in SQL Server

Le funzioni valutate in tabella in SQL Server sono ottime per scrivere codice SQL DRY incapsulando frammenti di logica di database di uso comune. Tuttavia, in alcuni casi possono essere alla radice di seri problemi di prestazioni. Diamo un’occhiata ad un esempio di questo. Diciamo che abbiamo una tabella che assomiglia alla seguente.

CREATE TABLE Products(ProductId INT,VendorId INT,Description NVARCHAR(1000))

Ora immaginate che nel contesto di una stored procedure abbiamo la seguente logica ricorrente.

SELECT *FROM ProductsWHERE Description LIKE @Description

Potremmo voler rifattorizzare questo in una funzione per rendere la nostra stored procedure più leggibile e facile da rifattorizzare. Molti tutorial su internet vi diranno di creare una funzione con valore di tabella come segue.

CREATE FUNCTION FN_Products_GetByDescription (@description NVARCHAR(1000))RETURNS @results TABLE(ProductId INT,VendorId INT,Description NVARCHAR(1000))ASBEGIN;INSERT INTO @resultsSELECT *FROM ProductsWHERE Description LIKE @description;RETURN;END;

Il problema con questo tipo di funzione è che si comporta come una scatola nera per il codice che la chiama. Immaginiamo di chiamare la nostra nuova funzione nel seguente scenario.

SELECT *FROM FN_Products_GetByDescription('%book%')WHERE VendorId = 3;

Per eseguire questa funzione, SQL Server deve analizzare ogni prodotto nel nostro database, controllando la descrizione del prodotto rispetto al testo della nostra query, prima di restituire il risultato al codice chiamante che lo filtra in base al VendorId. In grandi database questo è chiaramente molto inefficiente. Non sarebbe meglio far sapere a SQL Server fin dall’inizio che vogliamo filtrare sul testo della nostra query e sul VendorId? In questo modo, potrebbe prima filtrare per VendorId, e poi eseguire il costoso filtraggio LIKE su un set di risultati più piccolo. È qui che entra in gioco l’inlineamento della funzione. Per inlineare la nostra funzione, la riscriviamo semplicemente come segue.

CREATE FUNCTION FN_Products_GetByDescription (@description NVARCHAR(1000))RETURNS TABLEASRETURN(SELECT *FROM ProductsWHERE Description LIKE @description);

Ora, quando SQL Server esegue la query di cui sopra, la funzione viene trattata dietro le quinte come una parte del codice chiamante, invece di una scatola nera che deve restituire un valore prima di andare avanti. Questo permette all’ottimizzatore di query di essere molto più efficace nell’ottimizzare le query, perché sa fin dall’inizio esattamente di quali dati abbiamo bisogno. Ebbene, le funzioni possono essere inlineate solo quando consistono in una singola istruzione SELECT che restituisce un set di risultati. Le funzioni che contengono più sottoquery che riempiono una tabella di risultati, così come quelle che contengono dichiarazioni aggiuntive come gli IF non sono inlineabili. Tuttavia, se è possibile rendere una query inlineabile, direttamente o riscrivendola leggermente, allora è generalmente una buona idea. Il guadagno di prestazioni che si può ottenere dall’inlineamento delle funzioni può essere drammatico, e permettere la flessibilità e la leggibilità del codice DRY, senza un grande costo di prestazioni.