2019-03-11 • 4 comments

Scalar UDF Inlining in SQL Server 2019 – Simpler functions

I recently published a post detailing the new Scalar UDF Inlining feature in SQL 2019 here. That post introduced the new feature in a way that I used to compare performance to the other function types, continuing the performance evaluation of functions that I had previously posted here and here. In the Scalar UDF Inlining post, I used a function to strip all non-numeral values from a string, and to return the result. This used the FOR XML output option.

In thinking about how scalar functions are commonly used, I’ve decided to revisit this feature with a simpler function. I will still compare it to all the other types of functions to see how Scalar UDF Inlining compares to the others.

Scalar UDF Inlining Recap

Scalar UDF Inlining takes the operations that the Scalar UDF performs, and inlines those operations into the query plan, similar to a view or an Inline Table-Valued Function. To be able to inline the function, there are some requirements (see the link in the first sentence of this paragraph) that the function must meet.

The Test Environment

As I stated before, I want to run this test with a simpler function. I decided to have the function accept a number, and to return the number multiplied by itself. As in the previous test, we’ll use two databases (one in SQL 2019 compatibility mode, and the other in SQL 2017 compatibility mode):

USE master;

DROP DATABASE IF EXISTS FunctTest140;

DROP DATABASE IF EXISTS FunctTest150;

-- create databases

CREATE DATABASE FunctTest140;

ALTER DATABASE FunctTest140 SET COMPATIBILITY_LEVEL = 140;

CREATE DATABASE FunctTest150;

Inside each database, I’ve created a tally table, and three functions (one Scalar, one Inline Table-Valued Function (iTVF) and one Multi-Statement Table-Valued Function (MSTVF)):

-- build environment in each database
USE FunctTest150; -- repeat for FunctTest140
GO
-- create a 1,000,000 row table to test against
CREATE TABLE dbo.Tally (N INTEGER CONSTRAINT PK_Tally PRIMARY KEY);
WITH Tens    (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
     Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),
     Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3),
     Tally   (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)
INSERT INTO dbo.Tally (N)
SELECT N FROM Tally;
GO
-- create the functions. These just multiply the number by itself.
CREATE FUNCTION dbo.ITVF_Test (@N BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT @N * @N AS ReturnValue;
GO
CREATE FUNCTION dbo.MSTVF_Test (@N BIGINT)
RETURNS @Output TABLE (ReturnValue BIGINT)
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @Output (ReturnValue) VALUES (@N * @N);
    RETURN;
END;
GO
CREATE FUNCTION dbo.SF_Test (@N BIGINT)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
    RETURN @N * @N;
END;
GO

-- build environment in each database

USE FunctTest150; -- repeat for FunctTest140

-- create a 1,000,000 row table to test against

CREATE TABLE dbo.Tally (N INTEGER CONSTRAINT PK_Tally PRIMARY KEY);

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),

Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)

INSERT INTO dbo.Tally (N)

SELECT N FROM Tally;

-- create the functions. These just multiply the number by itself.

CREATE FUNCTION dbo.ITVF_Test (@N BIGINT)

RETURNS TABLE

WITH SCHEMABINDING

RETURN SELECT @N * @N AS ReturnValue;

CREATE FUNCTION dbo.MSTVF_Test (@N BIGINT)

RETURNS @Output TABLE (ReturnValue BIGINT)

WITH SCHEMABINDING

BEGIN

INSERT INTO @Output (ReturnValue) VALUES (@N * @N);

RETURN;

END;

CREATE FUNCTION dbo.SF_Test (@N BIGINT)

RETURNS BIGINT

WITH SCHEMABINDING

BEGIN

RETURN @N * @N;

END;

With these functions created, we run a simple test to see that they work properly:

-- test the functions

SELECT TOP (10)

t.N,

dbo.SF_TEST(t.N) AS ScalarFunction

,MSTVF.ReturnValue AS MultiStatementTVF

,ITVF.ReturnValue AS InlineTVF

FROM dbo.Tally t

CROSS APPLY dbo.MSTVF_TEST(t.N) MSTVF

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

This returns the following result set:

And the following execution plan (SQL 2017):

Notice that there is only one “Compute Scalar” operator that handles both the Scalar UDF and the iTVF. Inside this operator, all of the scalar operations that can be performed at this level are performed. Let’s look at this operator’s properties:

Expr1001 is:

And Expr1002 is:

Be reviewing these, we can see that Expr1001 is for the iTVF, and Expr1002 is for the Scalar function. Even though they can both be pulled into the execution plan into the same query operator, they are doing different things. We can now continue on to…

The Performance Test

Just like in the last post, we’ll run each function individually against the tally table, dumping the results into a temp table. The test against each function is run 10 times. The testing query is:

-- Create a table to store the results in.
IF OBJECT_ID('tempdb.dbo.#TestResults', 'U') IS NOT NULL DROP TABLE #TestResults;
CREATE TABLE #TestResults (
    RowID INTEGER IDENTITY,
    FunctionName sysname,
    ActionDateTime DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));
GO
TRUNCATE TABLE #TestResults;

IF OBJECT_ID('dbo.FunctionResults', 'U') IS NOT NULL DROP TABLE dbo.FunctionResults;
INSERT INTO #TestResults (FunctionName) VALUES  ('ITVF_TEST');
SELECT  t.N,
        ITVF.ReturnValue 
INTO    dbo.FunctionResults
FROM    dbo.Tally t
CROSS APPLY dbo.ITVF_TEST(t.N) ITVF;
INSERT INTO #TestResults (FunctionName) VALUES  ('ITVF_TEST');

IF OBJECT_ID('dbo.FunctionResults', 'U') IS NOT NULL DROP TABLE dbo.FunctionResults;
INSERT INTO #TestResults (FunctionName) VALUES  ('MSTVF_TEST');
SELECT  t.N,
        MSTVF.ReturnValue
INTO    dbo.FunctionResults
FROM    dbo.Tally t
CROSS APPLY dbo.MSTVF_TEST(t.N) MSTVF;
INSERT INTO #TestResults (FunctionName) VALUES  ('MSTVF_TEST');

IF OBJECT_ID('dbo.FunctionResults', 'U') IS NOT NULL DROP TABLE dbo.FunctionResults;
INSERT INTO #TestResults (FunctionName) VALUES  ('SF_TEST');
SELECT  t.N,
        dbo.SF_Test(t.N) AS ReturnValue
INTO    dbo.FunctionResults
FROM    dbo.Tally t
--CROSS APPLY dbo.ITVF_TEST(t.N) ITVF;
INSERT INTO #TestResults (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    #TestResults 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

-- Create a table to store the results in.

IF OBJECT_ID('tempdb.dbo.#TestResults', 'U') IS NOT NULL DROP TABLE #TestResults;

CREATE TABLE #TestResults (

RowID INTEGER IDENTITY,

FunctionName sysname,

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

TRUNCATE TABLE #TestResults;

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

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

SELECT t.N,

ITVF.ReturnValue

INTO dbo.FunctionResults

FROM dbo.Tally t

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

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

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

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

SELECT t.N,

MSTVF.ReturnValue

INTO dbo.FunctionResults

FROM dbo.Tally t

CROSS APPLY dbo.MSTVF_TEST(t.N) MSTVF;

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

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

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

SELECT t.N,

dbo.SF_Test(t.N) AS ReturnValue

INTO dbo.FunctionResults

FROM dbo.Tally t

--CROSS APPLY dbo.ITVF_TEST(t.N) ITVF;

INSERT INTO #TestResults (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 #TestResults 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 SQL 2017 results are:

The SQL 2019 results are:

Wow, the scalar function performance has improved so well that the times are essentially a tie. The average shows just how close they are. The scalar function even beat out the iTVF several times (highlighted in yellow above).

In conclusion…

In my last post, I concluded that the iTVF was still a bit faster, and recommended still using that. With this post, my recommendation is that for simple Scalar UDFs, it may be enough. For more involved functions, it requires some testing to determine which implementation would be better. This testing is to determine if the Scalar UDF can run as good as the iTVF. You can avoid this testing by just using an iTVF if possible. If you have an existing application, Scalar UDF Inlining will improve the performance just by being in the SQL 2019 compatibility level.

As all of these function tests have shown, only use Multi-Statement Table-Valued Functions if you can’t do it in another way.

Update

LondonDBA noted in the comments below that I had a copy / paste error in my test, specifically when dealing with the scalar function. That is the highlighted line in the above testing code (it was not remarked out). Since this issue could affect the performance in both databases, I’ve rerun the testing.

SQL 2017 results:

SQL 2019 results:

With this test, the scalar UDF is, on average, performing ever-so-slightly better than the iTVF. We can also see that in 7 of the 10 runs, it ran the fastest. Scalar UDF Inlining is definitely a game changer when considering what type of function to use. You do still need due diligence to test the functions to ensure that it performs the best for you.

4 responses

LondonDBA

2019-03-12

I think there’s a copy/paste error here:

SELECT t.N,
dbo.SF_Test(t.N) AS ReturnValue
INTO dbo.FunctionResults
FROM dbo.Tally t
CROSS APPLY dbo.ITVF_TEST(t.N) ITVF;

No need for the CROSS APPLY to the ITVF for your SF test.

- Wayne Sheffield
  
  2019-03-13
  
  Thanks for responding. That is potentially affecting the performance of the Scalar UDF, so it looks like I need to redo the test. Thanks for reporting this.
  
Brent Leslie

2020-06-16

Hi Wayne, in testing this works great.

In the real world, I often call the same UDF on multiple columns. When doing this, there seems to be a threshold for when Inlining is faster or slower, depending on the number of times it’s called in the SELECT. Call it on one column – almost always faster. But I had another one where I called it on 25 columns and it made the query dramatically slower. I guess it’s dependent on the function for where this threshold is…

- Wayne Sheffield
  
  2020-06-16
  
  Hi Brian,
  Okay, I’m intrigued. Can you be more specific as to what kind of function you would call on 25 columns?
  
  Thinking of what SQL will be doing , there will be 25 joins going on. That alone will slow things down.
  
  I’d love to see more information to test this out.

Scalar UDF Inlining in SQL Server 2019 – Simpler functions

Scalar UDF Inlining Recap

The Test Environment

The Performance Test

In conclusion…

Update

Like this:

Related

4 responses

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

Scalar UDF Inlining in SQL Server 2019 – Simpler functions

Scalar UDF Inlining Recap

The Test Environment

The Performance Test

In conclusion…

Update

Share this:

Like this:

Related

4 responses

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