details: https://code.openbravo.com/erp/devel/pi/rev/0bfbdaa123e3 changeset: 32205:0bfbdaa123e3 user: Mark <markmm82 <at> gmail.com> date: Wed May 24 16:13:14 2017 -0400 summary: Fixes issue 35959: Performance problem in Costing Migration Process
Due the big quantity of records in m_transaction table, the insertTrx() method of CostingMigrationProcess was consuming a lot of time to process and it was making an important use of the server's resources. To avoid that, problem was splitted in more simple tasks, instead of insert all records in an unique query transaction, process will be iterative, limiting in each iteration the record's count to insert. This way the use of the resources is balanced and more efficient. Also was improved the updateLegacyCosts() method, increasing the value of i to avoid flush and session clear in every iteration when legacy costs are being processed. details: https://code.openbravo.com/erp/devel/pi/rev/b622f00e5e44 changeset: 32206:b622f00e5e44 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Thu May 25 14:13:21 2017 +0200 summary: Related to issue 35959: Code review improvements diffstat: src/org/openbravo/costing/CostingMigrationProcess.java | 69 +++++------------ src/org/openbravo/costing/CostingUtils_data.xsql | 64 ++++++++++++++++- 2 files changed, 85 insertions(+), 48 deletions(-) diffs (215 lines): diff -r 2ea6fb210371 -r b622f00e5e44 src/org/openbravo/costing/CostingMigrationProcess.java --- a/src/org/openbravo/costing/CostingMigrationProcess.java Thu May 25 14:52:15 2017 +0200 +++ b/src/org/openbravo/costing/CostingMigrationProcess.java Thu May 25 14:13:21 2017 +0200 @@ -11,7 +11,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2012-2016 Openbravo SLU + * All portions are Copyright (C) 2012-2017 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* @@ -47,6 +47,7 @@ import org.openbravo.dal.service.OBCriteria; import org.openbravo.dal.service.OBDal; import org.openbravo.dal.service.OBQuery; +import org.openbravo.database.ConnectionProvider; import org.openbravo.erpCommon.ad_forms.ProductInfo; import org.openbravo.erpCommon.utility.OBDateUtils; import org.openbravo.erpCommon.utility.OBError; @@ -75,8 +76,6 @@ import org.openbravo.model.materialmgmt.transaction.InventoryCount; import org.openbravo.model.materialmgmt.transaction.InventoryCountLine; import org.openbravo.model.materialmgmt.transaction.MaterialTransaction; -import org.openbravo.model.materialmgmt.transaction.ShipmentInOut; -import org.openbravo.model.materialmgmt.transaction.ShipmentInOutLine; import org.openbravo.scheduling.Process; import org.openbravo.scheduling.ProcessBundle; import org.openbravo.scheduling.ProcessLogger; @@ -85,6 +84,7 @@ public class CostingMigrationProcess implements Process { private ProcessLogger logger; + private ConnectionProvider conn; private static final Logger log4j = Logger.getLogger(CostingMigrationProcess.class); private static CostingAlgorithm averageAlgorithm = null; private static final String alertRuleName = "Products with transactions without available cost on date."; @@ -97,11 +97,13 @@ private static final String valuedLegacy = "800088"; private static final String dimensionalLegacy = "800205"; private static final String processEntity = org.openbravo.model.ad.ui.Process.ENTITY_NAME; + private static final int maxTrx = 10000; @Override public void execute(ProcessBundle bundle) throws Exception { logger = bundle.getLogger(); OBError msg = new OBError(); + conn = bundle.getConnection(); msg.setType("Success"); msg.setTitle(OBMessageUtils.messageBD("Success")); try { @@ -389,10 +391,9 @@ try { while (legacyCosts.next()) { Costing cost = (Costing) legacyCosts.get(0); - updateTrxLegacyCosts(cost, stdPrecission, naturalTree); - if ((i % 100) == 0) { + if ((++i % 100) == 0) { OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } @@ -465,6 +466,7 @@ delQry.executeUpdate(); List<Client> clients = getClients(); + for (Client client : clients) { client = OBDal.getInstance().get(Client.class, client.getId()); OrganizationStructureProvider osp = OBContext.getOBContext() @@ -843,48 +845,21 @@ private void insertTrxCosts() { TriggerHandler.getInstance().disable(); try { - StringBuffer insert = new StringBuffer(); - insert.append(" insert into " + TransactionCost.ENTITY_NAME); - insert.append(" (" + TransactionCost.PROPERTY_ID); - insert.append(", " + TransactionCost.PROPERTY_ACTIVE); - insert.append(", " + TransactionCost.PROPERTY_CLIENT); - insert.append(", " + TransactionCost.PROPERTY_ORGANIZATION); - insert.append(", " + TransactionCost.PROPERTY_CREATIONDATE); - insert.append(", " + TransactionCost.PROPERTY_CREATEDBY); - insert.append(", " + TransactionCost.PROPERTY_UPDATED); - insert.append(", " + TransactionCost.PROPERTY_UPDATEDBY); - insert.append(", " + TransactionCost.PROPERTY_INVENTORYTRANSACTION); - insert.append(", " + TransactionCost.PROPERTY_COST); - insert.append(", " + TransactionCost.PROPERTY_COSTDATE); - insert.append(", " + TransactionCost.PROPERTY_CURRENCY); - insert.append(", " + TransactionCost.PROPERTY_ACCOUNTINGDATE); - insert.append(")"); - insert.append(" select get_uuid()"); - insert.append(", t." + MaterialTransaction.PROPERTY_ACTIVE); - insert.append(", t." + MaterialTransaction.PROPERTY_CLIENT); - insert.append(", t." + MaterialTransaction.PROPERTY_ORGANIZATION); - insert.append(", now()"); - insert.append(", t." + MaterialTransaction.PROPERTY_CREATEDBY); - insert.append(", now()"); - insert.append(", t." + MaterialTransaction.PROPERTY_UPDATEDBY); - insert.append(", t"); - insert.append(", t." + MaterialTransaction.PROPERTY_TRANSACTIONCOST); - insert.append(", t." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE); - insert.append(", t." + MaterialTransaction.PROPERTY_CURRENCY); - insert.append(", coalesce(io." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", t." - + MaterialTransaction.PROPERTY_MOVEMENTDATE + ")"); - insert.append(" from " + MaterialTransaction.ENTITY_NAME + " as t"); - insert.append(" left join t." + MaterialTransaction.PROPERTY_TRANSACTIONCOSTLIST + " as tc"); - insert.append(" left join t." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as iol"); - insert.append(" left join iol." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as io"); - insert.append(" where t." + MaterialTransaction.PROPERTY_TRANSACTIONCOST + " is not null"); - insert.append(" and tc." + TransactionCost.PROPERTY_ID + " is null"); - - Query queryInsert = OBDal.getInstance().getSession().createQuery(insert.toString()); - queryInsert.executeUpdate(); - - OBDal.getInstance().flush(); - OBDal.getInstance().getSession().clear(); + long countTrx = Long.valueOf(CostingUtilsData.countTrxCosts(conn)).longValue(); + long iters = (countTrx % maxTrx == 0) ? (countTrx / maxTrx) : (countTrx / maxTrx) + 1; + String pgLimit = null, oraLimit = null; + if (StringUtils.equalsIgnoreCase(conn.getRDBMS(), "ORACLE")) { + oraLimit = String.valueOf(maxTrx); + } else { + pgLimit = String.valueOf(maxTrx); + } + for (int i = 0; i < iters; i++) { + CostingUtilsData.insertTrxCosts(conn, pgLimit, oraLimit); + OBDal.getInstance().flush(); + OBDal.getInstance().getSession().clear(); + } + } catch (Exception e) { + log4j.error(e.getMessage()); } finally { TriggerHandler.getInstance().enable(); } diff -r 2ea6fb210371 -r b622f00e5e44 src/org/openbravo/costing/CostingUtils_data.xsql --- a/src/org/openbravo/costing/CostingUtils_data.xsql Thu May 25 14:52:15 2017 +0200 +++ b/src/org/openbravo/costing/CostingUtils_data.xsql Thu May 25 14:13:21 2017 +0200 @@ -12,7 +12,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2014-2015 Openbravo SLU + * All portions are Copyright (C) 2014-2017 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* @@ -100,4 +100,66 @@ <Parameter name="AD_Client_ID"/> <Parameter name="DocumentType"/> </SqlMethod> + <SqlMethod name="insertTrxCosts" type="preparedStatement" return="rowCount"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + insert into M_Transaction_Cost ( + M_Transaction_Cost_ID, + Isactive, + AD_Client_ID, + AD_Org_ID, + Created, + Createdby, + Updated, + Updatedby, + M_Transaction_ID, + Cost, + CostDate, + C_Currency_ID, + DateAcct) + (select + get_uuid(), + t.IsActive, + t.AD_Client_ID, + t.AD_Org_ID, + now(), + t.CreatedBy, + now(), + t.UpdatedBy, + t.M_Transaction_ID, + t.TransactionCost, + t.TrxProcessDate, + t.C_Currency_ID, + coalesce(io.DateAcct, t.MovementDate) + from + M_Transaction t + left join M_Transaction_Cost tc on t.M_Transaction_ID=tc.M_Transaction_ID + left join M_InOutLine iol on t.M_InOutLine_ID=iol.M_InOutLine_ID + left join M_InOut io on iol.M_InOut_ID=io.M_InOut_ID + where + (t.TransactionCost is not null) + and (tc.M_Transaction_Cost_ID is null) + ) + ]]> + </Sql> + <Parameter name="pgLimit" type="argument" optional="true" after="and (tc.M_Transaction_Cost_ID is null)"><![CDATA[LIMIT ]]></Parameter> + <Parameter name="oraLimit" type="argument" optional="true" after="and (tc.M_Transaction_Cost_ID is null)"><![CDATA[AND ROWNUM <= ]]></Parameter> + </SqlMethod> + <SqlMethod name="countTrxCosts" type="preparedStatement" return="String"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + select count(1) + from + M_Transaction t + left join M_Transaction_Cost tc on t.M_Transaction_ID=tc.M_Transaction_ID + left join M_InOutLine iol on t.M_InOutLine_ID=iol.M_InOutLine_ID + left join M_InOut io on iol.M_InOut_ID=io.M_InOut_ID + where + (t.TransactionCost is not null) + and (tc.M_Transaction_Cost_ID is null) + ]]> + </Sql> + </SqlMethod> </SqlClass> \ No newline at end of file ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
