details: https://code.openbravo.com/erp/devel/pi/rev/cd9a5d049ffb changeset: 26611:cd9a5d049ffb user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Tue May 05 13:26:14 2015 +0200 summary: Fixes issue 29319: Performance problems validating a Costing Rule
Query in checkTransactionsWithMovDateInClosedPeriod method of CostingRuleProcessOnProcessHandler class, which has performance problems, has been changed. Now M_Transaction.movementdate are truncated and grouped in order to have only one record per day instead of one record per transaction, which enhance a lot the performance of the query. The query has been created in XSQL, because subquerys in FROM statement in HQL are not supported. details: https://code.openbravo.com/erp/devel/pi/rev/e8b37e1f8639 changeset: 26612:e8b37e1f8639 user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com> date: Mon May 11 19:21:58 2015 +0200 summary: Related to issue 29319: Performance Problems on the process of Validating a Costing Rule Some code refactoring after code review diffstat: src/org/openbravo/costing/CostingRuleProcessOnProcessHandler.java | 58 ++------- src/org/openbravo/costing/CostingUtils_data.xsql | 37 ++++++ 2 files changed, 54 insertions(+), 41 deletions(-) diffs (150 lines): diff -r bf874a971617 -r e8b37e1f8639 src/org/openbravo/costing/CostingRuleProcessOnProcessHandler.java --- a/src/org/openbravo/costing/CostingRuleProcessOnProcessHandler.java Mon May 11 19:51:33 2015 +0000 +++ b/src/org/openbravo/costing/CostingRuleProcessOnProcessHandler.java Mon May 11 19:21:58 2015 +0200 @@ -11,23 +11,22 @@ * 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 Openbravo SLU + * All portions are Copyright (C) 2014-2015 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* */ package org.openbravo.costing; -import java.text.ParseException; +import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import java.util.Set; import org.apache.log4j.Logger; import org.codehaus.jettison.json.JSONObject; -import org.hibernate.Query; -import org.hibernate.Session; import org.openbravo.base.secureApp.VariablesSecureApp; +import org.openbravo.base.session.OBPropertiesProvider; import org.openbravo.client.kernel.BaseActionHandler; import org.openbravo.client.kernel.RequestContext; import org.openbravo.dal.core.OBContext; @@ -38,8 +37,6 @@ import org.openbravo.erpCommon.utility.OBMessageUtils; import org.openbravo.erpCommon.utility.Utility; import org.openbravo.model.common.plm.Product; -import org.openbravo.model.financialmgmt.calendar.Period; -import org.openbravo.model.financialmgmt.calendar.PeriodControl; import org.openbravo.model.materialmgmt.cost.CostingRule; import org.openbravo.model.materialmgmt.transaction.MaterialTransaction; import org.openbravo.service.db.DalConnectionProvider; @@ -110,44 +107,22 @@ private Date checkTransactionsWithMovDateInClosedPeriod(Set<String> naturalOrgs, Set<String> childOrgs, CostingRule rule) { - StringBuilder hql = new StringBuilder(); - final Session session = OBDal.getInstance().getSession(); - hql.append(" select min(trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + ")"); - hql.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); - hql.append(" join trx." + MaterialTransaction.PROPERTY_PRODUCT + " as p"); - hql.append("\n where trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = false"); - hql.append(" and p." + Product.PROPERTY_PRODUCTTYPE + " = 'I'"); - hql.append(" and p." + Product.PROPERTY_STOCKED + " = true"); - hql.append(" and p." + Product.PROPERTY_ORGANIZATION + ".id in (:porgs)"); - hql.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " >= :startingDate"); - hql.append(" and trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:childOrgs)"); - hql.append(" and exists"); - hql.append(" (select 1 from " + PeriodControl.ENTITY_NAME + " pc"); - hql.append(" inner join pc." + PeriodControl.PROPERTY_PERIOD + " p"); - hql.append(" where " + PeriodControl.PROPERTY_PERIODSTATUS + " <>'O'"); - hql.append(" and p." + Period.PROPERTY_CLIENT + "= :client"); - hql.append(" and pc." + PeriodControl.PROPERTY_ORGANIZATION + "= :org"); - hql.append(" and to_date(trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE - + ") >= p.startingDate"); - hql.append(" and to_date(trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE - + ") < p.endingDate + 1)"); - - final Query query = session.createQuery(hql.toString()); - - query.setParameterList("porgs", naturalOrgs); - query.setParameter("startingDate", CostingUtils.getCostingRuleStartingDate(rule)); - query.setParameterList("childOrgs", childOrgs); - query.setParameter("client", rule.getClient()); - query.setParameter("org", rule.getOrganization()); - + CostingUtilsData[] data = null; Date movementDateInPeriodClosed = null; try { - movementDateInPeriodClosed = (Date) query.uniqueResult(); - if (movementDateInPeriodClosed != null) { - movementDateInPeriodClosed = OBDateUtils.getDate(OBDateUtils - .formatDate(movementDateInPeriodClosed)); + String strDateFormat = OBPropertiesProvider.getInstance().getOpenbravoProperties() + .getProperty("dateFormat.java"); + final SimpleDateFormat dateFormat = new SimpleDateFormat(strDateFormat); + String strDateFrom = dateFormat.format(CostingUtils.getCostingRuleStartingDate(rule)); + + data = CostingUtilsData.selectTransactionsInClosedPeriod(new DalConnectionProvider(false), + Utility.getInStrSet(naturalOrgs), strDateFrom, Utility.getInStrSet(childOrgs), rule + .getClient().getId(), rule.getOrganization().getId()); + + if (data != null && data.length > 0) { + movementDateInPeriodClosed = OBDateUtils.getDate(data[0].mindatemovement); } - } catch (ParseException e) { + } catch (Exception e) { log4j.error("Error executing process", e); } @@ -186,4 +161,5 @@ pQry.setNamedParameter("childOrgs", childOrgs); return pQry.count() > 0; } + } diff -r bf874a971617 -r e8b37e1f8639 src/org/openbravo/costing/CostingUtils_data.xsql --- a/src/org/openbravo/costing/CostingUtils_data.xsql Mon May 11 19:51:33 2015 +0000 +++ b/src/org/openbravo/costing/CostingUtils_data.xsql Mon May 11 19:21:58 2015 +0200 @@ -20,6 +20,43 @@ <SqlClass name="CostingUtilsData" package="org.openbravo.costing"> <SqlClassComment></SqlClassComment> + <SqlMethod name="selectTransactionsInClosedPeriod" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT min(T.DATEMOVEMENT) AS minDateMovement, '' as period + FROM ( + SELECT trunc(TRX.MOVEMENTDATE) AS DATEMOVEMENT + FROM M_TRANSACTION TRX + INNER JOIN M_PRODUCT P + ON TRX.M_PRODUCT_ID = P.M_PRODUCT_ID + WHERE TRX.ISCOSTCALCULATED = 'N' + AND P.PRODUCTTYPE = 'I' + AND P.ISSTOCKED = 'Y' + AND P.AD_ORG_ID IN ('1') + AND TRX.MOVEMENTDATE >= ? + AND TRX.AD_ORG_ID IN ('1') + GROUP BY trunc(TRX.MOVEMENTDATE) + ) T + WHERE EXISTS ( + SELECT 1 + FROM C_PERIODCONTROL PC + INNER JOIN C_PERIOD PE + ON PC.C_PERIOD_ID = PE.C_PERIOD_ID + WHERE PC.PERIODSTATUS <> 'O' + AND PE.AD_CLIENT_ID = ? + AND PC.AD_ORG_ID = ? + AND T.DATEMOVEMENT >= PE.STARTDATE + AND T.DATEMOVEMENT < PE.ENDDATE + 1 + ) + ]]> + </Sql> + <Parameter name="Porgs" optional="true" type="replace" after="AND P.AD_ORG_ID IN (" text="'1'"/> + <Parameter name="StartingDate"/> + <Parameter name="ChildOrgs" optional="true" type="replace" after="AND TRX.AD_ORG_ID IN (" text="'1'"/> + <Parameter name="Client"/> + <Parameter name="Org"/> + </SqlMethod> <SqlMethod name="periodClosed" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> ------------------------------------------------------------------------------ One dashboard for servers and applications across Physical-Virtual-Cloud Widest out-of-the-box monitoring support with 50+ applications Performance metrics, stats and reports that give you Actionable Insights Deep dive visibility with transaction tracing using APM Insight. http://ad.doubleclick.net/ddm/clk/290420510;117567292;y _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
