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

下面举几个例子，以防万一:

内联表值

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

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

读到它们，它们的优点/区别并没有真正被解释清楚。

218338 次浏览
小开

我认为你的例子很好地回答了这个问题。第一个函数可以作为一个单独的选择来完成，这是使用内联样式的一个很好的理由。第二种方法可能可以作为一条语句来完成(使用子查询来获得最大日期)，但一些编码器可能会发现，像您这样在多条语句中完成它更容易阅读或更自然。有些函数不能在一条语句中完成，因此需要多语句版本。

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

小开

如果你要做一个查询，你可以在你的内联表值函数中加入:

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次，因此随着结果集变得越来越大，它将运行得越来越慢。

小开
最佳答案

在研究马特的评论时，我修改了我原来的陈述。他是正确的，即使内联表值函数(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

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

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

多亏了马特。

除了

因为我最近看到了这个问题，下面是Wayne Sheffield做的一个很好的分析，比较了内联表值函数和多语句函数之间的性能差异。

他的博客原文

Copy on SQL Server Central

小开

在内部，SQL Server像对待视图一样对待内联表值函数，像对待存储过程一样对待多语句表值函数。

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

对于多语句表值函数，为函数本身创建执行计划并存储在执行计划缓存中(函数第一次执行后)。如果多语句表值函数被用作较大查询的一部分，那么优化器不知道函数返回什么，因此会做出一些标准假设——实际上，它假设函数将返回单行，并且通过对单行表进行表扫描来访问函数的返回。

当多语句表值函数返回大量行并在外部查询中连接时，它们的性能可能会很差。性能问题主要是由于这样一个事实:优化器将生成一个假设返回一行的计划，而这个计划不一定是最合适的计划。

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

小开

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

小开

看看比较内联和多语句表值函数，你可以找到很好的描述和性能基准

小开

我没有对此进行测试，但是一个多语句函数缓存了结果集。可能在某些情况下，优化器要内联函数的事情太多。例如，假设您有一个函数，根据您传递的“公司编号”从不同的数据库返回结果。通常情况下，你可以创建一个带有工会的视图，然后根据公司编号进行过滤，但我发现有时sql server会拉回整个工会，并且不够智能，无法调用一个选择。表函数可以具有选择源的逻辑。

小开

使用多行函数的另一种情况是避免sql server下推where子句。

例如，我有一个表，有一个表名，一些表名的格式是C05_2019和C12_2018，所有这样格式化的表都有相同的模式。我想将所有数据合并到一个表中，并将05和12解析为一个CompNo列，将2018、2019解析为一个年份列。然而，还有其他像ACA_StupidTable这样的表，我不能提取CompNo和CompYr，如果我尝试了，会得到一个转换错误。所以，我的查询分为两部分，内部查询只返回像'C_______'这样的表，然后外部查询做了子字符串和int转换。将(Substring(2,2) as int)转换为CompNo。一切看起来都很好，除了sql server决定在结果被过滤之前把我的Cast函数，所以我得到了一个思想扰乱转换错误。多语句表函数可以防止这种情况发生，因为它基本上是一个“新”表。

小开

也许是以一种非常浓缩的方式。 ITVF(内联TVF):如果你是DB人，是一种参数化的视图，取一个SELECT st

MTVF(多语句TVF):开发人员，创建并加载表变量。


提交答案