Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0033809
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] A. Platformminorhave not tried2016-08-12 17:242016-08-25 15:20
ReportershuehnerView Statuspublic 
Assigned Toaferraz 
PrioritynormalResolutionfixedFixed in Version3.0PR16Q3.1
StatusclosedFix in branchFixed in SCM revision5af04a4df445
ProjectionnoneETAnoneTarget Version3.0PR16Q3.1
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tocaristu
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0033809: Double query in PricelistVersionFilterExpression (product selector)

DescriptionThe 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-
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
blocks defect 0033705 closedshuehner 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


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker