One new thing that SQL Server 2016 has added is the ability to natively compile user-defined functions. Previously, native compilation, part of the hekaton feature, was limited to stored procedures.
When I saw that, the first question that came to mind is whether natively compiling a scalar function reduces the overhead when calling that function within another query. I’m not talking about data-accessing scalar UDFs, since natively compiled functions can only access in-memory tables, but functions that do simple manipulation of the parameters passed in. String formatting, for example, or date manipulation.
While not as harmful as data-accessing scalar UDFs, there’s still overhead as these are not inline functions, they’re called for each row in the resultset (as a look at the Stored Procedure Completed XE event would show), and the call to the function takes time. Admittedly not a lot of time, but when it’s on each row of a large resultset the total can be noticeable.
I decided to do a simple test. A query against a table with ~600k rows, one query with a traditional scalar function, one with a natively compiled function and one with the function’s contents in the query as a column.
The two functions:
CREATE FUNCTION dbo.DateOnly (@Input DATETIME) RETURNS DATETIME AS BEGIN RETURN DATEADD(dd, DATEDIFF (dd, 0, @Input), 0); END GO
and
CREATE FUNCTION dbo.DateOnlyHekaton (@Input DATETIME) RETURNS DATETIME WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') RETURN DATEADD(dd,DATEDIFF(dd,0,@Input),0); END GO
And the query without the function would be a simple
SELECT DATEADD(dd, DATEDIFF(dd, 0, SomeDate), 0) FROM SomeTable;
Durations and CPU usage were caught with Extended Events. I ran each query 25 times and aggregated the results.
| Average CPU (ms) | Average Duration (ms) | |
| In-line expression | 289 | 294 |
| Normal function | 3555 | 3814 |
| Natively Compiled Function | 3318 | 3352 |
Not quite what I hoped. While the overhead of the natively compiled function is lower, it’s lower only by about 10%, which really is not worth it, now when we’re talking about an order of magnitude difference from the query without the function call.
Looks like the guidance is still going to be to not use scalar UDFs within other queries.
Hi,
nice blog post. Have you tried to make your funtion deterministic with the “SCHEMABINDING” keyword? How does it perform, if you add this?
The natively compiled one is schema-bound. I didn’t try with the normal one.
Given that the natively compiled, schemabound function is still terribly slow, I don’t hold out much hope it’ll improve the normal function
Did you try with à c# fonction ? The free library sqlsharp may be usefull in tour test-case to add a new test.
No, because the point wasn’t to see if I need to get a C# developer to help. It was to see if T-SQL functions are usable without killing performance yet.
Since the advent of the table-valued function an inline variant has been available. It would be a realy nice feature to have a similar variant of the scalar function. Notice that a single return would suffice for most utility functions like the one in this example. SQL Server could simply replace the call with the expansion, and we can keep these definitions in one place. Additional restrictions migth be necessary but I don’t see why such a macro-like extension cannot be made available in this magnificent database engine.
Did you compare this to CROSS APPLY & TVF just to see how much better that option is (still)?
Untested, but should work…
CREATE FUNCTION dbo.DateOnlyTVF (@Input DATETIME)
RETURNS @T TABLE (D datetime) AS
BEGIN
INSERT @T SELECT DATEADD(dd, DATEDIFF (dd, 0, @Input), 0);
RETURN
END
GO
SELECT t.D
FROM Table
CROSS APPLY dbo.DateOnlyTVF(SomeDate) t
Also probably should test against an Inline TVF vs regular TVF.
No, because I wasn’t testing in-line table-valued functions (which work very well), nor was I testing multi-statement table-valued user-defined functions (which don’t work very well)
I was testing to see whether natively compiled scalar functions had performance more comparable to an expression in-line in the query than regular scalar functions. Which they don’t.
Thanks Gail for this post!
Your findings are inline with what I’ve found so far, unfortunately the execution overhead seems to be the same for natively compiled as normal functions, the only difference being the “actual” work time.
Eirikur
Yeah, pity that. Maybe in a future version…
Hi Gail, (love reading your articles btw), and I’ve got a question for you.
We’ve got some complex MSTVF, and don’t ask why but they process data within cursors (which I’ve rewritten). It’s impossible to write these as ITVFs. Native scalar functions are out since these actually return multiple fields. Are there any options here you could suggest for improving performance (I know it’s tough not looking at the code but in terms of broad scope design).
Second question, when it comes to a SF, nativelycompiledSF, or an ITVF, what wins?
Inline table wins, see the results in this post for why natively compiled doesn’t.
As for your multi-statement function, not much other than rewrite it and what calls it.