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.
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)");
Environment
None
Attachments
1
24 Feb 2018, 05:44 PM
Activity
Show:
Pritesh Shah February 24, 2018 at 5:45 PM
I am sorry the query gave you error. I had mentioned that we are doing periodic cleaning of T_* table as intended. Though due to huge load of data it creates performance issues.
Please find attached patch
. I don't have working repository of current idempiere. I am using idempiere 2.0. So please check if patch works for you.
Carlos Ruiz February 24, 2018 at 1:52 PM
Sorry @Pritesh Shah, the suggested patch throws error in postgresql.
Please provide a patch in diff format for your tested working code.
Wondering if you're confronting issues because of not cleaning the T_InventoryValue table? The tables starting with T_ are temporary, intended to be cleaned periodically.
Major
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)");