Need help optimizing TSQL table valued function

Question

Here is the function:

CREATE FUNCTION dbo.agedItemResult(@ILC VARCHAR(30))
    RETURNS @AgedTable TABLE (
ItemID INT,
ItemLookupCode VARCHAR(30),
[Month] INT,
[Year] INT,
Aged INT,
Debug01 VARCHAR(MAX),
Debug02 VARCHAR(MAX),
Debug03 VARCHAR(MAX),
Debug04 VARCHAR(MAX),
Debug05 VARCHAR(MAX)
) 
AS
BEGIN

DECLARE @TotalSold INT
DECLARE @AdjustmentQty INT
DECLARE @FirstRcvdStart DATETIME
DECLARE @FirstRcvdEnd DATETIME
--DECLARE @ILC VARCHAR(MAX)
--DECLARE @ItemID INT
DECLARE @MovingQty INT
DECLARE @MovingMonthStart DATETIME
DECLARE @MovingMonthEnd DATETIME
DECLARE @CurrentMonthStart DATETIME
DECLARE @CurrentMonthEnd DATETIME
DECLARE @CurrentRcvd INT
DECLARE @NumMonths INT
DECLARE @AgeReached INT
/*DECLARE @AgedTable TABLE (    ItemID INT,
                            ItemLookupCode VARCHAR(MAX),
                            [Month] INT,
                            [Year] INT,
                            Aged INT,
                            Debug01 VARCHAR(MAX),
                            Debug02 VARCHAR(MAX),
                            Debug03 VARCHAR(MAX),
                            Debug04 VARCHAR(MAX),
                            Debug05 VARCHAR(MAX)
                            )*/

--SET @ILC = '21208641'
SET @TotalSold          = (SELECT SUM(Sold) FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC)
SET @AdjustmentQty      = (SELECT SUM(HQAdjustment) FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC)
SET @FirstRcvdStart     = (SELECT TOP 1 FirstTransferred FROM JSInventorySnapShot ORDER BY FirstTransferred ASC)
SET @FirstRcvdEnd       = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @FirstRcvdStart)+1,0)))
SET @CurrentMonthStart  = (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, GETDATE()),0))) 
SET @CurrentMonthEnd    = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, GETDATE())+1,0))) 
SET @NumMonths          = (SELECT CASE 
                            WHEN DATEPART(DAY, @FirstRcvdStart) > DATEPART(DAY, @CurrentMonthEnd) 
                                THEN DATEDIFF(MONTH, @FirstRcvdStart, @CurrentMonthEnd) - 1 
                                ELSE DATEDIFF(MONTH, @FirstRcvdStart, @CurrentMonthEnd) 
                            END) + 1
SET @MovingQty          = @TotalSold
SET @CurrentRcvd        = ISNULL((SELECT SUM(Rcvd) FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC AND [Month] = Month(@FirstRcvdStart)), 0) + @AdjustmentQty
SET @MovingMonthStart   = @FirstRcvdStart
SET @MovingMonthEnd     = @FirstRcvdEnd
SET @MovingQty          = @MovingQty - @CurrentRcvd
SET @AgeReached         = 0

WHILE(@NumMonths > 0)
BEGIN
    IF (@CurrentRcvd = 0)
    BEGIN
        INSERT INTO @AgedTable (ItemID, ItemLookupCode, [Month], [Year], Aged, Debug01) 
            SELECT (SELECT TOP 1 ItemID FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC),
                        @ILC, 
                        MONTH(@MovingMonthStart),
                        YEAR(@MovingMonthStart),
                        0,
                        @AdjustmentQty
    END
    ELSE
    BEGIN
        IF(@MovingQty > 0)
        BEGIN
            INSERT INTO @AgedTable (ItemID, ItemLookupCode, [Month], [Year], Aged, Debug01) 
                SELECT (SELECT TOP 1 ItemID FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC),
                            @ILC, 
                            MONTH(@MovingMonthStart),
                            YEAR(@MovingMonthStart),
                            0,
                            @AdjustmentQty
        END
        ELSE
        BEGIN
            IF (@AgeReached = 1)
            BEGIN
                INSERT INTO @AgedTable (ItemID, ItemLookupCode, [Month], [Year], Aged) 
                    SELECT (SELECT TOP 1 ItemID FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC),
                                @ILC, 
                                MONTH(@MovingMonthStart),
                                YEAR(@MovingMonthStart),
                                @CurrentRcvd
            END
            ELSE
            BEGIN
                INSERT INTO @AgedTable (ItemID, ItemLookupCode, [Month], [Year], Aged) 
                    SELECT (SELECT TOP 1 ItemID FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC),
                                @ILC, 
                                MONTH(@MovingMonthStart),
                                YEAR(@MovingMonthStart),
                                (-1 * @MovingQty)
                    SET @AgeReached = 1
            END
        END
    END
    SET @NumMonths          = @NumMonths - 1
    SET @MovingMonthStart   = (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0, @MovingMonthStart)+1,0))) 
    SET @MovingMonthEnd     = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @MovingMonthEnd)+2,0)))
    SET @CurrentRcvd        = ISNULL((SELECT SUM(Rcvd) FROM JSInventorySnapShot WHERE ItemLookupCode = @ILC AND [Month] = Month(@MovingMonthStart)), 0)
    SET @MovingQty          = @MovingQty - @CurrentRcvd
    END
    RETURN
END

Here's what it does in a nutshell:

First we find the first item ever received or moved and use the date as the starting date. Then we find the total sales for the that item, and begin subtracting the receiving amount from sales. The remainder is considered aged (not sold) and is evaluated against the next month. We then move to the next month and repeat the process until we hit a negative number or 0. If we hit a negative number then that means we have hit a month that we ordered more than sold to date, and that amount is considered aged (not sold). If there are more months to evaluate then any FURTHER received amount is considered aged by default as it clearly as not been sold.

Here is sample data from the table used for evaluation:

ID      ItemID  ItemLookupCode  HQAdjustment    FirstTransferred        Rcvd    RcvdDateEarly           RcvdDateLate            Sold    SoldDateEarly           SoldDateLate            Sales   Month   Year    StartOfMonthQty EndOfMonthQty
17350   188993  21208641        0               2012-04-05 13:29:14.000 48      2012-04-27 11:00:40.100 2012-04-27 11:00:40.100 40      2012-04-05 13:29:14.000 2012-04-30 12:50:13.000 75.20   4       2012    0               8
34427   188993  21208641        0               2012-04-05 13:29:14.000 120     2012-05-22 10:14:40.213 2012-05-22 10:14:40.213 24      2012-05-04 17:42:12.000 2012-05-29 10:22:10.000 44.72   5       2012    8               104
48437   188993  21208641        0               2012-04-05 13:29:14.000 0       NULL                    NULL                    12      2012-06-02 16:24:45.000 2012-06-21 11:15:05.000 22.36   6       2012    104             92
62369   188993  21208641        0               2012-04-05 13:29:14.000 60      2012-07-16 12:57:33.330 2012-07-16 12:57:33.330 9       2012-07-11 14:42:01.000 2012-07-25 14:58:41.000 16.22   7       2012    92              143
75781   188993  21208641        0               2012-04-05 13:29:14.000 0       NULL                    NULL                    2       2012-08-01 12:56:10.000 2012-08-14 19:01:00.000 4.01    8       2012    143             141

Here is the result of my function for the above data:

ItemLookupCode  Month   Rcvd    Sold    Aged
21208641        4       48      40      0
21208641        5       120     24      81
21208641        6       0       12      0
21208641        7       60      9       60
21208641        8       0       2       0

This tells me that we sold everything received in April. Ordered again in May, sold some but still have left over inventory (aged). Then without selling the inventory out in July we ordered more again, and therefore that inventory is automatically considered to be aged.

The purpose of this function is stop what happened above. Which is ordering items that have aged (unsold) inventory. It's also used for evaluating how much of a department is aged and if we should consider starting a sale to get rid of aged items.

The problem is that we have no less then 40,000 items to evaluate. This function works great for single items, however it takes 30 seconds for 100 items. I'm hoping someone can come a long and look at my function and realize I'm going about it the completely wrong way. If I could accomplish inline calculations and carry over the remainder that would be great.

I basically need this in simplified pseudo-code:

Foreach (Month2Date)
If AllInventoryAged = 1 Then //This says we've reached our sales:rcved thresh-hold and all received inventory is aged from here on out.
   ThisMonthAged = RcvedForMonth
Else
   TotalSales - RcvedForMonth = Aged
    If Aged < 0 Then
        ThisMonthAged = Aged * -1 //We received more than sold so it's negative and we have hit our sales:rcved thresh-hold.
        AllInventoryAged = 1 //Anything received from this point on is aged.
    Else    
        ThisMonthAged = 0  //We received less this month then sold. A good thing.
        TotalSales = Aged //We carry on the remainder for further evaluation

I hope I have been thorough in explaining myself this time. Sorry it's so long. I just really wanted to cover all questions I had in my last post.

I don't get how the numbers are calculated. Why is Aged = 81 in month 5, rather than 96=120-24? — Gordon Linoff, Sep 14 '12 at 15:25
@GordonLinoff Aged in month 5 is 81 because you take TOTAL sales - not sales from that month. Which would be 87 then subtract the receiving of 48 from the first month. 39. The fact it is positive says that we sold all of receiving for the first month leaving 0 aged. Carry 39 over to the next month and subtract the receiving from it. Giving you -81. That means we sold all but 81 units received that month. The fact it is negative says we have received more than sold. From that point on any further inventory is automatically aged, because we are selling oldest inventory first. — jayEss, Sep 14 '12 at 16:26
Are you just trying to get the difference between the cumulative sums of total sales and total received? You can do that in a single query. — Gordon Linoff, Sep 14 '12 at 17:16
@GordonLinoff Very similar Gordon. I'm looking to take total sales and evaluate it against the receiving month by month (not TOTAL received) I need to know what month we over-ordered (7 [July] in the above example). There is one more "movement" type I need to pay attention to which is called HQAdjustments in the main data source. It needs to be broken down by month. I am open to any suggestions. I've re-written this question and the function twice, and really need another set of skilled eyes on it. — jayEss, Sep 14 '12 at 17:34
Could this be done with a CTE instead? Any direction at all? — jayEss, Sep 19 '12 at 18:00

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

I eventually solved this by re-evaluating what I was trying to do. I was able to use a Recursive CTE. You can see my related "test" code here:

TSQL - Recursive CTE inefficient - Need an alternative

edited May 23 '17 at 11:43

Community

1
1

answered Oct 11 '12 at 13:56

jayEss

129
1
1
8

Need help optimizing TSQL table valued function

1 Answers1