snippetsqlModerate

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thiscanscalarmakefunctionfastereliminatehow

Problem

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 (@quantityToOrderDecim

Solution

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  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
),

Code Snippets

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

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;

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,

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;

Context

StackExchange Database Administrators Q#289391, answer score: 17

Revisions (0)

No revisions yet.