Natively compiled user-defined functions

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.

10 Comments

  1. Christian

    Hi,

    nice blog post. Have you tried to make your funtion deterministic with the “SCHEMABINDING” keyword? How does it perform, if you add this?

    Reply
    1. Gail (Post author)

      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

      Reply
  2. Ferraton

    Did you try with à c# fonction ? The free library sqlsharp may be usefull in tour test-case to add a new test.

    Reply
    1. Gail (Post author)

      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.

      Reply
  3. Dony van Vliet

    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.

    Reply
  4. Nate

    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

    Reply
    1. Nate

      Also probably should test against an Inline TVF vs regular TVF.

      Reply
      1. Gail (Post author)

        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.

        Reply
  5. Eirikur Eiriksson

    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

    Reply
    1. Gail (Post author)

      Yeah, pity that. Maybe in a future version…

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *