SQL Server 2016: Natively Compiled User-Defined Functions (UDFs)

SQL Server2016

SQL Server 2016 enables the creation of natively compiled user-defined functions (UDFs). Since natively compiled UDFs can be used anywhere a function written in TSQL can be used, many people assumed that this would provide an easy route to faster functions. This is not quite the case. Some people even wrote compiled functions that called standard T-SQL functions, apparently expecting them to be faster. TSQL function is still the same and runs at the same speed; wrapping it within a compiled function accomplishes nothing. (In fact, there is more overhead incurred just calling the function.)

You can understand, however, why folks would assume that natively compiled UDF code should run faster, since, in general, interpreted programming languages do not perform as well as natively compiled code. But this is TSQL we are talking about here. We’re not inverting huge matrices or modelling the world economy, and lord knows we’re not controlling a complex user interface from TSQL. In fact, the sorts of calculations commonly performed in T-SQL are often modest by computational standards and are unlikely to benefit directly from native compilation.

Let’s start by looking at a T-SQL function that calculates the value of the probability density function for some normal distribution with a specified mean and standard deviation. The primary motivation for choosing this calculation is to use an arithmetic expression just slightly more complex than is commonly found in SQL.

CREATE FUNCTION dbo.NormalPdf_TSQL

(

@X FLOAT — Point at which function is to be evaluated

,@Mean FLOAT — Mean of the Normal Distribution

,@StdDev FLOAT — Standard Deviation of the Normal Distribution

)

RETURNS FLOAT

AS

BEGIN

RETURN EXP(-0.5*(@X@Mean)*(@X@Mean) /

(@StdDev*@StdDev))/2.506628274631*@StdDev — SQRT(2.0 * PI()) = 2.506628274631

END

Native UDF Compilation

It is not surprising that natively compiled UDFs can be used anywhere a traditional TSQL UDF can be used, since there is no reason to imagine their use would be restricted to queries involving in-memory tables. However, you cannot create a natively compiled UDF unless there is at least one in-memory table defined in your database. If you are going to test these functions, you will have to create a memory-optimized table first.

Here is the same probability density function, this time created for native compilation:

CREATE FUNCTION dbo.NormalPdf

(

@X FLOAT — Point at which function is to be evaluated

,@Mean FLOAT — Mean of the Normal Distribution

,@StdDev FLOAT — Standard Deviation of the Normal Distribution

)

RETURNS FLOAT

WITH NATIVE_COMPILATION, SCHEMABINDING

AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’English’)

RETURN EXP(-0.5*(@X@Mean)*(@X@Mean) /

(@StdDev*@StdDev))/2.506628274631*@StdDev

END

Readers who have created natively compiled stored procedures in SQL Server 2014 will recognize that there is absolutely nothing new about the syntax for this statement.

Performance Measurements

Setting STATISTICS TIME ON will not help us here. Both functions will execute so quickly that statistics time will report their execution time as zero. We’ll have to create a loop that calls the functions many times (in this example 10,000 times) and then examine the total elapsed time.

CREATE PROC TestIt_TSQL

AS

SET STATISTICS TIME OFF

SET NOCOUNT ON

DECLARE @Counter INT = 0, @TestLoops INT = 10000

DECLARE @TestX FLOAT, @Result FLOAT

SET @TestX = RAND()

PRINT SYSDATETIME()

WHILE @Counter < @TestLoops

BEGIN

SET @Result=dbo.NormalPdf_TSQL(@TestX, 0.0, 1.0)

SET @Counter = @Counter+1

END

PRINT SYSDATETIME()

GO

We then create a sescond test stored procedure, identical except that it invokes dbo.NormalPdf ( ). On the virtual machine I am using for my tests, each loop took about 0.09 seconds, meaning that, on average, both both the compiled and the TSQL functions executed in about 9 microseconds. Certainly not a lot of time in the SQL world. There may even have been a slight advantage to the classic TSQL version, but it would take averaging of many multiple trials to assert this with confidence.

Why Natively Compiled UDFs?

In my opinion, the most important reason for the application of user-defined functions in good old-fashioned classic TSQL stored procedures is code management and code reuse. As any developer knows, too much duplicated in-line code can become a nightmare when the time comes to do updates or find bugs. The same considerations of good organization and management apply to natively compiled stored procedures as well. Since a natively compiled stored procedure cannot invoke a TSQL user-defined function, there is no alternative except to use natively compiled UDFs if we wish to adhere to these same good principles of code management. However, here we make a pleasing discovery. It is often the case that “crossing boundaries” in computer code elicits a penalty, whether it is crossing from user mode to kernel mode in C code or trying to read a Matlab matrix from Microsoft Excel. In our first performance tests, we were calling a natively compiled UDF 10,000 times from a TSQL execution environment. What if we run the same loop from a fully compiled environment?

Let’s create a simple procedure to run the same test loop:

create procedure dbo.TestIt

with native_compilation, schemabinding

as

begin atomic with

(transaction isolation level = snapshot,

language = N’English’)

–SET NOCOUNT ON — not allowed (or necessary) in native compilation

DECLARE @Counter INT = 0, @TestLoops INT = 10000

DECLARE @TestX FLOAT, @Result FLOAT

SET @TestX = RAND( )

WHILE @Counter < @TestLoops

BEGIN

–SET @Result=dbo.NormalPdf_TSQL(@TestX, 0.0, 1.0) — TSQL UDFs not allowed

SET @Result=dbo.NormalPdf(@TestX, 0.0, 1.0)

SET @Counter = @Counter+1

END

end

go

We can then test the execution of this procedure as follows:

PRINT SYSDATETIME()

EXEC TestIt

PRINT SYSDATETIME()

What we find is that now the 10,000 iterations of the loop complete in roughly 9 milliseconds as opposed to 90. A roughly ten-fold improvement in performance when using the same function within fully compiled code!

Conclusion

SQL Server 2016 supports the creation of natively compiled user-defined functions. While it is possible to use natively compiled UDFs from conventional TSQL code, if any improvement in performance is seen it is likely to be modest. However, using natively compiled UDFs within natively compiled stored procedures, certainly the use intended by our friends in Redmond, we can enjoy excellent performance benefits.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.