問題描述

我試圖通過在 SQL SERVER 中使用 UDF 使我的查詢可讀，但使用該函數(shù)時運行時間會急劇增加.

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.

使用該函數(shù)時，我的運行時間為 12.5 分鐘，而未使用該函數(shù)時的運行時間為 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 的想法更進一步，將標量函數(shù)轉(zhuǎn)換為內(nèi)聯(lián)表函數(shù)并使用交叉應用為每對列激活它 - 您可能會得到以更繁瑣的查詢?yōu)榇鷥r獲得更好的性能:

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

這篇關于T-SQL UDF 與完整表達式運行時的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！