多语句表值函数与内联表值函数 [英] Multi-statement Table Valued Function vs Inline Table Valued Function

问题描述

举几个例子，以防万一:

A few examples to show, just incase:

内联表值

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

多语句表值

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

使用一种类型(内联或多语句)比另一种有优势吗?是否存在某种情况下一种优于另一种的情况，或者差异纯粹是句法上的?我意识到这两个示例查询正在做不同的事情，但我是否有理由以这种方式编写它们?

Is there an advantage to using one type (in-line or multi statement) over the other? Is there certain scenarios when one is better than the other or are the differences purely syntactical? I realise the two example queries are doing different things but is there a reason I would write them in that way?

阅读有关它们以及优点/差异的内容尚未真正得到解释.

Reading about them and the advantages/differences haven't really been explained.

推荐答案

在研究 Matt 的评论时，我修改了我的原始声明.他是对的，内联表值函数 (ITVF) 和多语句表值函数 (MSTVF) 之间的性能会有差异，即使它们都只是执行 SELECT 语句.SQL Server 将 ITVF 有点像 VIEW，因为它将使用有关表的最新统计信息计算执行计划.MSTVF 相当于将 SELECT 语句的全部内容填充到表变量中，然后加入该变量.因此，编译器不能对 MSTVF 中的表使用任何表统计信息.因此，在所有条件相同的情况下(它们很少如此)，ITVF 的性能将优于 MSTVF.在我的测试中，完成时间的性能差异可以忽略不计，但从统计的角度来看，这是显而易见的.

In researching Matt's comment, I have revised my original statement. He is correct, there will be a difference in performance between an inline table valued function (ITVF) and a multi-statement table valued function (MSTVF) even if they both simply execute a SELECT statement. SQL Server will treat an ITVF somewhat like a VIEW in that it will calculate an execution plan using the latest statistics on the tables in question. A MSTVF is equivalent to stuffing the entire contents of your SELECT statement into a table variable and then joining to that. Thus, the compiler cannot use any table statistics on the tables in the MSTVF. So, all things being equal, (which they rarely are), the ITVF will perform better than the MSTVF. In my tests, the performance difference in completion time was negligible however from a statistics standpoint, it was noticeable.

就您而言，这两个函数在功能上并不等效.MSTV 函数在每次调用时都会执行一次额外的查询，最重要的是，对客户 ID 进行过滤.在大型查询中，优化器将无法利用其他类型的连接，因为它需要为每个传递的 customerId 调用该函数.但是，如果您像这样重写 MSTV 函数:

In your case, the two functions are not functionally equivalent. The MSTV function does an extra query each time it is called and, most importantly, filters on the customer id. In a large query, the optimizer would not be able to take advantage of other types of joins as it would need to call the function for each customerId passed. However, if you re-wrote your MSTV function like so:

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

在查询中，优化器将能够调用该函数一次并构建更好的执行计划，但它仍然不会比等效的非参数化 ITVS 或 VIEW 好.

In a query, the optimizer would be able to call that function once and build a better execution plan but it still would not be better than an equivalent, non-parameterized ITVS or a VIEW.

在可行的情况下，ITVF 应优先于 MSTVF，因为表中列的数据类型、可空性和排序规则，而您在多语句表值函数中声明这些属性，重要的是，您将从表中获得更好的执行计划国际电视基金会.根据我的经验，我没有发现很多情况下 ITVF 比 VIEW 更好，但里程可能会有所不同.

ITVFs should be preferred over a MSTVFs when feasible because the datatypes, nullability and collation from the columns in the table whereas you declare those properties in a multi-statement table valued function and, importantly, you will get better execution plans from the ITVF. In my experience, I have not found many circumstances where an ITVF was a better option than a VIEW but mileage may vary.

感谢马特.

添加

因为我最近看到了这个问题，这是 Wayne Sheffield 对内联表值函数和多语句函数之间的性能差异进行的一项出色的分析.

Since I saw this come up recently, here is an excellent analysis done by Wayne Sheffield comparing the performance difference between Inline Table Valued functions and Multi-Statement functions.

他的原始博客文章.

复制在 SQL Server Central 上

这篇关于多语句表值函数与内联表值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！