[OpenbravoERP-commits] devel/pi: 2 new changesets

hg Wed, 07 Sep 2016 01:24:26 -0700

details:   https://code.openbravo.com/erp/devel/pi/rev/076cf96db902
changeset: 29998:076cf96db902
user:      Asier Lostalé <asier.lostale <at> openbravo.com>
date:      Fri Aug 05 10:03:10 2016 +0200
summary:   fixed bug 33629: slow order/invoice grid population in Add Payment 
Description


  In evironments with big volumes it was slow because it performed a Seq Scan
  on FIN_Payment_ScheduleDetail table.

  It has been fixed for the cases when BP is preset by:
   - Removing left joins to get BP from order/invoice to retrieve it directly
     from FIN_Payment_ScheduleDetail
   - Adding an index on FIN_Payment_ScheduleDetail.BP so that previous query
     does not require of a Seq Scan

details:   https://code.openbravo.com/erp/devel/pi/rev/077bc1244bc0
changeset: 29999:077bc1244bc0
user:      Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
date:      Tue Sep 06 12:04:58 2016 +0200
summary:   Related to issue 33629: Code review improvements

Group by bp.id and bp.name instead of by bp to avoid errors in Oracle.

diffstat:

 modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml  
                                             |   2 +-
 modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml   
                                             |   3 +-
 
modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
 |  38 ++-------
 src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml                    
                                             |   3 +
 4 files changed, 15 insertions(+), 31 deletions(-)

diffs (144 lines):

diff -r e46e66b4f219 -r 077bc1244bc0 
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
--- 
a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml 
    Tue Sep 06 12:25:45 2016 +0200
+++ 
b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml 
    Tue Sep 06 12:04:58 2016 +0200
@@ -1022,7 +1022,7 @@
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE>
-<!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<ENTITY_ALIAS><![CDATA[COALESCE(invbp.name, ordbp.name)]]></ENTITY_ALIAS>
+<!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<ENTITY_ALIAS><![CDATA[bp.name]]></ENTITY_ALIAS>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<ALLOWSORTING><![CDATA[Y]]></ALLOWSORTING>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<ALLOWFILTERING><![CDATA[Y]]></ALLOWFILTERING>
 <!--2295DC7A28FE4F9DA842F453E2FF2F41-->  
<ALLOWED_CROSS_ORG_LINK><![CDATA[N]]></ALLOWED_CROSS_ORG_LINK>
diff -r e46e66b4f219 -r 077bc1244bc0 
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
--- 
a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml  
    Tue Sep 06 12:25:45 2016 +0200
+++ 
b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml  
    Tue Sep 06 12:04:58 2016 +0200
@@ -69,14 +69,13 @@
 <!--58AF4D3E594B421A9A7307480736F03E-->  <ISTREE><![CDATA[N]]></ISTREE>
 <!--58AF4D3E594B421A9A7307480736F03E-->  <HQLQUERY><![CDATA[select 
@selectClause@
 from FIN_Payment_ScheduleDetail as psd
+join psd.businessPartner as bp
 left outer join psd.orderPaymentSchedule as ops  
 left outer join ops.order as ord @joinClauseOrder@
-left outer join ord.businessPartner as ordbp
 left outer join ops.fINPaymentPriority as opriority 
 left outer join ops.finPaymentmethod opsfp
 left outer join psd.invoicePaymentSchedule as ips  
 left outer join ips.invoice as inv @joinClauseInvoice@
-left outer join inv.businessPartner as invbp
 left outer join ips.fINPaymentPriority as ipriority 
 left outer join ips.finPaymentmethod as ipsfp
 left outer join psd.organization as org 
diff -r e46e66b4f219 -r 077bc1244bc0 
modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
--- 
a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
       Tue Sep 06 12:25:45 2016 +0200
+++ 
b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
       Tue Sep 06 12:04:58 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) 2014 - 2016 Openbravo SLU
+ * All portions are Copyright (C) 2014-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -129,8 +129,6 @@
       selectClause
           .append(" COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id) 
as paymentMethod, ");
       selectClause.append(" COALESCE(ipsfp.name, opsfp.name) as 
paymentMethodName, ");
-      selectClause.append(" COALESCE(invbp.id, ordbp.id) as businessPartner, 
");
-      selectClause.append(" COALESCE(invbp.name, ordbp.name) as 
businessPartnerName, ");
       selectClause.append(" COALESCE(ips.expectedDate, ops.expectedDate) as 
expectedDate, ");
       selectClause.append(" max(COALESCE(ips.amount, ops.amount)) as 
expectedAmount, ");
       selectClause.append(" max(COALESCE(inv.grandTotalAmount, 0)) as 
invoicedAmount, ");
@@ -145,8 +143,6 @@
       selectClause
           .append(" COALESCE(ops.finPaymentmethod.id, ips.finPaymentmethod.id) 
as paymentMethod, ");
       selectClause.append(" COALESCE(opsfp.name, ipsfp.name) as 
paymentMethodName, ");
-      selectClause.append(" COALESCE(invbp.id, ordbp.id) as businessPartner, 
");
-      selectClause.append(" COALESCE(invbp.name, ordbp.name) as 
businessPartnerName, ");
       selectClause.append(" COALESCE(ops.expectedDate, ips.expectedDate) as 
expectedDate, ");
       selectClause.append(" max(COALESCE(ips.amount, ops.amount)) as 
expectedAmount, ");
       selectClause.append(" sum(COALESCE(inv.grandTotalAmount, 0)) as 
invoicedAmount, ");
@@ -160,12 +156,12 @@
       selectClause
           .append(" COALESCE(ips.finPaymentmethod.id, ops.finPaymentmethod.id) 
as paymentMethod, ");
       selectClause.append(" COALESCE(ipsfp.name, opsfp.name) as 
paymentMethodName, ");
-      selectClause.append(" COALESCE(invbp.id, ordbp.id) as businessPartner, 
");
-      selectClause.append(" COALESCE(invbp.name, ordbp.name) as 
businessPartnerName, ");
       selectClause.append(" COALESCE(ips.expectedDate, ops.expectedDate) as 
expectedDate, ");
       selectClause.append(" max(COALESCE(ips.amount, ops.amount)) as 
expectedAmount, ");
       selectClause.append(" max(COALESCE(inv.grandTotalAmount, 0)) as 
invoicedAmount, ");
     }
+    selectClause.append(" bp.id as businessPartner, ");
+    selectClause.append(" bp.name as businessPartnerName, ");
     selectClause.append(" SUM(psd.amount + psd.writeoffAmount) as 
outstandingAmount, ");
     selectClause.append(" COALESCE(sum(pd.amount), 0) as amount, ");
     selectClause
@@ -244,38 +240,24 @@
       }
       whereClause.append(") or ");
     }
+
+    whereClause.append(" ( ");
     if ("I".equals(transactionType)) {
-
-      whereClause.append(" ( ");
       whereClause.append(" inv.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && 
!"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and invbp.id = :businessPartnerId ");
-      }
       whereClause.append(" and inv.currency.id = :currencyId ) ");
-
     } else if ("O".equals(transactionType)) {
-      whereClause.append(" ( ");
       whereClause.append(" ord.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && 
!"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and ordbp.id = :businessPartnerId ");
-      }
       whereClause.append(" and ord.currency.id = :currencyId ) ");
-
     } else {
-
-      whereClause.append(" ( ");
       whereClause.append(" inv.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && 
!"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and invbp.id = :businessPartnerId ");
-      }
       whereClause.append(" and inv.currency.id = :currencyId ) ");
       whereClause.append(" or ( ");
       whereClause.append(" ord.salesTransaction = :isSalesTransaction");
-      if (strBusinessPartnerId != null && 
!"null".equals(strBusinessPartnerId)) {
-        whereClause.append(" and ordbp.id = :businessPartnerId");
-      }
       whereClause.append(" and ord.currency.id = :currencyId ) ");
+    }
 
+    if (strBusinessPartnerId != null && !"null".equals(strBusinessPartnerId)) {
+      whereClause.append(" and bp.id = :businessPartnerId ");
     }
 
     whereClause.append(")");
@@ -322,8 +304,8 @@
       groupByClause.append(" oinfo.aPRMPaymentDescription, ");
       groupByClause.append(" inv.orderReference, ");
     }
-    groupByClause.append(" COALESCE(invbp.id, ordbp.id), ");
-    groupByClause.append(" COALESCE(invbp.name, ordbp.name) ");
+    groupByClause.append(" bp.id, ");
+    groupByClause.append(" bp.name ");
     return groupByClause;
   }
 
diff -r e46e66b4f219 -r 077bc1244bc0 
src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml
--- a/src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml       Tue Sep 
06 12:25:45 2016 +0200
+++ b/src-db/database/model/tables/FIN_PAYMENT_SCHEDULEDETAIL.xml       Tue Sep 
06 12:04:58 2016 +0200
@@ -143,6 +143,9 @@
       <foreign-key foreignTable="USER2" name="FIN_SCHEDULEDETAIL_USER2">
         <reference local="USER2_ID" foreign="USER2_ID"/>
       </foreign-key>
+      <index name="FIN_PAY_SCHEDULEDETAIL_BP" unique="false">
+        <index-column name="C_BPARTNER_ID"/>
+      </index>
       <index name="FIN_PAY_SCHEDULEDETAIL_IN" unique="false">
         <index-column name="FIN_PAYMENT_SCHEDULE_INVOICE"/>
       </index>

------------------------------------------------------------------------------
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits

[OpenbravoERP-commits] devel/pi: 2 new changesets

Reply via email to