时间戳

首页 > 解决方案 > 使用 SQL 标量函数的查询如何从运行 5 分钟缩短到 5 小时

sql - 使用 SQL 标量函数的查询如何从运行 5 分钟缩短到 5 小时

问题描述

我一直在使用此功能，该功能很常见，可以删除非字母数字字符。

ALTER Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

这从运行 5 分钟到 5 小时。是什么原因造成的？当我从查询中删除该功能时，它会在 5 分钟内完成。此查询已运行数百次。

数据还是一样的，索引还是一样的，它们没有碎片化。180GB左右的磁盘空间也足够了，内存也很充足，基本上两次运行之间没有任何变化。我还检查了 SQL 探查器是否有任何跟踪，但我只能找到运行的内部跟踪，主要是 Microsoft Telemetry 服务（2016 开发人员版）

我真的很难过，我知道标量函数不是最有效的，但是运行时间的这种急剧增加让我感到困惑。

标签： sqlsql-serveruser-defined-functions

解决方案

正如我在评论中提到的，你的问题是这里的 2 倍：

  1. 你有一个多行标量函数，众所周知，它通常表现不佳。（即使您使用的是 SQL Server 2019，也不会内联该函数。）
  2. WHILE您的函数中有一个，它的性能确实很差，因为 SQL 是一种基于集合的语言，因此不能很好地执行迭代过程。

我假设该函数的意图是它执行它所调用的操作，并且只保留字符串中的数字和字母。由于您没有注意到版本，所以我建议使用内联表值函数。但是，我将假设您可以访问足够新的版本来使用STRING_AGG：

CREATE FUNCTION dbo.RemoveNonAlphaCharacters (@InputString varchar(1000))
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@InputString))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3),
    Chars AS(
        SELECT I,
               SUBSTRING(@InputString,I,1) AS C
        FROM Tally)
    SELECT STRING_AGG(C,'') WITHIN GROUP (ORDER BY I) AS OutputString
    FROM Chars
    WHERE C LIKE '[A-z]'
       OR C LIKE '[0-9]';

然后您可以CROSS APPLY在以下位置调用所述函数FROM：

SELECT V.YourString,
       RNAC.OutputString
FROM (VALUES('abc 123-789'),('Apples & Pears'),('Mr O''Mally'))V(YourString)
     CROSS APPLY dbo.RemoveNonAlphaCharacters(V.YourString) RNAC;

如果您使用的不是最新版本的 SQL Server，则需要将STRING_AGG调用替换为“旧” FOR XML PATH（和STUFF）方法。sql server pre 2017 的 string_agg

推荐阅读