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

deleted 17 characters in body

Source Link

edited Apr 7, 2021 at 9:36

14.5k
14
40

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
        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 < v2v1.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

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
        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 < v2.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

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

deleted 10 characters in body

Source Link

edited Apr 7, 2021 at 9:23

Charlieface

14.5k
14
40

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 THEN
               --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
        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 < v2.orderFrequencyMinimumQuantity THEN
            THEN v2v1.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

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 THEN
               --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
        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 < v2.orderFrequencyMinimumQuantity THEN
            THEN v2.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

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
        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 < v2.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

Source Link

created Apr 7, 2021 at 2:30

Charlieface

14.5k
14
40

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 THEN
               --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
        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 < v2.orderFrequencyMinimumQuantity THEN
            THEN v2.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.

Stack Exchange Network

Return to Answer