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

2021-09-10 00:00:00 sql tsql sql-server

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

I'm trying to make my query readable by using UDF in SQL SERVER but the run time increasing dramatically when using the function.

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

Following is the function I'm using:

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 个不同的领域.

for 20 different fields.

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

When using the function I'm getting run-time of 12.5 minutes while run-time of 45 seconds when not using the function.

有什么想法吗?

推荐答案

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

Taking John's idea one step further, converting the scalar function into an inline table function and using cross apply to activate it for each pair of columns - you might get an even better performance, for the price of a more cumbersome query:

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

相关文章