Funzione valutata tabella con più istruzioni e funzione con valore in tabella

Alcuni esempi da mostrare, solo in caso di:

Tabella in linea stimata

CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO 

Tabella multipla stimata

 CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder TABLE (SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL) AS BEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxDate AND a.CustomerID = @CustomerID RETURN END GO 

C’è un vantaggio nell’usare un tipo (in-line o multi statement) rispetto all’altro? Esistono alcuni scenari in cui uno è migliore dell’altro o le differenze sono puramente sintattiche? Mi rendo conto che le due query di esempio stanno facendo cose diverse ma c’è un motivo per scriverle in quel modo?

Leggere su di loro e i vantaggi / le differenze non sono stati davvero spiegati.

Nella ricerca del commento di Matt, ho rivisto la mia dichiarazione originale. È corretto, ci sarà una differenza di prestazioni tra una funzione di stima della tabella inline (ITVF) e una funzione di stima di tabelle con più istruzioni (MSTVF) anche se entrambi eseguono semplicemente un’istruzione SELECT. SQL Server tratterà un ITVF in qualche modo come una VIEW in quanto calcolerà un piano di esecuzione utilizzando le ultime statistiche sulle tabelle in questione. Un MSTVF equivale a riempire l’intero contenuto dell’istruzione SELECT in una variabile di tabella e quindi a unirsi a esso. Pertanto, il compilatore non può utilizzare alcuna statistica di tabella sulle tabelle nel MSTVF. Quindi, a parità di condizioni (che raramente sono), l’ITVF funzionerà meglio del MSTVF. Nei miei test, la differenza di prestazioni nei tempi di completamento era trascurabile, tuttavia dal punto di vista delle statistiche, era evidente.

Nel tuo caso, le due funzioni non sono equivalenti dal punto di vista funzionale. La funzione MSTV esegue una query aggiuntiva ogni volta che viene chiamata e, soprattutto, filtri sull’ID cliente. In una query di grandi dimensioni, l’ottimizzatore non sarebbe in grado di sfruttare gli altri tipi di join in quanto sarebbe necessario chiamare la funzione per ogni ID cliente passato. Tuttavia, se hai riscritto la tua funzione MSTV in questo modo:

 CREATE FUNCTION MyNS.GetLastShipped() RETURNS @CustomerOrder TABLE ( SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL ) AS BEGIN INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = ( Select Max(SH1.OrderDate) FROM Sales.SalesOrderHeader As SH1 WHERE SH1.CustomerID = A.CustomerId ) RETURN END GO 

In una query, l’ottimizzatore sarebbe in grado di chiamare tale funzione una volta e creare un piano di esecuzione migliore, ma non sarebbe comunque migliore di un ITVS o VIEW equivalente, non parametrizzato.

Gli ITVF dovrebbero essere preferiti su un MSTVF quando fattibile perché i tipi di dati, il nullability e le regole di confronto dalle colonne nella tabella mentre si dichiarano tali proprietà in una funzione di tabella con più istruzioni e, soprattutto, si ottengono migliori piani di esecuzione dall’ITVF. Nella mia esperienza, non ho trovato molte circostanze in cui un ITVF era un’opzione migliore di una VISTA, ma il chilometraggio può variare.

Grazie a Matt.

aggiunta

Da quando l’ho visto di recente, ecco un’eccellente analisi svolta da Wayne Sheffield che confronta la differenza di prestazioni tra le funzioni della tabella in linea e le funzioni multi-statement.

Il suo post sul blog originale.

Copia su SQL Server Central

Internamente, SQL Server considera una funzione di valore della tabella incorporata come se fosse una visualizzazione e considera una funzione con valori di tabelle a più istruzioni simile a quella di una stored procedure.

Quando una funzione valutata inline viene utilizzata come parte di una query esterna, il processore di query espande la definizione UDF e genera un piano di esecuzione che accede agli oggetti sottostanti, utilizzando gli indici su questi oggetti.

Per una funzione con valore di tabella con più istruzioni, viene creato un piano di esecuzione per la funzione stessa e memorizzato nella cache del piano di esecuzione (una volta che la funzione è stata eseguita per la prima volta). Se le funzioni con valori di tabella a più istruzioni vengono utilizzate come parte di query più ampie, l’ottimizzatore non sa cosa restituisce la funzione, e quindi fa alcune ipotesi standard – in effetti assume che la funzione restituirà una singola riga e che i ritorni di sarà ansible accedere alla funzione utilizzando una scansione della tabella su una tabella con una singola riga.

Quando le funzioni con valori di tabella a più istruzioni possono avere un rendimento scarso, quando restituiscono un numero elevato di righe e vengono associate contro le query esterne. I problemi di prestazioni dipendono principalmente dal fatto che l’ottimizzatore produrrà un piano assumendo che venga restituita una singola riga, che non sarà necessariamente il piano più appropriato.

Come regola generale, abbiamo scoperto che, ove ansible, le funzioni valutate in linea dovrebbero essere utilizzate preferibilmente rispetto a quelle multi-statement (quando l’UDF sarà usato come parte di una query esterna) a causa di questi potenziali problemi di prestazioni.

C’è un’altra differenza. È ansible inserire, aggiornare ed eliminare una funzione con valori di tabella incorporata, proprio come una vista. Restrizioni analoghe si applicano – non è ansible aggiornare le funzioni utilizzando gli aggregati, non è ansible aggiornare le colonne calcolate e così via.

I tuoi esempi, penso, rispondono molto bene alla domanda. La prima funzione può essere eseguita come una singola selezione ed è una buona ragione per utilizzare lo stile in linea. Il secondo potrebbe probabilmente essere fatto come una singola istruzione (usando una sotto-query per ottenere la data massima), ma alcuni programmatori potrebbero trovare più facile leggere o più naturale farlo in più affermazioni come hai fatto tu. Alcune funzioni semplicemente non possono essere eseguite in un’unica istruzione, e quindi richiedono la versione multi-statement.

Suggerisco di usare il più semplice (in linea) ogni volta che è ansible, e usando le multi-dichiarazioni quando necessario (ovviamente) o quando la preferenza personale / leggibilità rende ansible la digitazione extra.

guarda Confronto tra le funzioni con valori in tabella e con valori multipli in tabella puoi trovare descrizioni e benchmark delle prestazioni validi

se hai intenzione di fare una query puoi unirti alla tua funzione Inline Table Valued come:

 SELECT a.*,b.* FROM AAAA a INNER JOIN MyNS.GetUnshippedOrders() b ON az=bz 

incorrerà poco sopra la testa e correrà bene.

se si tenta di utilizzare la tabella Multi Statement valutata in una query simile, si avranno problemi di prestazioni:

 SELECT xa,xb,xc,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty FROM xxxx x 

poiché si eseguirà la funzione 1 volta per ogni riga restituita, poiché il set di risultati diventa grande, verrà eseguito più lentamente e più lentamente.