CODEWITHSUNDEEP
sqlsql-serveruser-defined-functions
SQL_Novice
SQL_Novice

Reputation: 59

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

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.

Upvotes: 0

Views: 264

Answers (1)

Thom A
Thom A

Reputation: 95564

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

  1. 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.)
  2. 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

Upvotes: 2

Related Questions