details: https://code.openbravo.com/erp/devel/pi/rev/a770479abfb3 changeset: 28863:a770479abfb3 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Wed Mar 02 10:18:21 2016 +0100 summary: Fixes issue 32341: Performance problem in ResetAccounting
Modify ad_org_getperiodcontrolallow function to make it recursive and improve its query. Execute query only once in ResetAccounting.java and for every orgIds instead of for each orgId in orgIds. Query will be done in SQL instead of HQL to avoid errors when executing PostDocumentTest. details: https://code.openbravo.com/erp/devel/pi/rev/40c86fcd61ae changeset: 28864:40c86fcd61ae user: David Miguelez <david.miguelez <at> openbravo.com> date: Thu Apr 07 11:26:17 2016 +0200 summary: Related to Issue 32341: Prevents infinite loop if organization is null diffstat: src-db/database/model/functions/AD_ORG_GETPERIODCONTROLALLOW.xml | 50 +++------ src/org/openbravo/financial/ResetAccounting.java | 47 ++++++--- 2 files changed, 50 insertions(+), 47 deletions(-) diffs (154 lines): diff -r 1450b7bf6b32 -r 40c86fcd61ae src-db/database/model/functions/AD_ORG_GETPERIODCONTROLALLOW.xml --- a/src-db/database/model/functions/AD_ORG_GETPERIODCONTROLALLOW.xml Wed Apr 06 11:12:42 2016 +0200 +++ b/src-db/database/model/functions/AD_ORG_GETPERIODCONTROLALLOW.xml Thu Apr 07 11:26:17 2016 +0200 @@ -16,7 +16,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) 2009-2014 Openbravo SLU +* All portions are Copyright (C) 2009-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ @@ -28,43 +28,31 @@ v_isperiodcontrolallowed AD_Org.IsPeriodControlAllowed%TYPE; v_parent_id ad_treenode.parent_id%TYPE; - v_node_id ad_treenode.parent_id%TYPE; BEGIN - SELECT IsPeriodControlAllowed, AD_Org_ID - INTO v_isperiodcontrolallowed, v_parent_id + + IF (p_organization IS NULL) THEN + RETURN NULL; + END IF; + + SELECT IsPeriodControlAllowed + INTO v_isperiodcontrolallowed FROM AD_Org WHERE AD_Org_ID=p_organization; IF (v_isperiodcontrolallowed='Y') THEN - RETURN v_parent_id; + RETURN p_organization; + ELSIF (p_organization='0') THEN + RETURN NULL; ELSE - v_node_id:=p_organization; - WHILE ( v_parent_id <> '0' AND v_node_id <> '0' ) LOOP - SELECT parent_id - INTO v_parent_id - FROM ad_treenode t - WHERE node_id=v_node_id - AND EXISTS (SELECT 1 - FROM ad_tree, ad_org - WHERE ad_tree.ad_client_id = ad_org.ad_client_id - AND ad_tree.ad_client_id=t.ad_client_id - AND ad_tree.ad_table_id='155' - AND t.ad_tree_id=ad_tree.ad_tree_id - ); - - SELECT IsPeriodControlAllowed, AD_Org_ID - INTO v_isperiodcontrolallowed, v_parent_id - FROM AD_Org - WHERE AD_Org_ID=v_parent_id; - - IF (v_isperiodcontrolallowed='Y') THEN - RETURN v_parent_id; - END IF; - - v_node_id:=v_parent_id; - END LOOP; -END IF; + SELECT tn.parent_id + INTO v_parent_id + FROM ad_tree t JOIN ad_treenode tn + ON t.ad_tree_id = tn.ad_tree_id + WHERE tn.node_id=p_organization + AND t.ad_table_id='155'; + RETURN ad_org_getperiodcontrolallow(v_parent_id); + END IF; RETURN NULL; END AD_ORG_GETPERIODCONTROLALLOW ]]></body> diff -r 1450b7bf6b32 -r 40c86fcd61ae src/org/openbravo/financial/ResetAccounting.java --- a/src/org/openbravo/financial/ResetAccounting.java Wed Apr 06 11:12:42 2016 +0200 +++ b/src/org/openbravo/financial/ResetAccounting.java Thu Apr 07 11:26:17 2016 +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) 2013-2015 Openbravo SLU + * All portions are Copyright (C) 2013-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* @@ -23,13 +23,14 @@ import java.util.Date; import java.util.HashMap; import java.util.HashSet; -import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.log4j.Logger; import org.hibernate.Query; +import org.hibernate.ScrollMode; +import org.hibernate.ScrollableResults; import org.hibernate.criterion.Restrictions; import org.openbravo.base.exception.OBException; import org.openbravo.base.model.ModelProvider; @@ -84,23 +85,37 @@ // period control associated Map<String, String> organizationPeriodControl = new HashMap<String, String>(); String calendarId = getCalendarId(adOrgId); - Iterator<String> iterator = orgIds.iterator(); - while (iterator.hasNext()) { - String organization = iterator.next(); - String myQuery1 = "select p.id from Organization p where ad_org_getperiodcontrolallow(:organization)=p.id"; - Query query1 = OBDal.getInstance().getSession().createQuery(myQuery1); - query1.setString("organization", organization); - query1.setMaxResults(1); - if (query1.uniqueResult() != null) { - String orgperiodcontrol = query1.uniqueResult().toString(); - organizationPeriodControl.put(organization, orgperiodcontrol); - if (!organizationPeriod.keySet().contains(orgperiodcontrol)) { - periods = getPeriodsDates(getOpenPeriods(client, dbt, orgIds, calendarId, table, - recordId, strdatefrom, strdateto, orgperiodcontrol)); - organizationPeriod.put(orgperiodcontrol, periods); + + String myQuery1 = "select ad_org_id, ad_org_getperiodcontrolallow(ad_org_id) from ad_org where ad_org_id in (:orgIds)"; + Query query1 = OBDal.getInstance().getSession().createSQLQuery(myQuery1); + query1.setParameterList("orgIds", orgIds); + ScrollableResults scroll = query1.scroll(ScrollMode.FORWARD_ONLY); + int i = 0; + try { + while (scroll.next()) { + Object[] resultSet = scroll.get(); + String organization = (String) resultSet[0]; + String orgperiodcontrol = (String) resultSet[1]; + + if (orgperiodcontrol != null) { + organizationPeriodControl.put(organization, orgperiodcontrol); + if (!organizationPeriod.keySet().contains(orgperiodcontrol)) { + periods = getPeriodsDates(getOpenPeriods(client, dbt, orgIds, calendarId, table, + recordId, strdatefrom, strdateto, orgperiodcontrol)); + organizationPeriod.put(orgperiodcontrol, periods); + } + } + + i++; + if (i % 100 == 0) { + OBDal.getInstance().flush(); + OBDal.getInstance().getSession().clear(); } } + } finally { + scroll.close(); } + int docUpdated = 0; int docDeleted = 0; for (String organization : orgIds) { ------------------------------------------------------------------------------ _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
