Carry Over Balance

[jovillanuev]

I have a query that get the balance for last month and current month balance. I'm using a function inside the view that took more than 30 minutes. May I ask your help guys any idea or approach to speed up the processing or optimize the script it can be done in CTE. thank you.

below is a sample data and actual result.

select
      t.po_month
      /*** identity the next month date ***/
  ,SUBSTRING(CAST(CONVERT(date,DATEADD(month,1,CONVERT(datetime,SUBSTRING(t.po_month,1,4)+SUBSTRING(t.po_month,6,2)+'01'))) AS VARCHAR),1,7) AS next_month
  ,t.item
  /** how can i achive this part in cte ****/
      ,CASE WHEN OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM) - balance_lm < 0 THEN 0 
                  ELSE OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm 
            END AS Order_Qty_1
      ,CASE WHEN (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM)- balance_lm) >= 0 THEN 0 
                  ELSE (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm) * -1 
            END AS balance_cm
      ,t.balance_lm 
     /**************************************/

  ,t.OEM_Need

  ,t.OnHand_R
  ,t.OnHand_RB
  ,t.OnHand_S
  ,t.OnHand_OEM

  ,t.OnOrder_RG
  ,t.OnOrder_RB
  ,t.OnOrder_S
  ,t.OnOrder_OEM
  ,row_number() over (partition by po_month, item order by po_month) as rnk
from
(
select  oe.po_month, oe.item, 
        sum(case when oe.oemneed_qty is null then 0 else oemneed_qty end) as OEM_Need,
        sum(case when oh.R is null then 0 else oh.R end) as OnHand_R,
    sum(case when oh.RB is null then 0 else oh.RB end) as OnHand_RB,
    sum(case when oh.S is null then 0 else oh.S end) as OnHand_S,
    sum(case when oh.oem is null then 0 else oh.oem end) as OnHand_OEM ,  

    sum(case when op.RG is null then 0 else op.RG end) as OnOrder_RG,
    sum(case when op.RB is null then 0 else op.RB end) as OnOrder_RB,
    sum(case when op.S is null then 0 else op.S end) as OnOrder_S,
    sum(case when op.oem is null then 0 else op.oem end) as OnOrder_OEM

    /** this is the function that capture the balance_lm from previous balance_cm***/
    --,dbo.get_carry_over_oem_qty(oen.PO_Month,oen.item) AS balance_lm
from    #oemneed oe
        inner join  #onhand oh 
        on oe.po_month= oh.oh_month
        inner join #openorder op
        on oe.po_month= op.del_month
where   oe.po_month >= ('2016-09') and oe.item='A1003'
group by
       oe.po_month
      ,oe.item
)t

formula getting the balance_cm

      ,CASE WHEN (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM)- balance_lm) >= 0 THEN 0 
                  ELSE (OEM_Need - (OnHand_R + OnHand_RB + OnHand_S + OnHand_OEM+ OnOrder_RG + OnOrder_RB + OnOrder_S + OnOrder_OEM )- balance_lm) * -1 
            END AS balance_cm

sample desired result

po_month---next_month--item---Order_Qty_1--balance_cm---balance_lm---OEM_Need---OnHand_R------OnHand_RB---OnHand_S---OnHand_OEM---OnOrder_RG--OnOrder_RB--OnOrder_S---OnOrder_OEM
2016-09----2016-10-----A1003----0-----------413434-------0-------------596---------184--------------0---------20---------124324-------0-----------0-----------0---------165520
2016-10----2016-11-----A1003----0-----------153694-------413434--------488---------200--------------0---------50---------10000--------0-----------0-----------0---------20000

[ryantroop]

If that's the same calculation each time in that case statement, why not do it once as its own output, and use that value's alias in your case statement. You are then only doing the calculation once, and its a scalar compare in the case statement.

As for the rest of what you are doing, and optimization, without some sort of trace analyzation it will be guesswork based on understanding how databases work... are all your indexes in order? Do you have to do the partition? Can you use local variables instead and figure out how to do the same work with joins?

Can your function be optimized in a similar fashion as the case statements? Is the function using appropriate indexes? Etc... etc.... etc...........

Edit:

So after reading a bit... I still think you're making this very complex. It might be worth while to try the most simple route first to see if that speeds things up, and only add on extended features and overhead when necessary. I'm not sure why you think CTE is the route to go for the valuation, unless the intent is to make this query run across multiple date ranges for various months.. if it is, I would still think there is a more optimal way, as the WITH () ... construct tends to be a shortcut, except where there is no other way (which, I cant really think of any, but Im sure they exist).

[jovillanuev]

Thanks ryantroop for the reply. actually, they wanted me to optimize this script but u have a hard time figure out to optmize this query. everytime this funciton call by the view gets exhausted and also the view is referencing the same view.

[ryantroop]

Well... it is not written the way I would personally do it... it looks like too much is being attempted because "it can be done" instead of taking a direct approach to the problem. Personally, I would break it apart into smaller pieces and work with that.. Seems over complicated :-/ However, you may be correct in that I just "don't understand" it... Something like this I would likely take a profiler to, and start breaking it apart based on where the highest load is.