Scalar Functions and Parallel Execution Plans

This article is just a simple demonstration of improvements in scalar functions handling implemented in SQL Server 2019. Prior to this version when the scalar function was used in a query, it was blocking parallel plan generation. You can read more on this topic in this article. One of the most anticipated features in SQL Server 2019 is the Scalar UDF Inlining and I will show you a very simple practical demonstration of how this feature can improve query performance compared to SQL 2017 and older versions.

We will build a scalar function that will replace two HTML tags in the input string with string literals used for tags escaping. We will also build the same function as Inlined because that solution was used before SQL 2019 to address most of the scalar functions related issues. Table dbo.SamleTable will be filled with 100k records to be used in this test.

USE [tempdb]
GO

-- Scalar Function
CREATE FUNCTION [dbo].[f_Scalar_ReplaceHTMLTags](
	@Data NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
BEGIN
	
	RETURN REPLACE(REPLACE(@Data, '<', '&lt;'), '>', '&gt;')

END
GO

-- Inlined function
CREATE FUNCTION [dbo].[f_Inline_ReplaceHTMLTags](
	@Data NVARCHAR(MAX)
)
RETURNS TABLE
AS
	
	RETURN (SELECT REPLACE(REPLACE(@Data, '<', '&lt;'), '>', '&gt;') [Data])
GO

-- Sample data with 100k records
CREATE TABLE [dbo].[SampleTable] (
	[Data] NVARCHAR(MAX)
)

INSERT INTO [dbo].[SampleTable]
	( [Data] )
SELECT TOP 100000
	REPLICATE('<' + 'HTML_Tag_Name' + '>', 100)
FROM [msdb].[sys].[columns] [c1]
	CROSS JOIN [msdb].[sys].[columns] [c2]
GO

Let’s run two queries against the sample data and compare it between SQL Server 2019 and 2017 and review execution plans generated and query statistics. The first query will use the scalar function, the second one is using CROSS APPLY with the inlined function.

SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

SELECT 
	[dbo].[f_Scalar_ReplaceHTMLTags]([Data]) [ReplacedData]
	INTO [#tmp1]
FROM [dbo].[SampleTable]
GO

SELECT 
	[f].[Data] [ReplacedData]
	INTO [#tmp2]
FROM [dbo].[SampleTable] [st]
	CROSS APPLY [dbo].[f_Inline_ReplaceHTMLTags]([st].[Data]) [f]
GO

SQL 2017

SQL 2019

2017

CPU time = 44797 ms, elapsed time = 55845 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000.
CPU time = 41189 ms, elapsed time = 10511 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000.

2019

CPU time = 34171 ms, elapsed time = 12179 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000.
CPU time = 33828 ms, elapsed time = 15719 ms. Table ‘SampleTable’. Scan count 5, logical reads 50000

As we can see the only difference in execution plans is that in 2017 scalar function was blocking optimizer from parallel plan creation. We can get rid of this issue when an inlined version of the function will be used. This workaround is not necessary for then SQL Server 2019 where the scalar function was successfully inlined and the same parallel plan was generated for both queries (scalar and inlined).