Reviewers: Joe Sack, Denzil Ribeiro, Jos de Bruijn
Interpreted (classic) |
Native compiled (new in SQL Server 2016) |
TVF |
|
CPU Time |
12734 |
8906 |
3735 |
Elapsed time |
13986 |
8906 |
3742 |
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 |
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.