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

Question

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.

Answer 1

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;

Answer 2

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.