SQL Server update语句性能

Question

我在优化SQL查询以进行一些数据清理时遇到了问题。

事实上，我有一个表，它是一种多个特殊字符和单词的引用。让我们称它为ABNORMAL(ID,PATTERN)

我还有另一个表INDIVIDUALS，其中包含一个列(NAME)，我希望通过删除表ABNORMAL中存在的所有字符来清除该列。

目前，我已经尝试使用update语句，但我不确定是否有更好的方法来做到这一点。

方法一

使用while循环构建一个包含来自ABNORMALS的所有字符的替换为空'‘，并使用内置的REPLACE执行一次更新

DECLARE @REPLACE_EXPRESSION  nvarchar(max) ='REPLACE(NAME,'''','''')'
DECLARE @i int  = 1
DECLARE @nbr int = (SELECT COUNT(*) FROM ABNORMAL) 
-- CURRENT_CHARAC
DECLARE @CURRENT_CHARAC nvarchar(max) 
-- NEW REPLACE EXPRESSION TO IMBRICATE INTO THE REPLACE EXPRESSION VARIABLE
DECLARE @CURR_REP NVARCHAR(max)
-- STRING TO BUILD AN SQL QUERY CONTAINING THE REPLACE EXPRESSION
DECLARE @UPDATE_QUERY nvarchar(max) 
WHILE @i < @nbr
BEGIN 
    SELECT @CURRENT_CHARAC=PATTERN FROM CLEANSING_STG_PRISM_FRA_REF_UNSIGNIFICANT_VALUES WHERE ID_PATTERN=@i ;
    SET @REPLACE_EXPRESSION = REPLACE(@REPLACE_EXPRESSION ,'NAME','REPLACE(NAME,'+''''+@CURRENT_CHARAC+''''+','''')')
    set @i=@i+1
END
SET @UPDATE_QUERY = 'UPDATE INDIVIDUAL  SET NAME ='+ @REPLACE_EXPRESSION
EXEC sp_executesql @UPDATE_QUERY

方法二

使用while循环选择异常中的每个字符，并使用包含要删除的字符的替换进行更新：

DECLARE @i int  = 1
DECLARE @nbr int = (SELECT COUNT(*) FROM ABNORMAL) 
-- CURRENT_CHARAC
DECLARE @CURRENT_CHARAC nvarchar(max) 
-- STRING TO BUILD AN SQL QUERY CONTAINING THE REPLACE EXPRESSION
DECLARE @UPDATE_QUERY nvarchar(max) 
WHILE @i < @nbr
BEGIN 
    SELECT @CURRENT_CHARAC=PATTERN FROM CLEANSING_STG_PRISM_FRA_REF_UNSIGNIFICANT_VALUES WHERE ID_PATTERN=@i ;
    UPDATE INDIVIDUAL 
    SET NAME = REPLACE(NAME,@CURRENT_CHARAC,'')
    SET @i=@i+1
END

我已经为两百万条记录测试了这两种方法，我发现第一种方法比第二种方法更快。我会知道你是否已经做了一些类似的和新的(更好的)想法来尝试。

Answer 1

如果您使用的是SQL Server 2017，则可以使用TRANSLATE并避免使用动态SQL：

SELECT i.*
  , REPLACE(TRANSLATE(i.NAME, f, REPLICATE('!', s.l)), '!', '') AS cleansed
FROM INDIVIDUALS i
OUTER APPLY (SELECT STRING_AGG(PATTERN, '') AS f
                   ,LEN(STRING_AGG(PATTERN,'')) AS l
             FROM ABNORMAL) AS s

无论如何，第一种方法更好，因为你做一个UPDATE，第二种方法你一次删除一个字符(所以你会有多个UPDATE)。

我还会用这两种方法跟踪事务日志的增长。

Answer 2

如果没有太多需要清理的字符，那么这个技巧可能会起作用。

基本上，您构建了一个大的update语句，将表中的每个值替换为要删除的字符。

示例代码：

测试数据(使用临时表)

create table #ABNORMAL_CHARACTERS (id int identity(1,1), chr varchar(30));
insert into #ABNORMAL_CHARACTERS (chr) values ('!'),('&'),('#');

create table #INDIVIDUAL (id int identity(1,1), name varchar(30));
insert into #INDIVIDUAL (name) values ('test 1 &'),('test !&#2'),('test 3');

代码：

declare @FieldName varchar(30) = 'name';
declare @Replaces varchar(max) = @FieldName;
declare @UpdateSQL varchar(max);

select @Replaces = concat('replace('+@Replaces+', ', ''''+chr+''','''')') from #ABNORMAL_CHARACTERS order by id;

set @UpdateSQL = 'update #INDIVIDUAL 
set name = '+@Replaces + ' 
where exists (select 1 from #ABNORMAL_CHARACTERS where charindex(chr,name)>0)';

exec (@UpdateSQL);

select * from #INDIVIDUAL;

一个测试here on rextester

如果你有一个可以进行正则表达式替换的UDF。例如here

然后，@Replaces变量可以简化为只有一个RegexReplace函数和一个模式。