Soften the RBAR impact with Native Compiled UDFs in SQL Server 2016

Posted by

This post has been republished via RSS; it originally appeared at: DataCAT articles.

First published on MSDN on Feb 17, 2016

Reviewers: Joe Sack, Denzil Ribeiro, Jos de Bruijn


Many of us are very familiar with the negative performance implications of using scalar UDFs on columns in queries: my colleagues have posted about issues here and here . Using UDFs in this manner is an anti-pattern most of us frown upon, because of the row-by-agonizing-row (RBAR) processing that this implies. In addition, scalar UDF usage also limits the optimizer to use serial plans. Overall, evil personified!

Native Compiled UDFs introduced


Though the problem with scalar UDFs is well-known, we still come across workloads where this problem is a serious detriment to the performance of the query. In some cases, it may be easy to refactor the UDF as an inline Table Valued Function, but in other cases, it may simply not be possible to refactor the UDF.

SQL Server 2016 offers natively compiled UDFs , which can be of interest where refactoring the UDF to a TVF is not possible, or where the number of referring T-SQL objects  are simply too many. Natively compiled UDFs will NOT eliminate the RBAR agony, but they can make each iteration incrementally faster, thereby reducing the overall query execution time. The big question is how much?

Real-life results


We recently worked with an actual customer workload in the lab. In this workload, we had a query which invoked a scalar UDF in the output list. That means that the UDF was actually executing once per row – in this case a total of 75 million rows! The UDF has a simple CASE expression inside it. However, we wanted to improve query performance so we decided to try rewriting the UDF.

We found the following results with the trivial UDF being refactored as a TVF versus the same UDF being natively compiled (all timings are in milliseconds):

Interpreted (classic)



Native compiled (new in SQL Server 2016)



TVF



CPU Time



12734



8906



3735



Elapsed time



13986



8906



3742



As can be expected, the TVF approach is the fastest, but it is encouraging that the native compiled UDF reduced execution time by solid 36% even though the logic in the UDF was very trivial!

Test Scenario


In order to take this further, I decided to do some testing with a neutral workload. For this neutral test I used the DB1BTicket table which is 230+ million rows and in my test database had a Clustered Columnstore Index created on it.

In this test, I used two different types of UDFs: a trivial one and another one which has more conditional statements in it. The scripts for these are at the end of the post. The results with these different iterations are summarized in the table below:

Interpreted (classic)



Native compiled (new in SQL Server 2016)



TVF



Simple UDF



1672.239 seconds



796.427 seconds



10.473 seconds



Multi-condition UDF



3763.584 seconds



848.106 seconds



Not attempted



Side Note: Parallelism


It is well known that UDFs (even those which do not access data and just do computation) cause a serial plan to be used. Here is the plan with interpreted UDF – as you can see it is serial:



Here is the plan with native compiled UDF – it is still serial:



Lastly, here is the execution plan with TVF – as you can see it is a parallel plan:



Here’s the million-dollar question to you: how badly do you want SQL Server to support parallel plans when UDFs are used anywhere in the plan? Do send us your feedback as comments.



Conclusion


While refactoring the scalar UDF as a TVF ultimately provided the best results, in cases where it is not possible to do this, using native compiled UDFs provides a very useful reduction in query execution time. Therefore, native compiled UDFs can be used as a mitigation or even considered a solution to those thorny cases where RBAR is absolutely necessary.

Appendix: UDF Definitions


Here is the simple UDF, in the classic T-SQL interpreted form:


CREATE FUNCTION dbo.FarePerMile ( @Fare MONEY, @Miles INT )


RETURNS MONEY


WITH SCHEMABINDING


AS


BEGIN


DECLARE @retVal MONEY = ( @Fare / @Miles );



RETURN @retVal;


END;


GO



Here is the simple UDF written as a native compiled version:


CREATE FUNCTION dbo.FarePerMile_native (@Fare money, @Miles int)


RETURNS MONEY


WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER


AS


BEGIN ATOMIC


WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')



DECLARE @retVal money = ( @Fare / @Miles)


RETURN @retVal


END



Here is the simple UDF refactored as a TVF:


CREATE FUNCTION dbo.FarePerMile_TVF ( @Fare MONEY, @Miles INT )


RETURNS TABLE


AS


RETURN


SELECT  ( @Fare / @Miles ) AS RetVal;



Now, the multiple condition UDF, in the classic T-SQL interpreted form:


CREATE FUNCTION dbo.FictionalPricingLogic


(


@RPCarrier VARCHAR(2) ,


@Origin VARCHAR(3) ,


@Fare MONEY ,


@Miles INT


)


RETURNS MONEY


WITH SCHEMABINDING


AS


BEGIN


DECLARE @retVal MONEY;


DECLARE @discount MONEY = 0; -- discount percentage



IF ( @RPCarrier = 'DL' )


SELECT  @discount += 0.05;


IF ( @RPCarrier = 'AA' )


SELECT  @discount += 0.05;


IF ( @Origin = 'DFW' )


SELECT  @discount += 0.01;


IF ( @Origin = 'SEA' )


SELECT  @discount += 0.009;


IF ( @Miles > 500 )


SELECT  @discount += 0.01;



SELECT  @retVal = @Fare * ( 1.0 - @discount );



RETURN @retVal;


END;



Here is the multiple condition UDF written as a native compiled version:


CREATE FUNCTION dbo.FictionalPricingLogic_Native


(


@RPCarrier VARCHAR(2) ,


@Origin VARCHAR(3) ,


@Fare MONEY ,


@Miles INT


)


RETURNS MONEY
WITH native_compilation
,schemabinding
,EXECUTE AS OWNER
AS
BEGIN
atomic
WITH (
TRANSACTION ISOLATION LEVEL = snapshot
,LANGUAGE = N'us_english'
)


DECLARE @retVal MONEY;


DECLARE @discount MONEY = 0; -- discount percentage



IF ( @RPCarrier = 'DL' )


SELECT  @discount += 0.05;


IF ( @RPCarrier = 'AA' )


SELECT  @discount += 0.05;


IF ( @Origin = 'DFW' )


SELECT  @discount += 0.01;


IF ( @Origin = 'SEA' )


SELECT  @discount += 0.009;


IF ( @Miles > 500 )


SELECT  @discount += 0.01;



SELECT  @retVal = @Fare * ( 1.0 - @discount );



RETURN @retVal;


END;



In this test, assume that it was not worth refactoring the multiple condition UDF as a TVF.



Appendix: Test Queries


Here are the sample queries used to test the performance of each of the above UDF variations:


SET STATISTICS TIME ON;


SET STATISTICS IO ON;



SELECT  AVG(dbo.FarePerMile(ItinFare, MilesFlown))


FROM    DB1BTicket;


GO



SELECT  AVG(dbo.FarePerMile_Native(ItinFare, MilesFlown))


FROM    DB1BTicket;


GO


The below query is the version with the TVF. Note the usage of CROSS APPLY:


SELECT  AVG(myTVF.RetVal)


FROM    DB1BTicket


CROSS APPLY dbo.FarePerMile_TVF(ItinFare, MilesFlown) AS myTVF;


GO



SELECT  AVG(dbo.FictionalPricingLogic(RPCarrier, Origin, ItinFare, MilesFlown))


FROM    DB1BTicket;


GO



SELECT  AVG(dbo.FictionalPricingLogic_Native(RPCarrier, Origin, ItinFare,


MilesFlown))


FROM    DB1BTicket;


GO

This articles are republished, there may be more discussion at the original link. But if you found this helpful, you're more than welcome to let us know!

This site uses Akismet to reduce spam. Learn how your comment data is processed.