How can a query with a SQL scalar function go from running in 5 minutes to 5 hours

Question

I've been using this function which is pretty common to remove non alpha numeric characters.

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

This went from running in 5 minutes to 5 hours. What could have caused this? When i remove the function from my query, it goes back to completing in 5 minutes. This query has been run hundreds of times.

The data is still the same, Indexes are still the same, they aren't fragmented. Disk space is sufficient too circa 180GB, Memory is also plentiful, basically, nothing has changed between the two runs. I have also checked SQL profiler for any traces, but I could only find the internal trace which runs and that is primarily the Microsoft Telemetry service (2016 developer edition)

I'm seriously stumped, I know scalar functions aren't the most efficient, but this drastic increase in running time has baffled me.

Because you have a`WHILE`. Multi-line scalar functions, like multi-line table-value functions, can be notoriously slow. Put a **iterative** task in there as well, which RDBMS perform poorly at, and you are going to end up with a slow function. What version of SQL Server are you using and what is the goal of the above function? Is it just to retain letters and numbers, and remove other characters? — Thom A, Oct 20 '20 at 11:23
Defining functions uses no time at all. *Queries* are processed. Your question is asking about performance, but has no information on the query that is being run. — Gordon Linoff, Oct 20 '20 at 11:23
The query is very straightforward, it's literally taking a single column using this function to strip out the special characters before joining to a dimension table to produce a set of results. The one thing that has changed is that an extra hundred thousand rows were added to the table on which this query runs, could it be that the execution plan has changed — SQL_Novice, Oct 20 '20 at 12:48

Thom A · Accepted Answer · 2020-10-20T11:53:32.997

As I mention in the comments, your problem is 2 fold here:

You have a Multi-line Scalar function, which are known to often perform poorly. (Even if you're using SQL Server 2019, the function would not be inlined.)
You have a WHILE in your function, which do perform poorly as SQL is a set-based language and thus not designed to perform iterative processes well.

I assume the function's intent is that it does what it called, and just retains the numbers and letters in a string. As you haven't noted the version then I would suggest using an inline table-value function. I am going to assume, however, you have access to a recent enough version to use STRING_AGG though:

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]';

Then you can call said function using CROSS APPLY in the 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;

If you aren't using a recent version of SQL Server, you'll need to replace the STRING_AGG call with the "old" FOR XML PATH (and STUFF) method. string_agg for sql server pre 2017

Amazing, i'm on SQL Server 2016 developer Edition. In your example, how would you pass an actual column from a table to remove the special characters? — SQL_Novice, Oct 20 '20 at 13:20
Thank you so much, this works so much faster, although I've only managed to use it on the Azure installation as opposed to the 2016 version, will have to figure out how to incorporate the XML version — SQL_Novice, Oct 20 '20 at 13:29

How can a query with a SQL scalar function go from running in 5 minutes to 5 hours

1 Answers1