代码之家 › 专栏 › 技术社区 › AndrewC

多语句表值函数与内联表值函数

user-defined-functions tsql sql-server-2008 sql-server sql

177

AndrewC · 技术社区 · 14 年前

举几个例子,比如:

内联表值

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

使用一种类型(内联或多语句)比使用另一种类型有什么优势吗?当一个比另一个好时,是否存在某些情况,或者这些差异纯粹是句法上的?我意识到这两个示例查询做的是不同的事情,但是我这样写它们有什么原因吗?

关于它们和优势/差异的阅读并没有得到真正的解释。

6 回复 | 直到 9 年前

131

Thomas 12 年前

在研究马特的评论时,我修改了我原来的陈述。他是对的,内联表值函数(itvf)和多语句表值函数(mstvf)之间的性能将有所不同,即使它们都只是执行select语句。SQL Server将把ITVF看作 VIEW 其中,它将使用有关表的最新统计信息计算执行计划。mstvf相当于将select语句的全部内容填充到一个表变量中,然后加入到该表变量中。因此,编译器不能对mstvf中的表使用任何表统计信息。所以,在所有条件都相同的情况下(这是很少见的),itvf将比mstvf表现得更好。在我的测试中,完成时间的性能差异可以忽略不计,但是从统计学的角度来看,这是显而易见的。

在您的例子中,这两个函数在功能上并不等价。每次调用mstv函数时,它都会执行一个额外的查询,最重要的是,它会过滤客户id。在大型查询中,优化器将无法利用其他类型的连接,因为它需要为传递的每个customerid调用该函数。但是,如果您像这样重新编写mstv功能:

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

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

可行时,应首选itvfs而不是mstvfs,因为表中列的数据类型、可空性和排序规则,而您在多语句表值函数中声明这些属性,而且,重要的是,您将从itvf获得更好的执行计划。以我的经验,我没有发现很多情况下,itvf是一个更好的选择,而不是一个看法,但里程可能会有所不同。

多亏了马特。

加法

由于我最近看到了这一点,下面是韦恩谢菲尔德做的一个出色的分析,比较内联表值函数和多语句函数之间的性能差异。

His original blog post.

Copy on SQL Server Central

Paul McLoughlin 14 年前

在内部,sql server将内联表值函数视为视图,并将多语句表值函数视为存储过程。

当内联表值函数用作外部查询的一部分时,查询处理器将扩展UDF定义,并使用这些对象上的索引生成访问底层对象的执行计划。

对于多语句表值函数,将为函数本身创建一个执行计划,并存储在执行计划缓存中(函数第一次执行后)。如果多语句表值函数用作较大查询的一部分,那么优化程序不知道该函数返回什么,因此作出一些标准假设-实际上,它假设该函数将返回一行,并且该函数的返回将由对只有一行的表使用表扫描。

当多语句表值函数返回大量行并在外部查询中联接时,它们的性能可能很差。性能问题主要归结于这样一个事实,即乐观者将产生一个计划,假设返回一行,这不一定是最合适的计划。

根据一般经验,由于这些潜在的性能问题,我们发现在可能的情况下,应该优先使用内联表值函数,而不是多语句函数(当udf将用作外部查询的一部分时)。

Craig Beere 13 年前

还有另一个区别。内联表值函数可以像视图一样插入、更新和删除。应用类似的限制-不能使用聚合更新函数,不能更新计算列,等等。

Ray 14 年前

我认为,你的例子很好地回答了这个问题。第一个函数可以作为单个select完成,这是使用内联样式的一个很好的理由。第二个可能是作为一个单独的语句来完成的(使用子查询来获取最大日期),但是一些编码人员可能会发现读起来更容易,或者更自然地在多个语句中完成。一些简单的函数不能在一个语句中完成,因此需要多语句版本。

我建议尽可能使用最简单的(内联)语句,必要时(显然)使用多语句,或者当个人偏好/可读性使它与额外的类型匹配时。

hmfarimani 9 年前

看 Comparing Inline and Multi-Statement Table-Valued Functions 您可以找到良好的描述和性能基准

-2

KM. 14 年前

如果要执行查询,可以加入内联表值函数,如:

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

它只需要很少的开销,运行良好。

如果尝试在类似查询中使用多语句表值,则会出现性能问题:

SELECT
    x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
    FROM xxxx   x

因为您将对返回的每一行执行函数1次,当结果集变大时,它将运行得越来越慢。