webentwicklung-frage-antwort-db.com.de

Tabellenwertfunktion mit mehreren Anweisungen vs Inline-Tabellenwertfunktion

Ein paar Beispiele zur Veranschaulichung:

Inline Table Valued

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

Multi Statement Table Valued

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

Gibt es einen Vorteil bei der Verwendung eines Typs (Inline- oder Multi-Anweisung) gegenüber dem anderen? Gibt es bestimmte Szenarien, in denen eines besser ist als das andere, oder sind die Unterschiede rein syntaktisch? Mir ist klar, dass die beiden Beispielabfragen unterschiedliche Aufgaben haben, aber gibt es einen Grund, warum ich sie so schreibe?

Das Lesen über sie und die Vorteile/Unterschiede wurde nicht wirklich erklärt.

184
AndrewC

Bei der Untersuchung von Matts Kommentar habe ich meine ursprüngliche Aussage überarbeitet. Er hat Recht, es gibt einen Unterschied in der Leistung zwischen einer Inline-Tabellenwertfunktion (ITVF) und einer Tabellenwertfunktion mit mehreren Anweisungen (MSTVF), selbst wenn beide einfach eine SELECT-Anweisung ausführen. SQL Server behandelt eine ITVF insofern wie eine VIEW, als es einen Ausführungsplan unter Verwendung der neuesten Statistiken für die fraglichen Tabellen berechnet. Ein MSTVF entspricht dem Füllen des gesamten Inhalts Ihrer SELECT-Anweisung in eine Tabellenvariable und dem anschließenden Verknüpfen mit dieser. Daher kann der Compiler keine Tabellenstatistiken für die Tabellen im MSTVF verwenden. Wenn also alle Dinge gleich sind (was selten der Fall ist), ist die Leistung der ITVF besser als die der MSTVF. In meinen Tests war der Leistungsunterschied in Bezug auf die Fertigstellungszeit vernachlässigbar, jedoch unter statistischen Gesichtspunkten bemerkbar.

In Ihrem Fall sind die beiden Funktionen nicht funktional gleichwertig. Die MSTV-Funktion führt bei jedem Aufruf eine zusätzliche Abfrage durch und filtert vor allem nach der Kunden-ID. In einer großen Abfrage kann das Optimierungsprogramm andere Join-Typen nicht nutzen, da die Funktion für jede übergebene Kunden-ID aufgerufen werden muss. Wenn Sie Ihre MSTV-Funktion jedoch wie folgt neu geschrieben haben:

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 einer Abfrage könnte das Optimierungsprogramm diese Funktion einmal aufrufen und einen besseren Ausführungsplan erstellen, aber es wäre immer noch nicht besser als ein äquivalentes, nicht parametrisiertes ITVS oder ein VIEW.

ITVFs sollten nach Möglichkeit MSTVFs vorgezogen werden, da die Datentypen, die Nullwertfähigkeit und die Sortierung aus den Spalten in der Tabelle stammen, während Sie diese Eigenschaften in einer Tabellenwertfunktion mit mehreren Anweisungen deklarieren und vor allem bessere Ausführungspläne von der ITVF erhalten. Nach meiner Erfahrung habe ich nicht viele Umstände festgestellt, in denen eine ITVF eine bessere Option als eine VIEW war, die Laufleistung jedoch variieren kann.

Vielen Dank an Matt.

Addition

Da ich dies kürzlich gesehen habe, ist hier eine ausgezeichnete Analyse von Wayne Sheffield, die den Leistungsunterschied zwischen Inline Table Valued-Funktionen und Multi-Statement-Funktionen vergleicht.

Sein ursprünglicher Blogbeitrag.

Auf SQL Server Central kopieren

134
Thomas

Intern behandelt SQL Server eine Inline-Tabellenwertfunktion ähnlich wie eine Ansicht und eine Tabellenwertfunktion mit mehreren Anweisungen ähnlich wie eine gespeicherte Prozedur.

Wenn eine Inline-Tabellenwertfunktion als Teil einer äußeren Abfrage verwendet wird, erweitert der Abfrageprozessor die UDF-Definition und generiert einen Ausführungsplan, der unter Verwendung der Indizes für diese Objekte auf die zugrunde liegenden Objekte zugreift.

Für eine Funktion mit mehreren Anweisungen und Tabellenwerten wird ein Ausführungsplan für die Funktion selbst erstellt und im Ausführungsplan-Cache gespeichert (sobald die Funktion zum ersten Mal ausgeführt wurde). Wenn Funktionen mit mehreren Anweisungen und Tabellenwerten als Teil größerer Abfragen verwendet werden, weiß das Optimierungsprogramm nicht, was die Funktion zurückgibt, und nimmt daher einige Standardannahmen an. In der Tat wird davon ausgegangen, dass die Funktion eine einzelne Zeile zurückgibt und die Rückgabe von Auf die Funktion wird zugegriffen, indem ein Tabellenscan für eine Tabelle mit einer einzelnen Zeile verwendet wird.

Funktionen mit mehreren Anweisungen und Tabellenwerten können schlecht ausgeführt werden, wenn sie eine große Anzahl von Zeilen zurückgeben und in äußeren Abfragen miteinander verknüpft werden. Die Leistungsprobleme sind in erster Linie darauf zurückzuführen, dass das Optimierungsprogramm einen Plan erstellt, bei dem davon ausgegangen wird, dass eine einzelne Zeile zurückgegeben wird. Dies ist nicht unbedingt der am besten geeignete Plan.

Als allgemeine Faustregel haben wir festgestellt, dass Inline-Tabellenfunktionen aufgrund dieser potenziellen Leistungsprobleme nach Möglichkeit gegenüber Funktionen mit mehreren Anweisungen vorzuziehen sind (wenn die UDF als Teil einer äußeren Abfrage verwendet wird).

27
Paul McLoughlin

Es gibt noch einen anderen Unterschied. Eine Inline-Tabellenwertfunktion kann wie eine Ansicht in eine Ansicht eingefügt, daraus aktualisiert und daraus gelöscht werden. Ähnliche Einschränkungen gelten - Funktionen, die Aggregate verwenden, können nicht aktualisiert werden, berechnete Spalten können nicht aktualisiert werden usw.

13
Craig Beere

Ich denke, Ihre Beispiele beantworten die Frage sehr gut. Die erste Funktion kann als Einzelauswahl ausgeführt werden und ist ein guter Grund, den Inline-Stil zu verwenden. Die zweite könnte wahrscheinlich als einzelne Anweisung ausgeführt werden (mithilfe einer Unterabfrage, um das maximale Datum zu erhalten), aber einige Programmierer finden es möglicherweise einfacher zu lesen oder natürlicher, sie in mehreren Anweisungen auszuführen, wie Sie es getan haben. Einige Funktionen können einfach nicht in einer Anweisung ausgeführt werden und erfordern daher die Version mit mehreren Anweisungen.

Ich schlage vor, wann immer möglich das Einfachste (Inline) zu verwenden und bei Bedarf (offensichtlich) Mehrfachaussagen zu verwenden, oder wenn die persönliche Präferenz/Lesbarkeit dies mit der zusätzlichen Typisierung verbindet.

3
Ray

schauen Sie sich an. Beim Vergleich von Inline- und Tabellenwertfunktionen mit mehreren Anweisungen finden Sie gute Beschreibungen und Leistungsbenchmarks

0
hmfarimani

Ein weiterer Fall für die Verwendung einer mehrzeiligen Funktion wäre, zu verhindern, dass SQL Server die WHERE-Klausel drückt.

Zum Beispiel habe ich eine Tabelle mit Tabellennamen und einige Tabellennamen sind wie C05_2019 und C12_2018 formatiert und alle so formatierten Tabellen haben dasselbe Schema. Ich wollte all diese Daten in einer Tabelle zusammenführen und 05 und 12 in eine CompNo-Spalte und 2018,2019 in eine Jahresspalte zerlegen. Es gibt jedoch auch andere Tabellen wie ACA_StupidTable, bei denen ich CompNo und CompYr nicht extrahieren kann und bei einem Versuch einen Konvertierungsfehler erhalte. Meine Abfrage bestand also aus zwei Teilen, einer inneren Abfrage, die nur Tabellen zurückgab, die wie "C_______" formatiert waren. Die äußere Abfrage führte dann eine Unterzeichenfolge- und eine Int-Konvertierung durch. dh Cast (Teilstring (2, 2) as int) as CompNo. Alles sieht gut aus, außer dass SQL Server beschlossen hat, meine Cast-Funktion zu verwenden, bevor die Ergebnisse gefiltert wurden, und ich daher einen irritierenden Konvertierungsfehler erhalte. Eine Tabellenfunktion mit mehreren Anweisungen kann dies verhindern, da es sich im Grunde um eine "neue" Tabelle handelt.

0
William Egge

Ich habe dies nicht getestet, aber eine Funktion mit mehreren Anweisungen speichert die Ergebnismenge zwischen. Es kann Fälle geben, in denen zu viel passiert, als dass der Optimierer die Funktion einbinden könnte. Angenommen, Sie haben eine Funktion, die ein Ergebnis aus verschiedenen Datenbanken zurückgibt, je nachdem, was Sie als "Firmennummer" übergeben. Normalerweise könnten Sie eine Ansicht mit einer Union alle erstellen und dann nach Firmennummer filtern, aber ich stellte fest, dass SQL Server manchmal die gesamte Union zurückzieht und nicht klug genug ist, um die ausgewählte zu nennen. Eine Tabellenfunktion kann über eine Logik zur Auswahl der Quelle verfügen.

0
William Egge