0033809: Double query in PricelistVersionFilterExpression (product selector)

Description

The PriceListVersionFilterExpression has following code which does 2 db queries

    OBCriteria<PriceListVersion> plVersionCrit = OBDal.getInstance().createCriteria(
        PriceListVersion.class);
    plVersionCrit.add(Restrictions.eq(PriceListVersion.PROPERTY_PRICELIST, priceList));
    plVersionCrit.add(Restrictions.le(PriceListVersion.PROPERTY_VALIDFROMDATE, date));
    if (plVersionCrit.count() > 0) {
      plVersionCrit.addOrderBy(PriceListVersion.PROPERTY_VALIDFROMDATE, false);
      return plVersionCrit.list().get(0);
    }

Filter m_pricelist_version by m_pricelist_id and validfrom<='some value'

First query does a count of matching rows.
Then order by validfrom desc is added and a 2nd query is done to retrieve the 'newest' row.

The count query is not really an optimization as it forces same effort on db.
As there is a m_pricelist_id + name unique constraint those filter criteria combination is indexed anyway.

Additionally that query should get a "limit 1" constraint as it only uses 1 row of result anyway.

Without that and many pricelistversion matches the filter criteria lots of rows are loaded into memory without good reason.

Steps To Reproduce

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

blocks

defect

0033705

closed

shuehner

Double query in PricelistVersionFilterExpression (product selector)

Notes
(0089403) hgbot (developer) 2016-08-25 15:14	Repository: erp/backports/3.0PR16Q3.1 Changeset: 5af04a4df445d10aa9a3a4f12de35f44783ebb92 Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> Date: Thu Aug 25 15:14:16 2016 +0200 URL: http://code.openbravo.com/erp/backports/3.0PR16Q3.1/rev/5af04a4df445d10aa9a3a4f12de35f44783ebb92 [^] Fixed issue 33809. Avoid double query with .count() + .list(). Add limit 1 to query Query did .count() + then retrieve 1 record after adding extra order by. This change removes the count as it only adds extra overhead and directly does the real .list() query. As the code only uses single row of result add extra limit 1 to reduce number of result rows loaded into memory if many match the filter. Simply code to use .uniqueResult() instead of .list().get(0) --- M src/org/openbravo/erpCommon/info/PriceListVersionFilterExpression.java ---

(0089405) caristu (developer) 2016-08-25 15:20	Code reviewed

Issue History
Date Modified	Username	Field	Change
2016-08-25 14:25	aferraz	Type	defect => backport
2016-08-25 14:25	aferraz	Target Version	=> 3.0PR16Q3.1
2016-08-25 15:14	aferraz	Assigned To	shuehner => aferraz
2016-08-25 15:14	hgbot	Checkin
2016-08-25 15:14	hgbot	Note Added: 0089403
2016-08-25 15:14	hgbot	Status	scheduled => resolved
2016-08-25 15:14	hgbot	Resolution	open => fixed
2016-08-25 15:14	hgbot	Fixed in SCM revision	http://code.openbravo.com/erp/devel/pi/rev/8057375243880a2830c0537f24d037f675879b66 [^] => http://code.openbravo.com/erp/backports/3.0PR16Q3.1/rev/5af04a4df445d10aa9a3a4f12de35f44783ebb92 [^]
2016-08-25 15:20	caristu	Note Added: 0089405
2016-08-25 15:20	caristu	Status	resolved => closed
2016-08-25 15:20	caristu	Fixed in Version	=> 3.0PR16Q3.1