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
| Query | Expression called | UDF Inlining | Duration ms | CPU ms |
| Q1 | many times | N | 38,100 | 30,900 |
| Q1 | many times | Y | 960 | 970 |
| Q2 | many times | 510 | 510 | |
| Q3 | once | N | 21 | 16 or 32 |
| Q3 | once | Y | 21 | 16 or 32 |
| Q4 | once | 21 | 16 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.