Vs de fonction Table multi-instructions évalué fonction de Table Inline

Question

Quelques exemples pour montrer, juste au cas où:

Inline Table

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 Déclaration De La Table

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

Est-il un avantage à l'utilisation d'un seul type (c'est à dire ligne ou multi déclaration) sur l'autre? Est-t-il des scénarios lorsque l'une est meilleure que l'autre ou les différences sont purement syntaxiques? Je me rends compte que les 2 exemples de requêtes sont en train de faire différentes choses, mais est-il une raison pour laquelle je voudrais écrire de cette façon?

Lire à leur sujet, et les avantages et les différences n'ont pas vraiment été expliqué.

Merci

Answer 1

Dans la recherche de Matt commentaire, j'ai révisé mon original de la déclaration. Il est correct, il y aura une différence de performances entre une ligne de la fonction table (ITVF) et un multi-déclaration de la fonction table (MSTVF) même si ils simplement exécuter une instruction SELECT. SQL Server traiter une ITVF un peu comme un VIEW en ce qu'elle permettra de calculer un plan d'exécution en utilisant les statistiques les plus récentes sur les tableaux en question. Un MSTVF est équivalent à la farce de la totalité du contenu de votre instruction SELECT dans une table variable, puis de rejoindre. Ainsi, le compilateur ne peut pas utiliser les statistiques de la table sur les tables de la MSTVF. Donc, toutes choses étant égales par ailleurs, (ce qui arrive rarement), l'ITVF sera mieux que la MSTVF. Dans mes tests, la différence de performances en temps de réalisation a été négligeable, cependant, d'un point de vue statistique, il a été perceptible.

Dans votre cas, les deux fonctions ne sont pas fonctionnellement équivalent. La ZONE de fonction ne une requête supplémentaire à chaque fois qu'il est appelé et, plus important encore, les filtres sur l'id de client. Dans une grande requête, l'optimiseur ne serait pas en mesure de tirer parti d'autres types de jointures, comme il serait nécessaire d'appeler la fonction pour chaque customerId passé. Toutefois, si vous ré-écrit votre ZONE de fonction comme ceci:

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

Dans une requête, l'optimiseur va être en mesure d'appeler cette fonction à la fois et de construire un meilleur plan d'exécution, mais il ne serait pas encore mieux qu'un équivalent, non paramétrée ITVS ou un VIEW.

ITVFs doit être préférée à une MSTVFs lorsque cela est possible parce que les types de données, la possibilité de valeur null et classement dans les colonnes de la table alors que vous déclarez ces propriétés dans un environnement multi-déclaration de la fonction à valeur de table et, surtout, vous obtiendrez de meilleurs plans d'exécution de l'ITVF. Dans mon expérience, je n'ai pas trouvé de nombreuses circonstances où un ITVF était une meilleure option que d'un point de VUE, mais le kilométrage peut varier.

Merci à Matt.

Plus

Depuis que j'ai vu ce sont en place récemment, voici une excellente analyse faite par Wayne Sheffield en comparant la différence de performances entre Inline fonctions à valeur de Table et Multi-Déclaration de fonctions.

Son blog original.

Copie sur SQL Serveur Central

Answer 2

En interne, SQL Server traite une ligne de la fonction table comme il l'aurait vue et traite un multi-déclaration de la fonction à valeur de table similaire à la façon dont il serait une procédure stockée.

Lorsqu'une fonction table en ligne est utilisé dans le cadre d'une requête externe, le processeur de requêtes développe l'UDF définition et génère un plan d'exécution qui accède à des objets sous-jacents, à l'aide de l'index sur ces objets.

Pour un multi-déclaration de la fonction à valeur de table, un plan d'exécution est créée pour la fonction elle-même et stockées dans le cache de plan d'exécution (une fois que la fonction a été exécutée la première fois). Si le multi-déclaration de la table des fonctions utilisées dans le cadre des grandes des requêtes, l'optimiseur ne sais pas ce que la fonction retourne, et fait en sorte que certains des hypothèses standard - en effet, il suppose que la fonction retourne une seule ligne, et que le rendement de la fonction sera accessible à l'aide d'une analyse de table sur une table avec une seule ligne.

Où multi-déclaration de fonctions à valeur de table peuvent exécuter mal, c'est quand ils reviennent d'un grand nombre de lignes, et sont reliés à l'encontre de requêtes externes. Les problèmes de performances sont principalement le fait que l'optimiseur va produire un plan en supposant qu'une seule ligne est renvoyé, ce qui ne sera pas nécessairement la plus appropriée le plan.

Comme une règle générale, nous avons constaté que, si possible inline fonctions à valeur de table doit être utilisée de préférence à multi-déclaration (lorsque l'UDF sera utilisé dans le cadre d'une requête externe) en raison de ces problèmes de performances potentiels.

Answer 3

Il y a une autre différence. Une fonction de table inline peut être insérée dans, mis à jour et supprimée de - juste comme un point de vue. Des restrictions similaires s’appliquent - ne peut pas mise à jour des fonctions à l’aide de granulats, ne peut pas mettre à jour les colonnes calculées et ainsi de suite.

Answer 4

Vos exemples, je pense, répondre à la question très bien. La première fonction peut être fait comme une sélection unique, et est une bonne raison pour utiliser le style en ligne. La seconde pourrait probablement être fait en une seule instruction (à l'aide d'une sous-requête pour obtenir le max de date), mais certains codeurs peuvent trouver plus facile de lire ou de plus naturel de le faire dans plusieurs états comme vous l'avez fait. Certaines fonctions de la plaine ne peut pas se faire en une seule instruction, et exigent le multi-compte de résultat.

Je suggère d'utiliser le plus simple (inline) chaque fois que possible, et en utilisant le multi-états lorsque c'est nécessaire (bien évidemment) ou lors de vos préférences personnelles et la lisibilité rend wirth la saisie de texte supplémentaire.

Answer 5

Si vous allez faire une requête, vous pouvez joindre dans votre fonction Inline Table évalués comme :

Il va subir une charge peu et fonctionnera parfaitement.

Si vous essayez d’utiliser, vous êtes le Multi instruction tableau précieux dans une requête similaire, vous aurez des problèmes de performances :

car vous ne s’exécutera la fonction 1 fois pour chaque ligne retournée, comme le jeu de résultats est grand, il fonctionnera plus lentement et plus lent.