Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033705Openbravo ERPA. Platformpublic2016-08-12 17:242016-08-25 22:53
Reportershuehner 
Assigned Toshuehner 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR16Q4 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033705: 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
TagsPerformance
Relationships
related to defect 0033791 acknowledged Triage Finance PricelistVersionFilterExpression & PriceListVersionFilterExpressionName are 99% identical 
depends on backport 00338093.0PR16Q3.1 closed aferraz Double query in PricelistVersionFilterExpression (product selector) 
depends on backport 00338103.0PR16Q2.3 closed aferraz Double query in PricelistVersionFilterExpression (product selector) 
related to feature request 0033767 closed platform Add code to auto-detect 'accidental double query' on same OBQuery or OBCriteria object 
related to defect 0033790 closed shuehner Accidental double query in PriceListVersionFilterExpressionName (order lines, product selector dropdown) 
related to defect 0033797 closed Triage Omni OMS Product selector in sales order lines, don't take the last price list version 
related to design defect 0036898 new Triage Finance Performance issues when using DAL 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-08-12 17:24shuehnerNew Issue
2016-08-12 17:24shuehnerAssigned To => shuehner
2016-08-12 17:24shuehnerModules => Core
2016-08-12 17:24shuehnerTriggers an Emergency Pack => No
2016-08-12 17:24shuehnerTag Attached: Performance
2016-08-23 14:33hgbotCheckin
2016-08-23 14:33hgbotNote Added: 0089303
2016-08-23 14:33hgbotStatusnew => resolved
2016-08-23 14:33hgbotResolutionopen => fixed
2016-08-23 14:33hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/8057375243880a2830c0537f24d037f675879b66 [^]
2016-08-23 14:34shuehnerReview Assigned To => caristu
2016-08-23 14:44shuehnerRelationship addedrelated to 0033767
2016-08-24 11:41shuehnerRelationship addedrelated to 0033790
2016-08-24 11:47shuehnerRelationship addedrelated to 0033791
2016-08-25 10:42caristuNote Added: 0089382
2016-08-25 10:42caristuStatusresolved => closed
2016-08-25 10:42caristuFixed in Version => 3.0PR16Q4
2016-08-25 13:13caristuRelationship addedrelated to 0033797
2016-08-25 14:17aferrazStatusclosed => new
2016-08-25 14:17aferrazResolutionfixed => open
2016-08-25 14:19aferrazFixed in Version3.0PR16Q4 =>
2016-08-25 14:25aferrazStatusnew => scheduled
2016-08-25 14:26aferrazStatusscheduled => resolved
2016-08-25 14:26aferrazFixed in Version => 3.0PR16Q4
2016-08-25 14:26aferrazResolutionopen => fixed
2016-08-25 14:26aferrazStatusresolved => closed
2016-08-25 15:11hgbotCheckin
2016-08-25 15:11hgbotNote Added: 0089401
2016-08-25 22:53hudsonbotCheckin
2016-08-25 22:53hudsonbotNote Added: 0089417
2017-09-19 18:49markmm82Relationship addedrelated to 0036898

Notes
(0089303)
hgbot   
2016-08-23 14:33   
Repository: erp/devel/pi
Changeset: 8057375243880a2830c0537f24d037f675879b66
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Fri Aug 12 17:27:25 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/8057375243880a2830c0537f24d037f675879b66 [^]

Fixed 33705. 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
---
(0089382)
caristu   
2016-08-25 10:42   
Code reviewed
(0089401)
hgbot   
2016-08-25 15:11   
Repository: erp/devel/pi
Changeset: 6075199d9aed81538a20df544af98f57754c6e00
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Thu Aug 25 15:08:40 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/6075199d9aed81538a20df544af98f57754c6e00 [^]

Related to issue 33705: Update copyright

---
M src/org/openbravo/erpCommon/info/PriceListVersionFilterExpression.java
---
(0089417)
hudsonbot   
2016-08-25 22:53   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/5e50832c9b35 [^]
Maturity status: Test