Scalar UDF Inlining in SQL Server 2019

2019-02-25 Wayne Sheffield No comments

How does Scalar UDF Inlining affect the performance of scalar functions?

SQL Server 2019 introduces a new feature called “Scalar UDF Inlining”. In a nutshell, this feature will take a scalar function and it will inline it into the query plan (similar to an Inline Table Valued Function (TVF), or even a view).

This blog post will examine changes to the query plan and performance when Scalar UDF Inlining is occurring.

I have previously blogged about function performance – here and here. For a quick recap, the performance test ranks these function in duration. The order of the types of functions by duration is Inline TVF, Scalar UDF, and then finally a Multi-Statement TVF (MSTVF) – and the MSTVF is way behind the other two types of functions.

I’m using a Linux (Ubuntu) VM with SQL Server 2019 to perform these comparison performance tests. I use one database in the SQL 2019 compatibility level, and another one in the SQL 2017 compatibility level. I’m using the same performance test used in the previous blog posts.

Creating the test environment

I start off by creating two databases, and putting one of them in the SQL 2017 compatibility level:

IF DB_ID('FunctTest140') IS NULL
    CREATE DATABASE FunctTest140;
    ALTER DATABASE FunctTest140 SET COMPATIBILITY_LEVEL = 140;
GO
IF DB_ID('FunctTest150') IS NULL
    CREATE DATABASE FunctTest150;
GO

IF DB_ID('FunctTest140') IS NULL

CREATE DATABASE FunctTest140;

ALTER DATABASE FunctTest140 SET COMPATIBILITY_LEVEL = 140;

IF DB_ID('FunctTest150') IS NULL

CREATE DATABASE FunctTest150;

Next, I create a table with a million rows of random, and three functions. These functions perform the same work, the only difference is in the type of function. The functions remove the non-numeric characters from the string, and return the result in the order that the digits appear. This table and the functions are created in both databases:

IF OBJECT_ID('dbo.temp1', 'U') IS NOT NULL DROP TABLE dbo.temp1;
WITH cteSymbols AS
(
SELECT  CharSymbol
FROM    (VALUES ('('), ('('), ('-'), (' '), ('-('), (')-'), ('.'), (','), ('/'), ('@')) dt (CharSymbol)
)
SELECT  TOP (1000000)
        IDENTITY(INTEGER) AS RowID,
        CONVERT(VARCHAR(30), s1.CharSymbol + CONVERT(VARCHAR(8), ABS(so1.object_id) % 10000000,0) +
                             s2.CharSymbol + CONVERT(VARCHAR(8), ABS(so2.object_id) % 10000000,0) +
                             s3.CharSymbol
               ) AS StringOfNumbersWithNonNumbers
INTO    dbo.temp1
FROM    cteSymbols s1
CROSS JOIN cteSymbols s2
CROSS JOIN cteSymbols s3
CROSS JOIN sys.all_objects so1
CROSS JOIN sys.all_objects so2;
 
-- let's look at a few rows to see what we have
SELECT * FROM dbo.temp1 WHERE RowID <= 10;
GO


	
-- Inline table valued function
IF OBJECT_ID('dbo.ITVF_TEST') IS NOT NULL DROP FUNCTION dbo.ITVF_TEST;
GO
CREATE FUNCTION dbo.ITVF_TEST (@Input VARCHAR(30))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT (SELECT SUBSTRING(@Input,N,1)
        FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)
        WHERE N<=LEN(@Input)
        AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')
        ORDER BY N
        FOR XML PATH(''), TYPE).value('.','VARCHAR(30)') AS StringNumbersOnly;
GO
 
-- Multi-statement table valued function
IF OBJECT_ID('dbo.MSTVF_TEST') IS NOT NULL DROP FUNCTION dbo.MSTVF_TEST;
GO
CREATE FUNCTION dbo.MSTVF_TEST (@Input VARCHAR(30))
RETURNS @Output TABLE (StringNumbersOnly VARCHAR(30))
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @Output (StringNumbersOnly)
    SELECT (SELECT SUBSTRING(@Input,N,1)
            FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)
            WHERE N<=LEN(@Input)
            AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')
            ORDER BY N
            FOR XML PATH(''), TYPE).value('.','VARCHAR(30)') AS StringNumbersOnly;
    RETURN;
END;
GO
 
-- Scalar function
IF OBJECT_ID('dbo.SF_TEST') IS NOT NULL DROP FUNCTION dbo.SF_TEST;
GO
CREATE FUNCTION dbo.SF_TEST (@Input VARCHAR(30))
RETURNS VARCHAR(30)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Output VARCHAR(30);
    SELECT @Output = (SELECT SUBSTRING(@Input,N,1)
            FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)
            WHERE N<=LEN(@Input)
            AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')
            ORDER BY N
            FOR XML PATH(''), TYPE).value('.','VARCHAR(30)');
    RETURN @Output;
END;
GO

IF OBJECT_ID('dbo.temp1', 'U') IS NOT NULL DROP TABLE dbo.temp1;

WITH cteSymbols AS

(

SELECT CharSymbol

FROM (VALUES ('('), ('('), ('-'), (' '), ('-('), (')-'), ('.'), (','), ('/'), ('@')) dt (CharSymbol)

)

SELECT TOP (1000000)

IDENTITY(INTEGER) AS RowID,

CONVERT(VARCHAR(30), s1.CharSymbol + CONVERT(VARCHAR(8), ABS(so1.object_id) % 10000000,0) +

s2.CharSymbol + CONVERT(VARCHAR(8), ABS(so2.object_id) % 10000000,0) +

s3.CharSymbol

) AS StringOfNumbersWithNonNumbers

INTO dbo.temp1

FROM cteSymbols s1

CROSS JOIN cteSymbols s2

CROSS JOIN cteSymbols s3

CROSS JOIN sys.all_objects so1

CROSS JOIN sys.all_objects so2;

-- let's look at a few rows to see what we have

SELECT * FROM dbo.temp1 WHERE RowID <= 10;

-- Inline table valued function

IF OBJECT_ID('dbo.ITVF_TEST') IS NOT NULL DROP FUNCTION dbo.ITVF_TEST;

CREATE FUNCTION dbo.ITVF_TEST (@Input VARCHAR(30))

RETURNS TABLE

WITH SCHEMABINDING

RETURN

SELECT (SELECT SUBSTRING(@Input,N,1)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)

WHERE N<=LEN(@Input)

AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')

ORDER BY N

FOR XML PATH(''), TYPE).value('.','VARCHAR(30)') AS StringNumbersOnly;

-- Multi-statement table valued function

IF OBJECT_ID('dbo.MSTVF_TEST') IS NOT NULL DROP FUNCTION dbo.MSTVF_TEST;

CREATE FUNCTION dbo.MSTVF_TEST (@Input VARCHAR(30))

RETURNS @Output TABLE (StringNumbersOnly VARCHAR(30))

WITH SCHEMABINDING

BEGIN

INSERT INTO @Output (StringNumbersOnly)

SELECT (SELECT SUBSTRING(@Input,N,1)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)

WHERE N<=LEN(@Input)

AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')

ORDER BY N

FOR XML PATH(''), TYPE).value('.','VARCHAR(30)') AS StringNumbersOnly;

RETURN;

END;

-- Scalar function

IF OBJECT_ID('dbo.SF_TEST') IS NOT NULL DROP FUNCTION dbo.SF_TEST;

CREATE FUNCTION dbo.SF_TEST (@Input VARCHAR(30))

RETURNS VARCHAR(30)

WITH SCHEMABINDING

BEGIN

DECLARE @Output VARCHAR(30);

SELECT @Output = (SELECT SUBSTRING(@Input,N,1)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) AS x(N)

WHERE N<=LEN(@Input)

AND SUBSTRING(@Input,N,1) LIKE ('[0-9]')

ORDER BY N

FOR XML PATH(''), TYPE).value('.','VARCHAR(30)');

RETURN @Output;

END;

The following query tests that all the functions do truly return the same data:

SELECT  TOP (10)
        t.RowID,
        t.StringOfNumbersWithNonNumbers AS StringOfNumbers,
        dbo.SF_TEST(t.StringOfNumbersWithNonNumbers) AS ScalarFunction,
        MSTVF.StringNumbersOnly AS MultiStatementTVF,
        ITVF.StringNumbersOnly AS InlineTVF
FROM    dbo.temp1 t
CROSS APPLY dbo.MSTVF_TEST(t.StringOfNumbersWithNonNumbers) MSTVF
CROSS APPLY dbo.ITVF_TEST(t.StringOfNumbersWithNonNumbers) ITVF;

SELECT TOP (10)

t.RowID,

t.StringOfNumbersWithNonNumbers AS StringOfNumbers,

dbo.SF_TEST(t.StringOfNumbersWithNonNumbers) AS ScalarFunction,

MSTVF.StringNumbersOnly AS MultiStatementTVF,

ITVF.StringNumbersOnly AS InlineTVF

FROM dbo.temp1 t

CROSS APPLY dbo.MSTVF_TEST(t.StringOfNumbersWithNonNumbers) MSTVF

CROSS APPLY dbo.ITVF_TEST(t.StringOfNumbersWithNonNumbers) ITVF;

Differences in the query plans

The first thing that I want to do is to compare the difference in the query plans produced in each database. I run the following query in each database with the actual execution plan turn on:

SELECT  TOP (10) 
        t.RowID,
        t.StringOfNumbersWithNonNumbers AS StringOfNumbers,
        dbo.SF_TEST(t.StringOfNumbersWithNonNumbers) AS ScalarFunction
FROM    dbo.temp1 t;

SELECT TOP (10)

t.RowID,

t.StringOfNumbersWithNonNumbers AS StringOfNumbers,

dbo.SF_TEST(t.StringOfNumbersWithNonNumbers) AS ScalarFunction

FROM dbo.temp1 t;

For the database in the SQL 2017 compatibility level, the resulting query plan is:

SQL 2017 Query Plan

Hovering over the Select operator:

SQL 2017 query plan cost

For the database in the SQL 2019 compatibility level, the resulting query plan is:

SQL 2019 Query Plan

Hovering over the Select operator:

SQL 2019 Query Plan Cost

A casual look shows that the Scalar UDF Inlining has occurred. The work being performed by the scalar function has been inlined into the query plan. In SQL 2017, all of the work is shown by a “Compute Scalar” operator. This operator hides all of the underlying work going on in the Scalar UDF. This results in a query plan with a cost that does not truly represent the work going on.

The performance test

The next step is to test the performance of each of the functions. This run is performed 11 times for each type of function, in each of the databases. Why 11 times? Well, I did it once, then decided to run this in a batch for ten loops. The performance testing code is:

-- PERFORMANCE TESTING TIME!!!
-- table to store the times:
IF OBJECT_ID('tempdb.dbo.#temp2') IS NOT NULL DROP TABLE #temp2;
CREATE TABLE #temp2 (
    RowID INTEGER IDENTITY,
    FunctionName sysname,
    ActionDateTime DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));
 
 
--------- INLINE TABLE-VALUED FUNCTION -----------
IF OBJECT_ID('dbo.ITVF_RESULTS') IS NOT NULL DROP TABLE dbo.ITVF_RESULTS;
INSERT INTO #temp2 (FunctionName) VALUES  ('ITVF_TEST');
SELECT  *
INTO    dbo.ITVF_RESULTS
FROM    dbo.temp1
CROSS APPLY dbo.ITVF_TEST(StringOfNumbersWithNonNumbers)
INSERT INTO #temp2 (FunctionName) VALUES  ('ITVF_TEST');
 
 
--------- MULTI_STATEMENT TABLE-VALUED FUNCTION -----------
IF OBJECT_ID('dbo.MSTVF_RESULTS') IS NOT NULL DROP TABLE dbo.MSTVF_RESULTS;
INSERT INTO #temp2 (FunctionName) VALUES  ('MSTVF_TEST');
SELECT  *
INTO    dbo.MSTVF_RESULTS
FROM    dbo.temp1
CROSS APPLY dbo.MSTVF_TEST(StringOfNumbersWithNonNumbers)
INSERT INTO #temp2 (FunctionName) VALUES  ('MSTVF_TEST');
 
 
--------- SCALAR FUNCTION ------------
IF OBJECT_ID('dbo.SF_Results') IS NOT NULL DROP TABLE dbo.SF_Results;
INSERT INTO #temp2 (FunctionName) VALUES  ('SF_TEST');
SELECT  *, dbo.SF_TEST(StringOfNumbersWithNonNumbers) AS StringNumbersOnly
INTO    dbo.SF_Results
FROM    dbo.temp1;
INSERT INTO #temp2 (FunctionName) VALUES  ('SF_TEST');
 
 
 
---------- Show the testing results -----------
WITH cte AS
(
    SELECT  t.FunctionName,
            DATEDIFF(MICROSECOND, MIN(t.ActionDateTime), MAX(t.ActionDateTime)) AS [Duration (microseconds)]
    FROM    #temp2 t
    GROUP BY t.FunctionName
)
SELECT  DB_NAME() AS DatabaseName,
        FunctionName,
        [Duration (microseconds)],
        CONVERT(NUMERIC(5,2), (cte.[Duration (microseconds)] * 1.0 / SUM(cte.[Duration (microseconds)]) OVER () * 1.0) * 100.0) AS PercentOfBatch
FROM    cte
ORDER BY [Duration (microseconds)];
GO 10

-- PERFORMANCE TESTING TIME!!!

-- table to store the times:

IF OBJECT_ID('tempdb.dbo.#temp2') IS NOT NULL DROP TABLE #temp2;

CREATE TABLE #temp2 (

RowID INTEGER IDENTITY,

FunctionName sysname,

ActionDateTime DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));

--------- INLINE TABLE-VALUED FUNCTION -----------

IF OBJECT_ID('dbo.ITVF_RESULTS') IS NOT NULL DROP TABLE dbo.ITVF_RESULTS;

INSERT INTO #temp2 (FunctionName) VALUES ('ITVF_TEST');

SELECT *

INTO dbo.ITVF_RESULTS

FROM dbo.temp1

CROSS APPLY dbo.ITVF_TEST(StringOfNumbersWithNonNumbers)

INSERT INTO #temp2 (FunctionName) VALUES ('ITVF_TEST');

--------- MULTI_STATEMENT TABLE-VALUED FUNCTION -----------

IF OBJECT_ID('dbo.MSTVF_RESULTS') IS NOT NULL DROP TABLE dbo.MSTVF_RESULTS;

INSERT INTO #temp2 (FunctionName) VALUES ('MSTVF_TEST');

SELECT *

INTO dbo.MSTVF_RESULTS

FROM dbo.temp1

CROSS APPLY dbo.MSTVF_TEST(StringOfNumbersWithNonNumbers)

INSERT INTO #temp2 (FunctionName) VALUES ('MSTVF_TEST');

--------- SCALAR FUNCTION ------------

IF OBJECT_ID('dbo.SF_Results') IS NOT NULL DROP TABLE dbo.SF_Results;

INSERT INTO #temp2 (FunctionName) VALUES ('SF_TEST');

SELECT *, dbo.SF_TEST(StringOfNumbersWithNonNumbers) AS StringNumbersOnly

INTO dbo.SF_Results

FROM dbo.temp1;

INSERT INTO #temp2 (FunctionName) VALUES ('SF_TEST');

---------- Show the testing results -----------

WITH cte AS

(

SELECT t.FunctionName,

DATEDIFF(MICROSECOND, MIN(t.ActionDateTime), MAX(t.ActionDateTime)) AS [Duration (microseconds)]

FROM #temp2 t

GROUP BY t.FunctionName

)

SELECT DB_NAME() AS DatabaseName,

FunctionName,

[Duration (microseconds)],

CONVERT(NUMERIC(5,2), (cte.[Duration (microseconds)] * 1.0 / SUM(cte.[Duration (microseconds)]) OVER () * 1.0) * 100.0) AS PercentOfBatch

FROM cte

ORDER BY [Duration (microseconds)];

GO 10

The results for SQL 2017:

SQL 2017 Results

As expected (based on the other two blog posts), the MSTVF is the slowest. Overall, the inline TVF is the fastest, with the scalar UDF falling between the two. For each batch, the Inline TVF is about 20% of the batch, the Scalar UDF is about 35%, and the MSTVF is about 45%.

How does the Scalar UDF Inlining help out? Well, the results for that are:

SQL 2019 Results

In this result set, we can see that the Scalar functions have improved, but the overall ranking has remained the same. The scalar functions are still between the Inline TVF and the MSTVF. However, when you look at the percent of batch, the Inline TVF is about 17%, the Scalar UDF is about 25% and the MSTVF is about 58%.

And if you look at the overall duration for all batches, we see that the SQL 2019 runs shaved off about 1/3 of the total time.

Both of these show that Scalar UDF Inlining is improving those Scalar UDFs!

In Conclusion…

We can see that the Scalar UDF Inlining is improving the performance of the Scalar UDFs. When the batches are running in 1/3 less time, that is a pretty dramatic improvement.

Going into this test, I was hopeful that the Scalar UDF Inlining performance would be on par with the Inline TVFs. While the performance has dramatically improved, it wasn’t enough to match the Inline TVFs. This means that when you are working with functions, the best choice is still to use an Inline TVF where possible. This also shows that we still want to avoid using a MSTVF.

Be sure to read the Microsoft article (the link is in the first line of this post) for all that you can, and can’t, do with Scalar UDF Inlining.

Database Masters, Performance, SQL, SQL 2019 Functions, Performance, Scalar UDF Inlining

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Blogroll

Scalar UDF Inlining in SQL Server 2019

How does Scalar UDF Inlining affect the performance of scalar functions?

Creating the test environment

Differences in the query plans

The performance test

In Conclusion…

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Tags

Blogroll

Scalar UDF Inlining in SQL Server 2019

How does Scalar UDF Inlining affect the performance of scalar functions?

Creating the test environment

Differences in the query plans

The performance test

In Conclusion…

Share this:

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply