T-SQL UDF 与完整表达式运行时

Question

我试图通过在SQL SERVER中使用UDF使我的查询可读，但是使用该函数时运行时会急剧增加。

以下是我正在使用的功能：

create function DL.trim_all(@input varchar(max)) 
returns varchar(max)
as begin 
set @input=replace(replace(replace(@input,' ',''),')',''),'(','')
return @input
end

而不是写：

SELECT
CASE WHEN replace(replace(replace([FULL_NAME_1],' ',''),')',''),'(','')=replace(replace(replace([FULL_NAME_2],' ',''),')',''),'(','') THEN 1 ELSE 0 END AS [name_match],
CASE WHEN replace(replace(replace([ADDRESS_1],' ',''),')',''),'(','')=replace(replace(replace([ADDRESS_2],' ',''),')',''),'(','') THEN 1 ELSE 0 END AS [adrs_match]
.
.
.
FROM
TABLE_1

适用于 20 个不同的领域。

使用该函数时，我的运行时间为12.5 分钟，而不使用该函数时的运行时间为 45 秒。

有什么想法吗？

Answer 1

将 John 的想法更进一步，将标量函数转换为内联表函数，并使用 cross apply 为每对列激活它 - 您可能会获得更好的性能，但代价是更繁琐的查询：

CREATE function DL.DoesItMatch(@s1 varchar(500),@s2 varchar(500)) 
returns table -- returns a table with a single row and a single column
as return 
SELECT 
CASE WHEN replace(replace(replace(@s1,' ',''),')',''),'(','') = 
replace(replace(replace(@s2,' ',''),')',''),'(','') THEN 1 ELSE 0 END As IsMatch;    

和查询：

SELECT NameMatch.IsMatch AS [name_match],
AddressMatch.IsMatch AS adrs_match
.
.
.
FROM TABLE_1
CROSS APPLY DL.DoesItMatch(FULL_NAME_1, FULL_NAME_2) As NameMatch
CROSS APPLY DL.DoesItMatch(ADDRESS_1, ADDRESS_2) As AddressMatch

Answer 2

无法想象巨大的提升，但是另一种方法怎么样

create function DL.DoesItMatch(@s1 varchar(500),@s2 varchar(500)) 
returns bit
as begin 
return CASE WHEN replace(replace(replace(@s1,' ',''),')',''),'(','')=replace(replace(replace(@s2,' ',''),')',''),'(','') THEN 1 ELSE 0 END
end

然后调用该函数：

SELECT 
DL.DoesItMatch([FULL_NAME_1],[FULL_NAME_2])  AS [name_match],
...
FROM
TABLE_1

Answer 3

内联始终是要走的路。时期。即使不考虑 T-SQL 标量 UDF 的并行性抑制方面，ITVF 速度更快，需要的资源(CPU、内存和 IO)更少，更易于维护，并且更容易进行故障排除/分析/分析/跟踪。为了好玩，我做了一个性能测试，将Zohar的ITVF与John的标量UDF进行了比较。我创建了 250K 行，针对两者测试了一个基本选择，然后对堆进行了另一个测试，ORDER BY以强制排序。

示例数据：

-- Sample Data
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmp','U') IS NOT NULL DROP TABLE #tmp;
SELECT TOP (250000) col1 = '('+LEFT(NEWID(),10)+')', col2 = '('+LEFT(NEWID(),10)+')'
INTO    #tmp
FROM   sys.all_columns a, sys.all_columns;
UPDATE #tmp SET col1 = col2 WHERE LEFT(col1,2) = LEFT(col2,2) 
END

性能测试：

PRINT 'scalar, no sort'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT @isMatch = DL.DoesItMatch(t.col1,t.col2)
FROM   #tmp AS t;
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3
PRINT CHAR(10)+'ITVF, no sort'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT      @isMatch = f.isMatch
FROM        #tmp AS t
CROSS APPLY DL.DoesItMatch_ITVF(t.col1,t.col2) AS f;
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3    
PRINT CHAR(10)+'scalar, sorted set'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT @isMatch = DL.DoesItMatch(t.col1,t.col2)
FROM   #tmp AS t
ORDER BY DL.DoesItMatch(t.col1,t.col2);
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3
PRINT CHAR(10)+'ITVF, sorted set'+CHAR(10)+REPLICATE('-',60);
GO
DECLARE @st DATETIME = GETDATE(), @isMatch BIT;
SELECT      @isMatch = f.isMatch
FROM        #tmp AS t
CROSS APPLY DL.DoesItMatch_ITVF(t.col1,t.col2) AS f
ORDER BY    f.isMatch;
PRINT DATEDIFF(MS,@st,GETDATE())
GO 3

测试结果：

scalar, no sort
------------------------------------------------------------
Beginning execution loop
844
843
840
Batch execution completed 3 times.
ITVF, no sort
------------------------------------------------------------
Beginning execution loop
270
270
270
Batch execution completed 3 times.
scalar, sorted set
------------------------------------------------------------
Beginning execution loop
937
930
936
Batch execution completed 3 times.
ITVF, sorted set
------------------------------------------------------------
Beginning execution loop
196
190
190
Batch execution completed 3 times.

因此，当不需要并行计划时，ITVF 的速度提高了 3 倍，当需要并行计划时，它的速度提高了 5 倍。以下是我测试ITVF与(标量和多语句表值UDF)的其他一些链接。

在许多条件下，基于集的计划运行速度比标量值函数慢

SQL Server 用户定义的函数来计算年龄段

函数速度慢，但查询运行速度快

为什么SQL Server说这个函数是不确定的？

根据匹配百分比进行分组

SQL Server 2008 用户定义的函数，用于在每个数字之间添加空格 SQL 表逗号分隔值包含任何变量值检查

SQL 字符串操作，查找所有排列

Answer 4

> 您可以在SQL Server 2019中使用Scalar UDF内联。这样，您将能够保留您编写的相同 UDF，并自动获得与没有 UDF 的查询相同的性能。

您提供的 UDF 符合可内联性的标准，因此您的状态良好。有关 UDF 内联功能的文档如下：https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=azuresqldb-current

专业提示：我建议您在使用标量UDF内联之前对UDF进行小幅修改。通过避免局部变量，将其转换为单个语句标量 UDF。有了这个，您应该比使用带有交叉应用的内联 TVF 更好。