Optimize Cost Roll Up query

[Pritesh Shah]

Hello All,

I was going through the cost roll up process and found we can optimize the query as below 

WITH productBOM as (select b.BOMQty, c.currentcostprice, c.futurecostprice FROM M_Product_BOM b INNER JOIN M_Cost c ON (b.M_PRODUCTBOM_ID = c.M_Product_ID) WHERE b.M_Product_ID = <productID> AND M_CostElement_ID = <costElementID>)

UPDATE M_Cost set 

CurrentCostPrice = COALESCE((select Sum (BOMQty * currentcostprice) FROM productBOM ),0), 

FutureCostPrice = COALESCE((select Sum (BOMQty * futurecostprice) FROM productBOM),0) 

WHERE M_Product_ID = <productID> AND AD_Client_ID = <ad_client_ID> AND M_CostElement_ID = <costelementID> AND exists (SELECT M_PRODUCT_ID FROM M_PRODUCT_BOM where m_product_id=<productID>) 

Current query as below,

UPDATE M_Cost set 

CurrentCostPrice = COALESCE((select Sum (b.BOMQty * c.currentcostprice) FROM M_Product_BOM b INNER JOIN M_Cost c ON (b.M_PRODUCTBOM_ID = c.M_Product_ID) WHERE b.M_Product_ID = <productID> AND M_CostElement_ID = <costelementID>),0), 

FutureCostPrice = COALESCE((select Sum (b.BOMQty * c.futurecostprice) FROM M_Product_BOM b INNER JOIN M_Cost c ON (b.M_PRODUCTBOM_ID = c.M_Product_ID) WHERE b.M_Product_ID = <productID> AND M_CostElement_ID = <costelementID>),0) 

WHERE M_Product_ID = <productID> AND AD_Client_ID = <ad_client_ID> AND M_CostElement_ID = <costelementID> AND M_PRODUCT_ID IN (SELECT M_PRODUCT_ID FROM M_PRODUCT_BOM) 

Please check above optimize query and let me know we can improve a little performance.