T-SQL慢查询调优选项？

Using SQL Server 2016. I am running the following update query:

使用SQL Server 2016.我正在运行以下更新查询：

update a set a.[Project Definition]=b.[Project] FROM [Table1] a 
inner join [Table2] b 
on dbo.fReplace(a.[Project Definition]) = dbo.fReplace(b. 
[PROJECT #]) 
and a.[Project Definition] <>'' 
and difference(a.[Project Description],b.[Project Name])>=3

Where [fReplace] is defined as follows (it just strips all non-numeric characters from a field):

其中[fReplace]定义如下（它只是从一个字段中删除所有非数字字符）：

CREATE function [dbo].[fReplace](@strtext varchar(2000))
returns varchar(2000)
as
begin
declare @i int = 32, @rplc varchar(1) = '';
while @i < 256
begin
    if (@i < 48 or @i > 57) and CHARINDEX(char(@i),@strtext) > 0
    begin
        --° #176 ~ 0   --¹ #185 ~ 1   --² #178 ~ 2   --³ #179 ~ 3
        set @rplc = case @i
        when 176 then '0'
        when 185 then '1'
        when 178 then '2'
        when 179 then '3'
        else '' end;

        set @strtext = REPLACE(@strtext,CHAR(@i),@rplc);
    end

    set @i = @i + 1;
end
return ltrim(rtrim(@strtext));
end

The query runs very slow... Any way to speed it up? Note that I added indexes by the fields above already.

查询运行速度很慢......有什么方法可以加快速度吗？请注意，我已经按上面的字段添加了索引。

Regards, M.R.

此致，M.R。

2 个解决方案

#1

Using functions in join conditions is probably what's causing your slowness as it creates a RBAR (row-by-agonizing-row) condition. SQL Server can't perform your JOIN as a set operation, rather it must use loops. If you run your query and have it return the execution plan you will most likely see that.

在连接条件中使用函数可能会导致您的缓慢，因为它会创建一个RBAR（逐行 - 激动行）条件。 SQL Server无法将JOIN作为set操作执行，而是必须使用循环。如果您运行查询并让它返回执行计划，您很可能会看到它。

Adding columns to your table to hold the return values of the function would help speed up your JOIN.

向表中添加列以保存函数的返回值将有助于加速JOIN。

#2

Indexes won't help because you're not looking for the values, you're looking for the function result. This has to do a table scan on both tables and run the function for each row. If you could create a compute column in each table and create an index on that it should speed up:

索引无济于事，因为您没有查找值，您正在寻找函数结果。这必须在两个表上进行表扫描并为每一行运行函数。如果你可以在每个表中创建一个计算列并创建一个索引，它应该加速：

ALTER TABLE Table1
ADD ComputeId AS (dbo.fReplace([Project Definition]))

CREATE INDEX Table1_ComputedIndex ON Table1 (ComputeId)