# HG changeset patch # User Víctor Martínez Romanos # Date 1488973821 -3600 # Wed Mar 08 12:50:21 2017 +0100 # Node ID 0b13c0775882cba122a1a0f274e081c0b57cd320 # Parent 2d002737ee99169df4fb03ea8f76aee2a2d7869b Fixed bug 33581: slow defaults retrieval in add payment The FIN_Payment table didn't have an index on the c_bpartner_id column, thus forcing a sequencial scan in any query run over this table. Added an index on the c_bpartner_id column to fix that problem. This index should fix most of the cases, however when for example a concrete business partner has an important percentage of the records in that table, the previous index would be useless, and the db might decide to run a seq. scan. To control this scenario we have extended the previous index with a function to distinguish credit payments (generated credit != used credit). This way we could filter by the credit records for the business partner directly using the index. Note that the function obequals is declared in the prescript because it must be defined as IMMUTABLE/DETERMINISTIC and this syntaxis is not supported yet by the DBSM. The AdvPaymentMngtDao.getCustomerPaymentsWithCredit() has been rewritten to take advantage of the new index. diff --git a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java --- a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java +++ b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java @@ -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) 2010-2016 Openbravo SLU + * All portions are Copyright (C) 2010-2017 Openbravo SLU * All Rights Reserved. * Contributor(s): Enterprise Intelligence Systems (http://www.eintel.com.au). ************************************************************************* @@ -23,11 +23,15 @@ import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; +import java.util.HashMap; import java.util.Iterator; import java.util.List; +import java.util.Map; import java.util.Set; import org.apache.commons.lang.StringUtils; +import org.hibernate.Query; +import org.hibernate.Session; import org.hibernate.criterion.Criterion; import org.hibernate.criterion.DetachedCriteria; import org.hibernate.criterion.Projections; @@ -1970,24 +1974,41 @@ try { OBContext.setAdminMode(true); - OBCriteria obcPayment = OBDal.getInstance().createCriteria(FIN_Payment.class); - obcPayment.add(Restrictions.eq(FIN_Payment.PROPERTY_BUSINESSPARTNER, bp)); - obcPayment.add(Restrictions.eq(FIN_Payment.PROPERTY_RECEIPT, isReceipt)); - obcPayment.add(Restrictions.ne(FIN_Payment.PROPERTY_GENERATEDCREDIT, BigDecimal.ZERO)); - obcPayment.add(Restrictions.neProperty(FIN_Payment.PROPERTY_GENERATEDCREDIT, - FIN_Payment.PROPERTY_USEDCREDIT)); + final Organization legalEntity = FIN_Utility.getLegalEntityOrg(org); Set orgIds = OBContext.getOBContext().getOrganizationStructureProvider() .getChildTree(legalEntity.getId(), true); - obcPayment.add(Restrictions.in("organization.id", orgIds)); + + final Map params = new HashMap(); + + final StringBuffer hql = new StringBuffer("select p "); + hql.append(" from " + FIN_Payment.ENTITY_NAME + " as p "); + hql.append(" where p." + FIN_Payment.PROPERTY_BUSINESSPARTNER + ".id = :bpartnerId "); + hql.append(" and p." + FIN_Payment.PROPERTY_RECEIPT + " = :isReceipt "); + hql.append(" and p." + FIN_Payment.PROPERTY_ORGANIZATION + ".id in (:orgIds) "); + hql.append(" and obequals(p." + FIN_Payment.PROPERTY_GENERATEDCREDIT + ", p." + + FIN_Payment.PROPERTY_USEDCREDIT + ") = 'N' "); + hql.append(" and p." + FIN_Payment.PROPERTY_GENERATEDCREDIT + " <> 0 "); if (currency != null) { - obcPayment.add(Restrictions.eq(FIN_Payment.PROPERTY_CURRENCY, currency)); + hql.append(" and p." + FIN_Payment.PROPERTY_CURRENCY + ".id = :currencyId"); + params.put("currencyId", currency.getId()); } - obcPayment.addOrderBy(FIN_Payment.PROPERTY_PAYMENTDATE, true); - obcPayment.addOrderBy(FIN_Payment.PROPERTY_DOCUMENTNO, true); - List paymentList = new ArrayList(); - for (FIN_Payment fp : obcPayment.list()) { + hql.append(" order by p." + FIN_Payment.PROPERTY_PAYMENTDATE + " asc, "); + hql.append(" p." + FIN_Payment.PROPERTY_DOCUMENTNO + " asc "); + + params.put("bpartnerId", bp.getId()); + params.put("isReceipt", isReceipt); + params.put("orgIds", orgIds); + + final Session session = OBDal.getInstance().getSession(); + final Query query = session.createQuery(hql.toString()); + query.setProperties(params); + + @SuppressWarnings("unchecked") + final List queryList = query.list(); + final List paymentList = new ArrayList(); + for (FIN_Payment fp : queryList) { if ((FIN_Utility.seqnumberpaymentstatus(fp.getStatus())) >= (FIN_Utility .seqnumberpaymentstatus(FIN_Utility.invoicePaymentStatus(fp)))) { paymentList.add(fp); diff --git a/src-db/database/model/excludeFilter.xml b/src-db/database/model/excludeFilter.xml --- a/src-db/database/model/excludeFilter.xml +++ b/src-db/database/model/excludeFilter.xml @@ -86,4 +86,5 @@ + diff --git a/src-db/database/model/prescript-Oracle.sql b/src-db/database/model/prescript-Oracle.sql --- a/src-db/database/model/prescript-Oracle.sql +++ b/src-db/database/model/prescript-Oracle.sql @@ -234,3 +234,38 @@ RETURN p_date + p_hours/24 + p_minutes/1440 + p_seconds/86400; end ADD_HMS; /-- END + +create or replace +FUNCTION OBEQUALS(p_number_a IN NUMBER, p_number_b IN NUMBER) RETURN CHAR DETERMINISTIC + +AS +/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2017 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ +/** +* Returns 'Y' when both numbers are equals, else returns 'N' +* This function is used as index in FIN_Payment table +**/ + v_dif NUMBER; +begin + v_dif := coalesce(p_number_a, 0) - coalesce(p_number_b, 0); + IF (v_dif = 0) THEN + return 'Y'; + ELSE + return 'N'; + END IF; +end OBEQUALS; +/-- END diff --git a/src-db/database/model/prescript-PostgreSql.sql b/src-db/database/model/prescript-PostgreSql.sql --- a/src-db/database/model/prescript-PostgreSql.sql +++ b/src-db/database/model/prescript-PostgreSql.sql @@ -1536,3 +1536,40 @@ -- INSTALL PG_TRGM EXTENSION CREATE EXTENSION IF NOT EXISTS "pg_trgm" /-- END + +CREATE OR REPLACE FUNCTION obequals( + p_number_a numeric, + p_number_b numeric) + RETURNS char AS +$BODY$ DECLARE +/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2017 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ +/** +* Returns 'Y' when both numbers are equals, else returns 'N' +* This function is used as index in FIN_Payment table +**/ + v_dif NUMERIC; +BEGIN + v_dif := coalesce(p_number_a, 0) - coalesce(p_number_b, 0); + IF (v_dif = 0) THEN + return 'Y'; + ELSE + return 'N'; + END IF; +END; $BODY$ + LANGUAGE plpgsql IMMUTABLE +/-- END diff --git a/src-db/database/model/tables/FIN_PAYMENT.xml b/src-db/database/model/tables/FIN_PAYMENT.xml --- a/src-db/database/model/tables/FIN_PAYMENT.xml +++ b/src-db/database/model/tables/FIN_PAYMENT.xml @@ -187,6 +187,10 @@ + + + +