Scribnasium

Avrete forse sentito dire che le funzioni definite dall’utente di SQL Server sono il diavolo. Questo perché qualcuno è rimasto scottato. Hanno messo una funzione scalare in una clausola WHERE su una grande tabella e le hanno dato un nome di colonna. E sono successe brutte cose.

In questo caso, “male” significa lento. Molto lento.

Sarga-What-Now

Per fare in modo che le query funzionino bene, SQL Server deve essere in grado di trovare i dati velocemente, e questo di solito significa usare un indice. Un indice è una lista di dati chiave di una tabella, organizzata per rendere la ricerca più veloce. Proprio come in un libro, invece di scannerizzare ogni pagina per una menzione di una parola, puoi usare l’indice, dove l’autore – o il suo programma per computer – ha fatto quel lavoro per te come un comodo riferimento.

Un indice è più veloce quando lo puoi usare direttamente. Se stai cercando delle pagine che parlano di “SQL”, trovi “SQL” nell’indice e ti dice i numeri di pagina da controllare. Ma cosa succede se stai cercando tutte le parole che finiscono in “SQL”, come “T-SQL”? Ora non puoi andare direttamente alla parte dell’indice che vuoi. Devi fare la scansione di tutto l’indice. Non hai più un singolo argomento di ricerca (“SQL”); hai un’espressione di ricerca (“finisce in SQL”). Il termine industriale che usiamo è che la tua ricerca non è più “sargable” (“Search ARGument ABLE”).

La morale della storia è che se vuoi usare un indice nel modo più efficace, assicurati di cercare esattamente su ciò che viene indicizzato. Se stai cercando per data, e vuoi trovare tutto in un certo anno, non usare WHERE Year() = 2018". Invece, usate WHERE >= '20180101' AND . As a human, you can understand these as the same thing, but often the optimizer just sees "arbitrary function on indexed column" and figures it can't use the index the normal way but instead has to slog through the whole thing and run the function to see what it does.

E se l’ottimizzatore ha questo problema con la funzione integrata “Anno”, cosa pensate che farà con il vostro scalare “Complex_2000_Line_Monstrosity”? Lo tratterà come la scatola nera che è e passerà attraverso ogni voce dell’indice per testarlo. E questo presuppone che ci sia un indice. Una grande funzione definita dall’utente ha spesso bisogno di diverse colonne di dati, alcuni dei quali non sono in un indice. Quindi ora stai scansionando l’intera tabella. Prenditi una tazza di caffè.

Puoi vedere attraverso di loro.

Mentre le funzioni scalari sono opache per l’ottimizzatore, le funzioni in linea sono trasparenti. Invece di vedere una funzione in linea come una scatola nera, l’ottimizzatore di query la vede piuttosto come una vista parametrizzata– cioè, una vista che accetta parametri. Le viste e le funzioni in linea sono essenzialmente copiate e incollate nella query che le chiama, come se non fossero affatto un costrutto separato.

Ora solo perché i confini della vostra funzione svaniscono nella terra degli ottimizzatori non significa che troverete la vostra ricerca. Non avete bisogno di una funzione per distruggere la saraggine. Semplicemente riorganizzando la vostra logica dei criteri potete gestirla da soli. Per esempio, non otterrete il beneficio del vostro indice su Column1 se usate WHERE 1 = CASE WHEN Column1 = @value THEN 1 ELSE 0 END invece di WHERE Column1 = @value. Di nuovo, noi umani possiamo vedere che è la stessa cosa, ma SQL Server non lo fa.

Ma se usate una funzione in linea che agisce direttamente su un campo indicizzato, senza un calcolo offuscante, l’ottimizzatore userà ancora una ricerca indicizzata. Il modo in cui farlo accadere sembra un po’ più arte che scienza.

Considera una tabella Orders con una colonna OrderID, una colonna OrderStatusID e una colonna OrderDate. I OrderID e OrderDate sono irrilevanti per i nostri esempi tranne che per fornire alcuni dati aggiuntivi relativi all’ordine. La colonna chiave qui è OrderStatusID. Diciamo che abbiamo molti stati in cui un ordine potrebbe trovarsi (Nuovo, In elaborazione, Spedizione, Pagato, ecc.).

Ora diciamo che abbiamo una regola di business che si applica solo in alcuni stati. Forse un ordine è gestito da un certo dipartimento solo se è Nuovo o in Elaborazione ma non quando è in altri stati. Se mettete questa logica di business nel database, come dovreste, il vostro codice del database avrà bisogno di capire quali stati sono speciali.

Il modo più efficiente in termini di prestazioni per interrogare la tabella Ordini per gli ordini di stato speciale è di elencarli direttamente in ogni query pertinente:

SELECTo.*FROM#Orders oWHEREo.OrderStatusID IN (3,8);

Con un indice sul campo OrderStatusID, l’ottimizzatore userebbe probabilmente un’azione di ricerca dell’indice e troverebbe le tue righe in un batter d’occhio. Tuttavia, probabilmente avrete bisogno che il database capisca questo concetto in molti posti diversi. Piuttosto che ripetere questa lista di “stati speciali”, decidete che volete una funzione per calcolare se uno stato è speciale. In questo modo la vostra logica di business è centralizzata:

CREATE FUNCTION IsOrderStatusSpecialScalar(@OrderStatusID int)RETURNS bit ASBEGINRETURN(CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END);

Ora ogni volta che volete trovare questi stati speciali, basta usare la funzione.

SELECTo.*FROM#Orders oWHEREdbo.IsOrderStatusSpecialScalar(o.OrderStatusID) = 1;

Ahimè, si trova una scansione lenta dell’indice perché l’ottimizzatore non sa cosa succede dentro la funzione. La chiama semplicemente più e più volte per ogni riga.

Potresti pensare di poter salvare le tue prestazioni usando una funzione in linea.

CREATE FUNCTION IsOrderStatusSpecial(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderStatusIsSpecial =CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END;

Devi cambiare un po’ l’uso, ma è ugualmente conciso.

SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecial(o.OrderStatusID) sWHEREs.OrderStatusIsSpecial = 1;

Purtroppo, ci si imbatte nello stesso problema di prestazioni di scansione dell’indice. Non si può davvero incolpare la funzione inline, però. Dopo tutto, si ottengono le stesse scarse prestazioni usando direttamente la logica CASEWHEN della funzione:

SELECTo.*FROM#Orders oWHERE1 = CASEWHEN o.OrderStatusID IN (3,8) THEN1ELSE0END;

Si deve quindi trovare un modo per utilizzare una funzione inline in cui l’ottimizzatore veda un uso più diretto del campo indicizzato. Quindi si crea una funzione inline che non usa alcun calcolo per produrre il suo bit.

CREATE FUNCTION IsOrderStatusSpecialLimiter(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderIsSpecial = CONVERT(bit,1)WHERE@OrderStatusID IN (3,8);

SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecialLimiter(o.OrderStatusID) s;

CREATE FUNCTION GetSpecialOrderStatusIDs()RETURNS TABLE ASRETURNSELECTs.OrderStatusIDFROM(SELECT3UNION ALLSELECT8) AS s(OrderStatusID);

Non stai nemmeno passando nulla in questa funzione. In effetti, potresti anche usare una semplice vecchia vista. E la useresti in modo più tradizionale, senza quel nuovo operatore APPLY.

SELECTo.*FROM#Orders oWHEREEXISTS(SELECT*FROMGetSpecialOrderStatusIDs() sWHEREs.OrderStatusID = o.OrderStatusID);

Questo è un altro esempio di utilizzo di una funzione in linea senza sacrificare le prestazioni.

Hide and No Seek

Quindi no, le funzioni definite dall’utente non sono il diavolo. Le funzioni scalari definite dall’utente possono causare grossi problemi se usate male, ma generalmente le funzioni definite dall’utente in linea non causano problemi.

La vera regola generale non è quella di evitare le funzioni, ma piuttosto di evitare di adornare i vostri campi indice con logica o funzioni. Perché quando si nascondono le proprie intenzioni all’ottimizzatore con una sintassi complessa, si rischia di non ottenere un indice più performante.