Fixes issue 39171 improve reset accounting process performance
authorSandra Huguet <sandra.huguet@openbravo.com>
Tue, 04 Sep 2018 15:23:34 +0200
changeset 34770 6bac4a0d2846
parent 34769 bfd9f58405fa
child 34771 7671c9b22d7e
Fixes issue 39171 improve reset accounting process performance

Improve performance of getTables and getDocbasetypes queries
src-db/database/model/tables/FACT_ACCT.xml
src/org/openbravo/financial/ResetAccounting.java
--- a/src-db/database/model/tables/FACT_ACCT.xml	Wed Sep 12 15:02:46 2018 +0200
+++ b/src-db/database/model/tables/FACT_ACCT.xml	Tue Sep 04 15:23:34 2018 +0200
@@ -305,6 +305,9 @@
       <index name="FACT_ACCT_DATEBALANCED" unique="false">
         <index-column name="DATEBALANCED"/>
       </index>
+      <index name="FACT_ACCT_DOCBASETYPE" unique="false">
+        <index-column name="DOCBASETYPE"/>
+      </index>
       <index name="FACT_ACCT_GROUP" unique="false">
         <index-column name="FACT_ACCT_GROUP_ID"/>
       </index>
--- a/src/org/openbravo/financial/ResetAccounting.java	Wed Sep 12 15:02:46 2018 +0200
+++ b/src/org/openbravo/financial/ResetAccounting.java	Tue Sep 04 15:23:34 2018 +0200
@@ -418,7 +418,9 @@
         accountingTables.add(myTable.getId());
         return accountingTables;
       }
-      String myQuery = "select distinct e.table.id from FinancialMgmtAccountingFact e where e.table.id <> '145'";
+
+      String myQuery = "select distinct t.id from ADTable t where t.id  <> '145' "
+          + " and exists (select 1 from FinancialMgmtAccountingFact e where e.table.id=t.id) ";
       accountingTables = OBDal.getInstance().getSession().createQuery(myQuery).list();
       return accountingTables;
     } finally {
@@ -440,10 +442,14 @@
   }
 
   private static List<String> getDocbasetypes(String clientId, String tableId, String recordId) {
-    String myQuery = "select distinct e.documentCategory from FinancialMgmtAccountingFact e where e.client.id = :clientId and e.table.id = :tableId ";
+
+    String myQuery = "select distinct d.documentCategory from DocumentType d where d.client.id = :clientId and d.table.id = :tableId"
+        + " and exists (select 1 from FinancialMgmtAccountingFact e where e.documentCategory=d.documentCategory ";
+
     if (!"".equals(recordId)) {
       myQuery = myQuery + "and e.recordID=:recordId";
     }
+    myQuery = myQuery + ")";
     Query<String> query = OBDal.getInstance().getSession().createQuery(myQuery, String.class);
     query.setParameter("clientId", clientId);
     query.setParameter("tableId", tableId);