Uploaded image for project: 'iDempiere'
  1. IDEMPIERE-3636

Add AD_PInstance_ID in update T_InventoryValue where clause in InventoryValue

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Sprint:

      Description

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

      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 * *

      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)");
      

        Attachments

          Activity

            People

            • Assignee:
              carlosruiz_globalqss Carlos Ruiz
              Reporter:
              pritesh.shah17 Pritesh Shah
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: