6

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.

0
16

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:

execution plan in Plan Explorer

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;
1
  • 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.
    – Dwight T
    Apr 7 at 12:51
9

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.