Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033810Openbravo ERPA. Platformpublic2016-08-12 17:242016-08-29 15:12
Reportershuehner 
Assigned Toaferraz 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR16Q2.3Fixed in Version3.0PR16Q2.3 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033810: 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-
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks defect 0033705 closed shuehner Double query in PricelistVersionFilterExpression (product selector) 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-08-25 14:25aferrazTypedefect => backport
2016-08-25 14:25aferrazTarget Version => 3.0PR16Q2.3
2016-08-29 13:09aferrazAssigned Toshuehner => aferraz
2016-08-29 14:20hgbotCheckin
2016-08-29 14:20hgbotNote Added: 0089497
2016-08-29 14:20hgbotStatusscheduled => resolved
2016-08-29 14:20hgbotResolutionopen => fixed
2016-08-29 14:20hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/8057375243880a2830c0537f24d037f675879b66 [^] => http://code.openbravo.com/erp/backports/3.0PR16Q2.3/rev/2cc93f8dd77a19d5de50d47f71ef46e941e49655 [^]
2016-08-29 15:12caristuNote Added: 0089505
2016-08-29 15:12caristuStatusresolved => closed
2016-08-29 15:12caristuFixed in Version => 3.0PR16Q2.3

Notes
(0089497)
hgbot   
2016-08-29 14:20   
Repository: erp/backports/3.0PR16Q2.3
Changeset: 2cc93f8dd77a19d5de50d47f71ef46e941e49655
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.0PR16Q2.3/rev/2cc93f8dd77a19d5de50d47f71ef46e941e49655 [^]

Fixed issue 33810. 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
---
(0089505)
caristu   
2016-08-29 15:12   
Code reviewed