为什么SQL Server标量值函数变慢?

在大多数情况下,最好避免使用引用表的标量值函数,因为(如其他人所说)它们基本上是黑盒,每行需要运行一次,并且不能由查询计划引擎优化。因此,即使相关表具有索引,它们也倾向于线性缩放。

您可能需要考虑使用一个内联表值函数,因为它们是用查询内联计算的,并且可以进行优化。您可以得到所需的封装,但可以在select语句中正确粘贴表达式。

作为内联的副作用,它们不能包含任何过程代码(no declare@variable;set@variable=;return)。但是,它们可以返回若干行和列。

您可以这样重新编写函数:

create function usf_GIS_GET_LAT(
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 lat
  from GIS_Location with (nolock) 
  where [State] = @State
    and [City] = @City
);

GO

create function usf_GIS_GET_LON (
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 LON
  from GIS_Location with (nolock)
  where [State] = @State
    and [City] = @City
);

使用它们的语法也略有不同:

select
    Lat.Lat,
    Lon.Lon
from
    Address_Location with (nolock)
    cross apply dbo.usf_GIS_GET_LAT(City,[State]) AS Lat
    cross apply dbo.usf_GIS_GET_LON(City,[State]) AS Lon
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)

他们没有。

标量函数中没有导致性能指数级下降的bug,这取决于对标量函数执行的行数。再次尝试您的测试,并查看SQL事件探查器、CPU、读取和持续时间列。增加测试大小以包括耗时超过一秒、两秒、五秒的测试。

CREATE FUNCTION dbo.slow
(
    @ignore int
)
RETURNS INT 
AS
BEGIN
    DECLARE @slow INT
    SET @slow = (select count(*) from sysobjects a 
        cross join sysobjects b 
        cross join sysobjects c 
        cross join sysobjects d 
        cross join sysobjects e 
        cross join sysobjects f
    where a.id = @ignore) 

    RETURN @slow
END
go
SET STATISTICS TIME ON

select top 1 dbo.slow(id)
from sysobjects
go
select top 5 dbo.slow(id)
from sysobjects
go
select top 10 dbo.slow(id)
from sysobjects
go
select top 20 dbo.slow(id)
from sysobjects
go
select top 40 dbo.slow(id)
from sysobjects

SET STATISTICS TIME OFF

产量

SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 202 ms.


SQL Server Execution Times:
   CPU time = 889 ms,  elapsed time = 939 ms.

SQL Server Execution Times:
   CPU time = 1748 ms,  elapsed time = 1855 ms.

SQL Server Execution Times:
   CPU time = 3541 ms,  elapsed time = 3696 ms.


SQL Server Execution Times:
   CPU time = 7207 ms,  elapsed time = 7392 ms.

请记住,如果对结果集中的行运行标量函数,则每行执行一个标量函数,而不进行全局优化。

您可以将您的功能包装在内联TVF中,这样会更快:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx

对结果集中的每一行调用函数两次(对数据库进行两次选择命中)。

要使您的查询更快地连接到地理信息系统的位置并跳过以下功能:

SELECT
    g.Lat,
    g.Lon
FROM
    Address_Location        l WITH(NOLOCK)
    INNER JOIN GIS_Location g WITH(NOLOCK) WHERE l.State = g.State AND l.City = g.City
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)

我不知道为什么我只是从问题中抄袭了nolock或crazy where子句…

简单地说,因为具有用户定义函数的SQL表达式比没有它们的SQL表达式效率低。无法优化执行逻辑;必须为每一行产生函数开销(包括调用协议)。

凯米克的建议很好。哪里。。in(select something)不太可能是一个有效的模式,在这种情况下,可以很容易地用join替换。

看看这是否更有效…或者是一个明显的内部连接?

select a.*,
(select top 1 g.Lat from GIS_Location g where g.City = a.City and g.State = a.State) as Lat,
(select top 1 g.Lon from GIS_Location g where g.City = a.City and g.State = a.State) as Lon
from Address_Location a
where a.ID in (select top 100 ID from Address_Location order by ID desc)

至于标量函数的性能,我不确定。

通常,标量函数比内联TVF对应函数慢得多。幸运的是,在许多情况下,它都会改变。

将引入SQL Server 2019 Scalar UDF Inlining :

智能查询处理功能套件下的功能。 此功能改进了在SQL Server中调用标量UDF的查询的性能(从SQL Server 2019预览版开始)

T-SQL标量用户定义函数

在Transact-SQL中实现并返回单个数据值的用户定义函数称为T-SQL标量用户定义函数。 T-SQL UDF是一种跨SQL查询实现代码重用和模块化的优雅方式。一些计算(例如复杂的业务规则)更容易用命令式UDF形式表示。UDF有助于构建复杂的逻辑,而无需专门编写复杂的SQL查询。

由于以下原因,标量UDF通常表现不佳。

• 迭代调用
• 成本核算不足
• 解释性执行
• 串行执行

---

标量UDF的自动内联

标量UDF内联特性的目标是提高调用T-SQL标量UDF的查询的性能,其中UDF执行是主要瓶颈。

有了这个新特性,标量UDF会自动转换成标量表达式或标量子查询,在调用查询中取代UDF运算符。然后优化这些表达式和子查询。 因此,查询计划将不再具有用户定义的函数运算符,但其效果将在计划中观察到,如视图或内联TVF。

---

内联标量UDF要求

如果满足以下所有条件,则可以内联标量T-SQL UDF 是真的:

• 使用以下构造编写UDF:

  1. declare,set:变量声明和赋值。
  2. 选择:带有单/多变量赋值的SQL查询1。
  3. if/else:使用任意嵌套级别进行分支。
  4. 返回:单个或多个返回语句。
  5. UDF:嵌套/递归函数调用2。
  6. 其他:关系操作,如exists、isnull。

• UDF不调用任何与时间相关(如getDate())或具有副作用3(如 newSequentialID())。

• UDF使用EXECUTEAS CALLER子句(如果未指定EXECUTEAS子句,则为默认行为)。
• UDF不引用表变量或表值参数。
• 调用标量UDF的查询未引用其group by子句中的标量UDF调用。
• UDF不是本机编译的(支持互操作)。
• 在计算列或检查约束定义中未使用UDF。
• UDF不引用用户定义的类型。
• 没有添加到UDF的签名。
• UDF不是分区函数。

检查功能是否不可输入:

SELECT OBJECT_NAME([object_id]) AS name, is_inlineable
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('schema.function_name')

在数据库级别启用/禁用功能:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON/OFF;