Fixes issue 32341: Performance problem in ResetAccounting
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Wed, 02 Mar 2016 10:18:21 +0100
changeset 28863 a770479abfb3
parent 28862 1450b7bf6b32
child 28864 40c86fcd61ae
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.
src-db/database/model/functions/AD_ORG_GETPERIODCONTROLALLOW.xml
src/org/openbravo/financial/ResetAccounting.java
--- 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	Wed Mar 02 10:18:21 2016 +0100
@@ -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,26 @@
 
   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
+  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>
--- a/src/org/openbravo/financial/ResetAccounting.java	Wed Apr 06 11:12:42 2016 +0200
+++ b/src/org/openbravo/financial/ResetAccounting.java	Wed Mar 02 10:18:21 2016 +0100
@@ -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) {