SQL Server, boost your UDFs!

Hi Guys,

Welcome back to my SQL Server tech blog

Today we talk about UDF or User Defined Function.

We'll see why UDFs are slow and mostly how to make them faster!

Are you ready?

What is an UDF?

So, what is an User defined Function?

An user defined function is a "procedure" written in T-SQL language.

You can define a function through the syntax CREATE FUNCTION.

For example:

       
CREATE OR ALTER FUNCTION F_GET_PREZZO(@ARTID INTEGER)
RETURNS FLOAT
AS
BEGIN
   DECLARE @Prezzo FLOAT
   SELECT @Prezzo = PREZZO FROM LISTINO WHERE ARTID=@ARTID;
   RETURN(@Prezzo)
END;                 
 

 

Once defined you can use your function inside your query:

       
SELECT F_GET_PREZZO(ARTID) FROM LISTINO
        

UDFs may have none, one o more input parameters and one output parameters.

Let's say right away that in SQL Server we can have different types of UDF:

• Scalar functions that return one simgle value
• Multi-statement table valued functions also called TVF which return multiple values
• The inline table valued functions that are optimized for performance.

Today we talk about Scalar UDFs. 

Why a scalar UDF is slow

 

Yes, scalar UDFs is knows to be slow! But Why?

Let do an example using the Query below:

       
SELECT F_GET_PREZZO(ARTID) FROM LISTINO  
 

 

If you are using a SQL Server version before 2019 and so with a compatibility level of 140 or lower execute the Query and look ad the execution plan.

The execution plan

Looking at the execution plan, what is the scalar computation task?

This task is the our function that is called for each row estracted from the table.

Remember that this is also the reason for which UDFs are slow!

SQL Server 2019 and the scalar Inlining

This is important:Starting from SQL Server 2019, in order to improve the UDFs performances, microsoft introduces the Scalar UDF Inlining feature.

This feature automatically transforms inline-able UDFs into relational expressions and so improve the performance of queries that use Scalar UDFs

But remember: that not all Scalar UDFs are inline-able.

So write a performant scalar UDFs is matter of write a query that is inline-able!

An example: is our Query inlineable?

Let's take the UDF used previously:

       
CREATE OR ALTER FUNCTION F_GET_PREZZO(@ARTID INTEGER)
RETURNS FLOAT
AS
BEGIN
   DECLARE @Prezzo FLOAT
   SELECT @Prezzo = PREZZO FROM LISTINO WHERE ARTID=@ARTID;
   RETURN(@Prezzo)
END;                 
  

Is our UDF inlineable?

How to verify if an UDF is inlineable?

Simple, you must execute the T-SQL Query below:

       
SELECT
  OBJECT_SCHEMA_NAME(object_id),
  OBJECT_NAME(object_id),
  is_inlineable,
  inline_type
FROM sys.sql_modules
      

If you execute this statement:

SELECT F_GET_PREZZO(ARTID) FROM LISTINO 

you could easily verify that our UDF is inlineable.

Watching now to the execution plan you can verify that the UDF is trasformated into a relational expression:

Our scalar UDF used as example is a good UDF!

Let's now compare the execution time executing the Query:

       
SELECT F_GET_PREZZO(ARTID) FROM LISTINO
 

First in SQL Server 2017 without the inline feature and then in SQL Server 2019 with the inline feature.

In SQL Server 2017 we obtain:

Tempo di esecuzione SQL Server:
tempo di CPU = 78 ms, tempo trascorso = 120 ms.
Tempo di analisi e compilazione SQL Server:
tempo di CPU = 0 ms, tempo trascorso = 0 ms. 

In SQL Server 2019 we obtain:

Tempo di esecuzione SQL Server: 

tempo di CPU = 0 ms, tempo trascorso = 39 ms.
Tempo di analisi e compilazione SQL Server:
tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Through the inline feature the performances are 3 time faster!

Latest UDFs news inside CU2 and CU4

As said previously not all scalar UDFs may became inlineable.

Principal notable cases are:

The @@ROWCOUNT case

Inlining an UDF that contain certain intrinsic functions (e.g. the @@ROWCOUNT) the results may be altered.

SQL Server 2019 without any CU inline the UDF

Updgrading to the CU2 the UDF will be not inlined anymore.

So i suggest to not use the rowcount inside a scalar UDF.

The SELECT with ORDER BY without a "TOP 1"

If our UDF contains a SELECT with an ORDER BY but without a "TOP 1" starting from the latest CU4 the UDF will be not inlined anymore.

Assignment in cojunction with the ORDER BY clause.

If our UDF contain a Query that perform a SELECT with an assignment in conjunction an ORDER BY clause starting from the latest CU4 the UDF will be not inlined anymore.

e.g. SELECT @x = @x +1 FROM table ORDER BY column_name

Finally

Starting from SQL Server 2019 If you need to use scalar UDF you must try to write inlineable UDF. Write a scalar UDF e then always verify if it is inlineable!


OK! That's all for today!
I hope you enjoy this post
If continue to follow my blog.

Luca Biondi @ SQLServerPerformance blog 2020!

Previus post:Slow query performance using UPPER, LOWER or RTRIM in SQL Server 2017 and 2019