Update Costprice in inventory valuation report takes too much time

[Pritesh Shah]

Hello,

I was observing slow queries and found when t_inventoryvalue table has huge number of rows say 1 million (there is housekeeping scheduler running which wipes out data however table still gets too many rows due to huge data in clients). Update query to update currentcostprice is taking too much time and i have observed if we add ad_pinstance_id in where clause performance can be improved. Would it be fine in terms of entire query?

Query

UPDATE T_InventoryValue iv SET (Cost, M_CostElement_ID)=(SELECT c.CurrentCostPrice, c.M_CostElement_ID FROM M_Warehouse w
INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)
INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)
INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID))
WHERE c.M_CostElement_ID=p_M_CostElement_ID AND iv.M_Warehouse_ID=w.M_Warehouse_ID AND iv.M_Product_ID=c.M_Product_ID AND iv.M_AttributeSetInstance_ID=c.M_AttributeSetInstance_ID  AND iv.ad_pinstance_id=getAD_PInstance_ID()) WHERE EXISTS (SELECT * FROM T_InventoryValue ivv WHERE ivv.AD_PInstance_ID= getAD_PInstance_ID() AND ivv.M_CostElement_ID IS NULL)

Carlos, do you have inputs here?

[Pritesh Shah]

Can anyone validate above change?