Scalar functions in SQL server 2019, part 2

In my earlier blog post, I focused on scalar functions that performs I/O, meaning they have at least one SELECT. For this post, I will focus on the opposite.

I have seen plenty of cases where functions just concatenate two or three strings, possibly substituting NULL with an empty string. Or doing simple arithmetic. In other languages, that might be just fine – the function is compiled in such a way that the performance difference between in-lining that expression and calling the function is minuscule. But not in SQL server! At least not until SQL server 2019.

The point here is that there has been a significant overhead of just calling the function. And since SQL is set-based, the function can be called many many times in just one statement (a SELECT, for instance).

The example

I’m using the AdventureworksDW database, and the FactResellerSalesXL_CCI table, which has approx 11,000,000 rows. I’m using the one with a clustered columnstore index, which is about 0.48 GB.

We are going to calculate the average UnitPrice, including VAT (which in Sweden is 25%).

The UDF

CREATE OR ALTER FUNCTION addVAT (@v money)
RETURNS money
AS
BEGIN
    RETURN @v * 1.25
END
GO

The queries

We will calculate average price including VAT for all approx 11 million rows. The queries that call the UDF will be executed with both UDF inlining turned off and on. See my earlier blog post for turning UDF inlining on/off.

-- Q1 With scalar function, called for each row
SELECT SUM(dbo.addVAT(Unitprice)) FROM FactResellerSalesXL_CCI
GO

-- Q2 In-line the expression
SELECT SUM(Unitprice * 1.25) FROM FactResellerSalesXL_CCI
GO

-- Q3 Call the UDF only once
SELECT dbo.addVAT(SUM(Unitprice)) FROM FactResellerSalesXL_CCI
GO

-- Q4 In-line the expression and calculate only once
SELECT (SUM(Unitprice)) * 1.25 FROM FactResellerSalesXL_CCI
GO

The result

QueryExpression calledUDF InliningDuration msCPU ms
Q1many timesN38,10030,900
Q1many timesY960970
Q2many times510510
Q3onceN2116 or 32
Q3onceY2116 or 32
Q4once2116 or 32

Conclusion

First I want to remind you that we call this function many times: 11 million times. Keep that in mind. But also keep in mind that scalar functions prohibit parallelism, unless they are inlined in 2019.

Note that measuring CPU seems to be in some type of quanta. We seem to get a multiple of 16 ms, or close to that. You might have seen this before, possibly from SET STATISTICS TIME or a trace. Not really relevant to the discussion, just an observation.

Anyhow, as you can see, the cost of calling an UDF can be significant. The savings with SQL server inlining it for us can be drastic. As good as not having it in the first place? No, not really but still a potential for a hefty saving.

And, less stupid code is always better!

Disclaimer

If you look closely enough at the result, you will see that there is a slight difference between the result from some of the queries. It isn’t until the 8:th digit, though. And, more importantly: it is beside the point.