details: https://code.openbravo.com/erp/devel/pi/rev/e8edf135fc2a changeset: 33703:e8edf135fc2a user: Mark <markmm82 <at> gmail.com> date: Mon Mar 12 11:21:51 2018 -0400 summary: Fixes issue 38045: Improved performance when executing the payment report
The query to retrieve the invoices using credit in getInvoicesUsingCredit method was restructured to improve the performance, avoiding the join with the FIN_Payment_Detail_V view and extracting the logic of it but without do some joins with not needed tables on this scenario. Also the new query is using the defined indexes to execute the query faster and decrease the cost of the execution. details: https://code.openbravo.com/erp/devel/pi/rev/04567df3957a changeset: 33704:04567df3957a user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Fri Mar 16 15:02:20 2018 +0100 summary: Related to issue 38045: Code review improvements Improve more the query. Use setParameter method. diffstat: modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java | 24 ++++++--- 1 files changed, 15 insertions(+), 9 deletions(-) diffs (47 lines): diff -r 7534b01e42e3 -r 04567df3957a modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java --- a/modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java Fri Mar 16 16:22:58 2018 +0000 +++ b/modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java Fri Mar 16 15:02:20 2018 +0100 @@ -11,7 +11,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SL - * All portions are Copyright (C) 2009-2017 Openbravo SL + * All portions are Copyright (C) 2009-2018 Openbravo SL * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -2141,20 +2141,26 @@ public java.util.List<Invoice> getInvoicesUsingCredit(final FIN_Payment payment) { final StringBuilder sql = new StringBuilder(); - final java.util.List<Invoice> result = new ArrayList<Invoice>(); + final java.util.List<Invoice> result = new ArrayList<>(); - sql.append(" select distinct(pdv.invoicePaymentPlan.invoice.id) "); - sql.append(" from FIN_Payment_Credit pc, FIN_Payment p0, "); - sql.append(" FIN_Payment p1, FIN_Payment_Detail_V pdv "); - sql.append(" where p0.id=pc.creditPaymentUsed "); - sql.append(" and pc.payment=p1.id "); - sql.append(" and pdv.payment=p1.id "); - sql.append(" and p0.id = '" + payment.getId() + "' "); + sql.append(" select distinct(psiv.invoice.id) "); + sql.append(" from FIN_Payment_Sched_Inv_V psiv "); + sql.append(" where exists ("); + sql.append(" select 1 "); + sql.append(" from FIN_Payment_Credit pc"); + sql.append(" , FIN_Payment_Detail pd"); + sql.append(" , FIN_Payment_ScheduleDetail psd"); + sql.append(" where pc.payment.id = pd.finPayment.id"); + sql.append(" and pd.id = psd.paymentDetails.id"); + sql.append(" and pc.creditPaymentUsed.id = :paymentId"); + sql.append(" and psd.invoicePaymentSchedule.id = psiv.id"); + sql.append(" )"); try { OBContext.setAdminMode(true); final Session session = OBDal.getReadOnlyInstance().getSession(); final Query query = session.createQuery(sql.toString()); + query.setParameter("paymentId", payment.getId()); for (final Object o : query.list()) { result.add(OBDal.getReadOnlyInstance().get(Invoice.class, o)); } ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
