# How can I eliminate this scalar function or make it faster?

I'm working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql server 2016)

Really not sure the best ways to modify this process.

Simplified select statement.

``````Select vendorpartid,
dbo.ufn_StockUomQuantityToOrder(vpp.priorityLevel, vpp.monthlyUsageRate, vpp.minimumPurchaseUomOrderQuantity, vpp.purchaseUomConversionFactor, vpp.orderFrequencyDays, vpp.boxQuantity) stockUomQuantityToOrder
FROM vendorpartpriority vpp

ALTER FUNCTION [dbo].[ufn_StockUomQuantityToOrder] (
@priorityLevel decimal(38, 10),
@monthlyUsageRate decimal(38,10),
@minimumPurchaseUomOrderQuantity decimal(38, 10),
@purchaseUomConversion decimal(38, 10),
@orderFrequencyDays decimal(38,10),
@boxQuantity int
)
RETURNS int
WITH SCHEMABINDING
AS
-- Calculate the quantity that needs to be ordered
BEGIN
DECLARE @quantityToOrderDecimal decimal(38, 10) = NULL;
DECLARE @quantityToOrderInt int = NULL;
DECLARE @orderFrequencyMinimumQuantity decimal(38, 10) = NULL;
DECLARE @minimumStockUomOrderQuantity decimal(38,10) = NULL;

--set the default order quantity
SELECT @quantityToOrderDecimal = (-1.0 * @monthlyUsageRate * @priorityLevel);

--get the minimum order quantity in stock UOM
SELECT @minimumStockUomOrderQuantity = (@minimumPurchaseUomOrderQuantity * @purchaseUomConversion);

--calculate the order frequency minimum
IF(@orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL)
SELECT @orderFrequencyMinimumQuantity = (@monthlyUsageRate * @orderFrequencyDays / 30.0);

--do we need to meet a vendor minimum
IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @minimumStockUomOrderQuantity)
SELECT @quantityToOrderDecimal = @minimumStockUomOrderQuantity;

--do we need to meet an order frequency minimum
IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @orderFrequencyMinimumQuantity)
SELECT @quantityToOrderDecimal = @orderFrequencyMinimumQuantity;

--convert to the int
SELECT @quantityToOrderInt = CAST(CEILING(@quantityToOrderDecimal) AS int);

--did we come up with a number that needs to be an increment of boxQuantity
IF(@quantityToOrderInt IS NOT NULL AND @boxQuantity > 0)
BEGIN
--get the partial box quantity if any
DECLARE @partialBox int = @quantityToOrderInt % @boxQuantity;

--remove the partial box and add a full one (if we are not of box increments)
IF(@partialBox <> 0)
BEGIN
SELECT @quantityToOrderInt = (@quantityToOrderInt - @partialBox + @boxQuantity);
END
END

RETURN @quantityToOrderInt;
END;

GO
``````

2019 isn't an option now. I have dabbled with the in-line table value, but I'm not quite sure how to get rid of the declares.

I'm not 100% sure on the nulls. My gut says I shouldn't be passing any values that are nulls, but looks like input table has quite a few in different columns.

Right now I'm trying to make some glaring performance changes until we get time to dismantle the whole process.

Rewrite the scalar function as an in-line table-valued function, or upgrade to SQL Server 2019, which will automatically perform that translation for you.

## Machine Translation

The following is the most direct T-SQL representation of the in-lined function produced by SQL Server 2019 for your scalar user-defined function. One wouldn't rewrite the function this way; I present it for its educational and entertainment value:

``````DROP FUNCTION IF EXISTS dbo.ufn_StockUomQuantityToOrder;
GO
CREATE FUNCTION dbo.ufn_StockUomQuantityToOrder
(
@priorityLevel decimal(38, 10),
@monthlyUsageRate decimal(38,10),
@minimumPurchaseUomOrderQuantity decimal(38, 10),
@purchaseUomConversion decimal(38, 10),
@orderFrequencyDays decimal(38,10),
@boxQuantity int
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
SELECT
Node0.Expr1019
FROM
(
SELECT Expr1002 = CONVERT(decimal(38,10), NULL)
) AS Node7
CROSS APPLY
(
SELECT Node10.Expr1007
FROM
(
SELECT Expr1006 =
CASE
WHEN @orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL
THEN 1
ELSE 0
END
) AS Node9
OUTER APPLY
(
SELECT Expr1007 =
CASE
WHEN Node9.Expr1006 = 1
THEN CONVERT(decimal(38,10),@monthlyUsageRate * @orderFrequencyDays/(30.0),0)
ELSE Node7.Expr1002
END
) AS Node10
) AS Node6
CROSS APPLY
(
SELECT
Expr1004 = CONVERT(decimal(38,10), -(1.0 * @monthlyUsageRate * @priorityLevel), 0),
Expr1005 = CONVERT(decimal(38,10), @minimumPurchaseUomOrderQuantity * @purchaseUomConversion, 0),
Node6.Expr1007
) AS Node5
CROSS APPLY
(
SELECT Node13.Expr1009
FROM
(
SELECT Expr1008 =
CASE
WHEN Node5.Expr1004 IS NULL OR Node5.Expr1004 < Node5.Expr1005
THEN 1
ELSE 0
END
) AS Node12
OUTER APPLY
(
SELECT Expr1009 =
CASE
WHEN Node12.Expr1008 = 1
THEN Node5.Expr1005
ELSE Node5.Expr1004
END
) AS Node13
) AS Node4
CROSS APPLY
(
SELECT Node16.Expr1011
FROM
(
SELECT Expr1010 =
CASE
WHEN Node4.Expr1009 IS NULL OR Node4.Expr1009 < Node5.Expr1007
THEN 1
ELSE 0
END
) AS Node15
OUTER APPLY
(
SELECT Expr1011 =
CASE
WHEN Node15.Expr1010 = 1
THEN Node5.Expr1007
ELSE Node4.Expr1009
END
) AS Node16
) AS Node3
CROSS APPLY
(
SELECT Expr1012 = CONVERT(int, CEILING(Node3.Expr1011), 0)
) AS Node2
CROSS APPLY
(
SELECT Node17.Expr1016
FROM
(
SELECT Expr1013 =
CASE
WHEN Node2.Expr1012 IS NOT NULL AND @boxQuantity > 0
THEN 1
ELSE 0
END
) AS Node18
OUTER APPLY
(
SELECT Node19.Expr1016
FROM
(
SELECT Expr1014 =
CASE
WHEN Node18.Expr1013 = 1
THEN Node2.Expr1012 % @boxQuantity
ELSE NULL
END
) AS Node20
CROSS APPLY
(
SELECT Node21.Expr1016
FROM
(
SELECT Expr1015 =
CASE
WHEN Node20.Expr1014 <> 0
THEN 1
ELSE 0
END
) AS Node22
OUTER APPLY
(
SELECT Expr1016 =
CASE
WHEN Node18.Expr1013 = 1 AND Node22.Expr1015 = 1
THEN Node2.Expr1012 - Node20.Expr1014 + @boxQuantity
ELSE Node2.Expr1012
END
) AS Node21
) AS Node19
) AS Node17
) AS Node1
CROSS APPLY
(
SELECT
Expr1019 = CONVERT(int, Node1.Expr1016, 0)
) AS Node0;
GO
``````

Example call:

``````DECLARE
@priorityLevel decimal(38, 10),
@monthlyUsageRate decimal(38,10),
@minimumPurchaseUomOrderQuantity decimal(38, 10),
@purchaseUomConversion decimal(38, 10),
@orderFrequencyDays decimal(38,10),
@boxQuantity int

SELECT
stockUomQuantityToOrder = FN.Expr1019
FROM dbo.ufn_StockUomQuantityToOrder
(
@priorityLevel,
@monthlyUsageRate,
@minimumPurchaseUomOrderQuantity,
@purchaseUomConversion,
@orderFrequencyDays,
@boxQuantity
) AS FN;
``````

The execution plan is: ## Human Translation

This human would rewrite your scalar function as:

``````CREATE FUNCTION dbo.ufn_StockUomQuantityToOrder
(
@priorityLevel decimal(38, 10),
@monthlyUsageRate decimal(38,10),
@minimumPurchaseUomOrderQuantity decimal(38, 10),
@purchaseUomConversion decimal(38, 10),
@orderFrequencyDays decimal(38,10),
@boxQuantity int
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
SELECT
stockUomQuantityToOrder = Step6.quantityToOrderInt
FROM
(
SELECT
--set the default order quantity
quantityToOrderDecimal =
CONVERT
(
decimal(38,10),
-1.0 * @monthlyUsageRate * @priorityLevel
),
--get the minimum order quantity in stock UOM
minimumStockUomOrderQuantity =
CONVERT
(
decimal(38,10),
@minimumPurchaseUomOrderQuantity * @purchaseUomConversion
),
--calculate the order frequency minimum
orderFrequencyMinimumQuantity =
CONVERT
(
decimal(38,10),
IIF
(
@orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL,
@monthlyUsageRate * @orderFrequencyDays / 30.0,
NULL
)
)
) AS Step1
CROSS APPLY
(
SELECT
--do we need to meet a vendor minimum
quantityToOrderDecimal =
IIF
(
Step1.quantityToOrderDecimal IS NULL OR Step1.quantityToOrderDecimal < Step1.minimumStockUomOrderQuantity,
Step1.minimumStockUomOrderQuantity,
Step1.quantityToOrderDecimal
),
Step1.orderFrequencyMinimumQuantity
) AS Step2
CROSS APPLY
(
SELECT
--do we need to meet an order frequency minimum
quantityToOrderDecimal =
IIF
(
Step2.quantityToOrderDecimal IS NULL OR Step2.quantityToOrderDecimal < Step2.orderFrequencyMinimumQuantity,
Step2.orderFrequencyMinimumQuantity,
Step2.quantityToOrderDecimal
)
) AS Step3
CROSS APPLY
(
SELECT
--convert to the int
quantityToOrderInt = CAST(CEILING(Step3.quantityToOrderDecimal) AS integer)
) AS Step4
CROSS APPLY
(
SELECT
Step4.quantityToOrderInt,
--get the partial box quantity if any
partialBox = CONVERT(integer, Step4.quantityToOrderInt % @boxQuantity)
) AS Step5
CROSS APPLY
(
SELECT
--did we come up with a number that needs to be an increment of boxQuantity
quantityToOrderInt =
IIF
(
Step5.quantityToOrderInt IS NOT NULL AND @boxQuantity > 0,
--remove the partial box and add a full one (if we are not of box increments)
IIF
(
Step5.partialBox != 0,
Step5.quantityToOrderInt - Step5.partialBox + @boxQuantity,
Step5.quantityToOrderInt
),
Step5.quantityToOrderInt
)
) AS Step6;
``````

The original view in the question becomes:

``````SELECT
VPP.vendorpartid,
FN.stockUomQuantityToOrder
FROM dbo.vendorpartpriority AS VPP
CROSS APPLY dbo.ufn_StockUomQuantityToOrder
(
VPP.priorityLevel,
VPP.monthlyUsageRate,
VPP.minimumPurchaseUomOrderQuantity,
VPP.purchaseUomConversionFactor,
VPP.orderFrequencyDays,
VPP.boxQuantity
) AS FN;
``````
• Charlie and Paul, thank you very much for the information. Lots to chew on here. I did use Charile's code last night and the performance was increased significantly. Now to compare these two for a better understanding. Apr 7 at 12:51

Your best bet is to use an inline Table-Valued Function. This acts like a parameterized view, and it can contain almost anything you can shoehorn into a single query.

It will effectively be pasted/inlined directly into the outer query, so it should be very fast.

We can use a series of `CROSS APPLY` to replace `DECLARE/SELECT/SET`:

``````CREATE OR ALTER FUNCTION [dbo].[tvf_StockUomQuantityToOrder] (
@priorityLevel decimal(38, 10),
@monthlyUsageRate decimal(38,10),
@minimumPurchaseUomOrderQuantity decimal(38, 10),
@purchaseUomConversion decimal(38, 10),
@orderFrequencyDays decimal(38,10),
@boxQuantity int
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
-- Calculate the quantity that needs to be ordered
(
SELECT
--did we come up with a number that needs to be an increment of boxQuantity
CASE WHEN v4.quantityToOrderInt IS NOT NULL AND v5.partialBox <> 0
--remove the partial box and add a full one (if we are not of box increments)
THEN
(v4.quantityToOrderInt - v5.partialBox + @boxQuantity)
ELSE
v4.quantityToOrderInt
END

FROM (VALUES (
--set the default order quantity
-1.0 * @monthlyUsageRate * @priorityLevel,

--get the minimum order quantity in stock UOM
@minimumPurchaseUomOrderQuantity * @purchaseUomConversion,

--calculate the order frequency minimum
CASE WHEN @orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL
THEN @monthlyUsageRate * @orderFrequencyDays / 30.0
END

) ) AS v1(quantityToOrderDecimal, minimumStockUomOrderQuantity, orderFrequencyMinimumQuantity)

CROSS APPLY (VALUES (
--do we need to meet a vendor minimum
CASE WHEN v1.quantityToOrderDecimal IS NULL OR v1.quantityToOrderDecimal < v1.minimumStockUomOrderQuantity THEN
v1.minimumStockUomOrderQuantity
END
) ) AS v2(quantityToOrderDecimal)

CROSS APPLY (VALUES (
--do we need to meet an order frequency minimum
CASE WHEN v2.quantityToOrderDecimal IS NULL OR v2.quantityToOrderDecimal < v1.orderFrequencyMinimumQuantity
THEN v1.orderFrequencyMinimumQuantity END
) ) AS v3(quantityToOrderDecimal)

CROSS APPLY (VALUES (
--convert to the int
CAST(CEILING(v3.quantityToOrderDecimal) AS int)
) ) AS v4(quantityToOrderInt)

CROSS APPLY (VALUES (
--get the partial box quantity if any
CASE WHEN @boxQuantity > 0 THEN v4.quantityToOrderInt % @boxQuantity END
) ) AS v5(partialBox)

);

GO
``````

You can change `VALUES` to `SELECT val =` if you feel it helps readability.