Scalar UDF Inlining in SQL Server 2019

Scalar UDFs in SQL Server have very bad reputation. You can find a lot of articles why they are bad and why you should avoid them. When you google them, here are article captions you’ll most probably get:

UDF0
It is always suggested to use inline TVF instead of scalar UDFs. This is still valid in most of the cases.

SQL Server 2019 introduces the Scalar UDF Inlining feature. As you might guess, with this feature SQL Server inlines scalar UDFs for you. More details about it you can find in Books Online: Scalar UDF Inlining in SQL Server 2019 . If you would like to know details about the concept and implementation behind it, a great source for this is the Optimization of Imperative Programs in a Relational Database paper.

In this article, I am gonna test this feature by using a sample function that calculates distance between two points. The credits for the function and its invocation go to Hugo Kornelis (blog | twitter) and his great series of articles about Scalar UDFs: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1 to 3). I will use the Application.Cities table from the WideWorldImporters sample database, invoke the dbo.Distance function and compare the performance under different compatibility levels.

Let’s create and populate our sample table:

USE WideWorldImporters;
--create and populate a sample table
DROP TABLE IF EXISTS dbo.Place;
CREATE TABLE dbo.Place(Id INT PRIMARY KEY CLUSTERED, Lat FLOAT, Lon FLOAT, St CHAR(2));
GO
INSERT INTO dbo.Place
SELECT c.CityID, Location.Lat, Location.Long, sp.StateProvinceCode
FROM Application.Cities c
INNER JOIN Application.StateProvinces sp ON c.StateProvinceID = sp.StateProvinceID;
GO

After inserting rows, the table should have 37.490 entries for places in US states. The next piece of code brings the function definition. As mentioned, you can find the original function definition and considerations here:

--credit for function and comments to Hugo Kornelis
CREATE OR ALTER FUNCTION dbo.Distance (@Lat1 FLOAT, @Lon1 FLOAT, @Lat2 FLOAT, @Lon2 FLOAT)
RETURNS FLOAT
AS
BEGIN;
--Convert from degrees to radians
DECLARE @Lat1R FLOAT = RADIANS(@Lat1), @Lon1R FLOAT = RADIANS(@Lon1),
@Lat2R FLOAT = RADIANS(@Lat2),@Lon2R FLOAT = RADIANS(@Lon2),
@DistR FLOAT, @Dist FLOAT;
-- Calculate the distance (in radians)
SET @DistR = 2 * ASIN(SQRT(POWER(SIN((@Lat1R - @Lat2R) / 2), 2)
+ (COS(@Lat1R) * COS(@Lat2R) * POWER(SIN((@Lon1R - @Lon2R) / 2), 2))));
--Convert distance from radians to kilometers
-- Explanation: Distance in radians = distance in nautical miles * (pi / (180 * 60)), so
-- distance in nautical miles = distance in radians * 180 * 60 / pi
-- One nautical mile is 1.852 kilometers, so
-- distance in km = (distance in radians * 180 * 60 / pi) * 1.852
-- And since 180 * 60 * 1.852 = 20001.6, this can be simplified to
-- distance in km = distance in radians * 20001.6 / pi
SET @Dist = @DistR * 20001.6 / PI();
RETURN @Dist;
END;
GO

Now, we need a sample query to invoke the function. The following query will return the distance between each two places in the state Texas. There are 2.395 places in our sample table that meet this criteria. Therefore, the function will be invoked 5.7 million times. This sound as a good test. Before you execute the query, please ensure that the Discard results after execution option is turned ON. With this option, returned times will represent query execution times, and time required for painting 5.7 million rows in SSMS will not interfere with it. We will execute the query twice, under the 140 and 150 compatibility levels which correspond to SQL Server 2017 and SQL Server 2019 respectively:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;
GO
SET STATISTICS TIME ON;
--Ensure that the Discard results after execution option is turned ON
GO
SELECT dbo.Distance(a.Lat, a.Lon, b.Lat, b.Lon)
FROM dbo.Place AS a
CROSS JOIN dbo.Place AS b
WHERE a.St = 'TX' AND b.St = 'TX';
GO
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 150;
GO
SELECT dbo.Distance(a.Lat, a.Lon, b.Lat, b.Lon)
FROM dbo.Place AS a
CROSS JOIN dbo.Place AS b
WHERE a.St = 'TX' AND b.St = 'TX';
GO

Results are quite impressive:
--CL 140:
SQL Server Execution Times:
CPU time = 49594 ms, elapsed time = 63069 ms.
--CL 150:
SQL Server Execution Times:
CPU time = 15672 ms, elapsed time = 2911 ms.

You can see that the CPU time is 3 times lower under the 150 CL, and from the duration point of view, the query is more than 20x faster! When you look at execution plans, you can see that in the first case invoking of UDF is hidden within the Compute Scalar operator, while in SQL Server 2019 UDF definition is expanded and the plan is optimized.

UDF1

The inlined execution plans looks more complicated – SSMS even shows that the first plan is 4 times cheaper –  but this is simple not true and it is caused by completely wrong perception of costs of UDF invocation in the execution plan.

The improvement looks really fabulous, a query is 20 times faster and all you need is to run the database under the latest compatibility level!

With such as big improvement, I was curious about what would be the execution time, if we would write expression in the query instead of function. By taking this approach, SQL Server does not need to switch the context between modules, does not need to invoke function at all. So, let’s compare the following two queries under the 150 CL:

SELECT dbo.Distance(a.Lat, a.Lon, b.Lat, b.Lon)
FROM dbo.Place AS a
CROSS JOIN dbo.Place AS b
WHERE a.St = 'TX' AND b.St = 'TX';
GO
SELECT 2 * ASIN(SQRT(POWER(SIN((RADIANS(a.Lat) - RADIANS(b.Lat)) / 2), 2)
+ (COS(RADIANS(a.Lat)) * COS(RADIANS(b.Lat))
* POWER(SIN((RADIANS(a.Lon) - RADIANS(b.Lon)) / 2), 2)
))) * 20001.6 / PI()
FROM dbo.Place AS a
CROSS JOIN dbo.Place AS b
WHERE a.St = 'TX' AND b.St = 'TX';

Here are CPU and execution times:
--UDF:
SQL Server Execution Times:
CPU time = 16483 ms, elapsed time = 2952 ms
--Expression:
SQL Server Execution Times:
CPU time = 10219 ms, elapsed time = 5474 ms.

The query that invokes UDF used 60% more CPU resources that the one with expression, but at the same time it is almost 2 times faster! And this is not coincidence; I have executed both queries 100+ times and the first query was always about 2 times faster. Both execution plans are parallel, the one with UDF is still more complicated, but it definitely better handles multi-core opportunities.

UDF2

You’ll get the same results, if you wrap the expression in an inline table valued function:

CREATE OR ALTER FUNCTION dbo.Distance_Inline](@Lat1 FLOAT, @Lon1 FLOAT, @Lat2 FLOAT, @Lon2 FLOAT) RETURNS TABLE
AS RETURN
SELECT 2 * ASIN(SQRT(POWER(SIN((RADIANS(@Lat1) - RADIANS(@Lat2)) / 2), 2)
+ (COS(RADIANS(@Lat1)) * COS(RADIANS(@Lat2))
* POWER(SIN((RADIANS(@Lon1) - RADIANS(@Lon2)) / 2), 2)
))) * 20001.6 / PI() AS Dist;
GO
SELECT dbo.Distance(a.Lat, a.Lon, b.Lat, b.Lon)
FROM dbo.Place AS a
CROSS JOIN dbo.Place AS b
WHERE a.St = 'TX' AND b.St = 'TX';
SELECT (SELECT Dist FROM dbo.Distance_Inline(a.Lat, a.Lon, b.Lat, b.Lon)) Dist
FROM dbo.Place AS a
CROSS JOIN dbo.Place AS b
WHERE a.St = 'TX' AND b.St = 'TX';

Results:

--inlined scalar UDF
SQL Server Execution Times:
CPU time = 15158 ms, elapsed time = 3051 ms.
--inline TVF
SQL Server Execution Times:
CPU time = 9967 ms, elapsed time = 5553 ms.

When you look at this example, you can figure out that the advice to use inline-table-valued function instead of scalar UDFs does not always lead you to the best performance. 

I have executed the query with UDF against 140 and 150 CLs for different states and here are some results:

UDF3

For those, who intensive use scalar UDFs in SQL Server, the new version looks like a gift from heaven. The improvement is very impressive. However, the list of limitations also looks very impressive (taken from the article Inlineable Scalar UDF Requirements):

  • The UDF does not invoke any intrinsic function that is either time-dependent (such as GETDATE()) or has side effects (such as NEWSEQUENTIALID())
  • The UDF uses the EXECUTE AS CALLER clause (the default behavior if the EXECUTE AS clause is not specified)
  • The UDF does not reference table variables or table-valued parameters
  • The query invoking a scalar UDF does not reference a scalar UDF call in its GROUP BY clause
  • The query invoking a scalar UDF in its select list with DISTINCT clause does not reference a scalar UDF call in its ORDER BY clause
  • The UDF is not natively compiled (interop is supported)
  • The UDF is not used in a computed column or a check constraint definition.
  • The UDF does not reference user-defined types
  • There are no signatures added to the UDF
  • The UDF is not a partition function

Nevertheless, SQL Server team did a great job with inlining of scalar user defined functions and since the RTM version is not done yet, it is to expect that the list of limitations will be shortened by a few items.

Thanks for reading!

Related Articles

About author View all posts

Milos R.

Leave a Reply

Your email address will not be published. Required fields are marked *