We're updating the issue view to help you get more done. 

Add AD_PInstance_ID in update T_InventoryValue where clause in InventoryValue

Description

With huge data below query is performing slow, though we are cleaning up temporary table periodically.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 sql = new StringBuilder ("UPDATE T_InventoryValue iv ") .append("SET (Cost, M_CostElement_ID)=") .append("(SELECT c.CurrentCostPrice, c.M_CostElement_ID ") .append("FROM M_Warehouse w") .append(" INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)") .append(" INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)") .append(" INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID") .append(" AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID)) ") .append("WHERE c.M_CostElement_ID=").append(p_M_CostElement_ID) .append(" AND iv.M_Warehouse_ID=w.M_Warehouse_ID") .append(" AND iv.M_Product_ID=c.M_Product_ID") .append(" AND iv.M_AttributeSetInstance_ID=c.M_AttributeSetInstance_ID) ") .append("WHERE EXISTS (SELECT * FROM T_InventoryValue ivv ") .append("WHERE ivv.AD_PInstance_ID=").append(getAD_PInstance_ID()) .append(" AND ivv.M_CostElement_ID IS NULL)");

To improve performance, minor change required is as below between the * *

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 sql = new StringBuilder ("UPDATE T_InventoryValue iv ") .append("SET (Cost, M_CostElement_ID)=") .append("(SELECT c.CurrentCostPrice, c.M_CostElement_ID ") .append("FROM M_Warehouse w") .append(" INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)") .append(" INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)") .append(" INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID") .append(" AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID)) ") .append("WHERE c.M_CostElement_ID=").append(p_M_CostElement_ID) .append(" AND iv.M_Warehouse_ID=w.M_Warehouse_ID") .append(" AND iv.M_Product_ID=c.M_Product_ID") .append(" AND iv.M_AttributeSetInstance_ID=c.M_AttributeSetInstance_ID) ") * .append(" AND iv.AD_PInstance_ID=").append(getAD_PInstance_ID())* .append("WHERE EXISTS (SELECT * FROM T_InventoryValue ivv ") .append("WHERE ivv.AD_PInstance_ID=").append(getAD_PInstance_ID()) .append(" AND ivv.M_CostElement_ID IS NULL)");

Environment

None

Status

Assignee

Carlos Ruiz

Reporter

Pritesh Shah

Labels

None

Tested By

None

Priority

Major