SQL Server Portal

All of us know that user defined functions may affect the query performance very badly, specially when we are dealing with a huge amount of data. Despite saying this, most of the time we are left with no choice but to use User Defined Function (UDF) and bear the cost of its performance.

In SQL Server 2019, a feature shipped namely Scalar UDF Inlining, which was a long awaited feature. The main goal of this feature is to improve the performance of UDF drastically. Excited ?

Let me create a sample User Defined Function (UDF) to use in my demonstration as shown below.

Sample:

USE WideWorldImporters;
GO

CREATE FUNCTION [dbo].[UDF_customer_Name]
(
 @CustomerID INT
)
RETURNS VARCHAR(250) 
AS
BEGIN

  DECLARE @CustomerName VARCHAR(250);

  SELECT  @CustomerName  = [CustomerName] 
  FROM    [Sales].[Customers] 
  WHERE  [CustomerID] = @CustomerID;

  RETURN  @CustomerName;
END
GO

Let me show you in the below example how we were using UDF in the earlier version of SQL Server and facing performance issues. Let me call the above created UDF in the below query and show you its performance. In this UDF, I will pass the Customer ID as a parameter and get Customer Name in return.

USE WideWorldImporters;
GO

SET STATISTICS TIME ON; 
GO

SELECT [CustomerID]
     , [dbo].[UDF_Customer_Name](CustomerID) AS [CustomerName]
     , [InvoiceID]
     , [TransactionDate]
FROM   [Sales].[CustomerTransactions];
GO

SET STATISTICS TIME OFF; 
GO
--OUTPUT

Performance:

As we can see below, I have 97147 records and it takes 5 seconds to execute the query as shown below.

Scalar UDF Inlining Feature:

As we can see in above example, it took time to execute above query even though we have few records, which means that the performance is not good and this was normal. Now let’s try Scalar UDF Inlining feature to see the performance impact.

Compatibility level:

The database compatibility MUST be 150 or higher to use this feature. Given below is the query to set the compatibility level.

ALTER DATABASE WideWorldImporters
SET COMPATIBILITY_LEVEL =150;
GO
--OUTPUT

Step 1:

Let’s enable Scalar UDF Inlining as shown below.

USE WideWorldImporters;
GO

ALTER DATABASE SCOPED CONFIGURATION 
SET TSQL_SCALAR_UDF_INLINING = ON;
GO

Step 2:

Let’s check the inlineable status of my above created UDF by using sys.sql_modules. It MUST be 1 to use this feature as shown below.

USE WideWorldImporters;
GO

SELECT object_id
     , definition
     , is_inlineable
FROM sys.sql_modules
WHERE definition LIKE '%UDF_CUSTOMER%';
GO

Step 3:

Now, let’s execute the same query and check the performance as shown below

USE WideWorldImporters;
GO

SET STATISTICS TIME ON; 
GO

SELECT [CustomerID]
     , [dbo].[UDF_Customer_Name](CustomerID) AS [CustomerName]
	 , [InvoiceID]
	 , [TransactionDate]
FROM   [SALES].[CustomerTransactions];
GO
--Output
SET STATISTICS TIME OFF; 
GO

Performance:

As we can see below that this feature has increased the performance tremendously and it hardly took 1 second to execute the query.

Clean Up:

Let’s drop the above created User Defined Function.

USE WideWorldImporters;
GO

DROP FUNCTION [dbo].[UDF_customer_Name]
GO

Limitations:

There are certain limitations for this feature, if those conditions are NOT met in your user defined function, then you can’t use this feature, please review it here.

Conclusion:

I found this new feature very useful since it improves the performance of User Defined Function a lot. Do let me know if you use this feature and how helpful you find it.