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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0033705
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformminorhave not tried2016-08-12 17:242016-08-25 22:53
ReportershuehnerView Statuspublic 
Assigned Toshuehner 
PrioritynormalResolutionfixedFixed in Version3.0PR16Q4
StatusclosedFix in branchFixed in SCM revision805737524388
ProjectionnoneETAnoneTarget Version
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

0033705: 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-
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0033791 acknowledgedTriage Finance PricelistVersionFilterExpression & PriceListVersionFilterExpressionName are 99% identical 
depends on backport 00338093.0PR16Q3.1 closedaferraz Double query in PricelistVersionFilterExpression (product selector) 
depends on backport 00338103.0PR16Q2.3 closedaferraz Double query in PricelistVersionFilterExpression (product selector) 
related to feature request 0033767 closedplatform Add code to auto-detect 'accidental double query' on same OBQuery or OBCriteria object 
related to defect 0033790 closedshuehner Accidental double query in PriceListVersionFilterExpressionName (order lines, product selector dropdown) 
related to defect 0033797 closedTriage Omni OMS Product selector in sales order lines, don't take the last price list version 
related to design defect 0036898 newTriage Finance Performance issues when using DAL 

-  Notes
(0089303)
hgbot (developer)
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 (developer)
2016-08-25 10:42

 Code reviewed
(0089401)
hgbot (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2016-08-12 17:24 shuehner New Issue
2016-08-12 17:24 shuehner Assigned To => shuehner
2016-08-12 17:24 shuehner Modules => Core
2016-08-12 17:24 shuehner Triggers an Emergency Pack => No
2016-08-12 17:24 shuehner Tag Attached: Performance
2016-08-23 14:33 hgbot Checkin
2016-08-23 14:33 hgbot Note Added: 0089303
2016-08-23 14:33 hgbot Status new => resolved
2016-08-23 14:33 hgbot Resolution open => fixed
2016-08-23 14:33 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/8057375243880a2830c0537f24d037f675879b66 [^]
2016-08-23 14:34 shuehner Review Assigned To => caristu
2016-08-23 14:44 shuehner Relationship added related to 0033767
2016-08-24 11:41 shuehner Relationship added related to 0033790
2016-08-24 11:47 shuehner Relationship added related to 0033791
2016-08-25 10:42 caristu Note Added: 0089382
2016-08-25 10:42 caristu Status resolved => closed
2016-08-25 10:42 caristu Fixed in Version => 3.0PR16Q4
2016-08-25 13:13 caristu Relationship added related to 0033797
2016-08-25 14:17 aferraz Status closed => new
2016-08-25 14:17 aferraz Resolution fixed => open
2016-08-25 14:19 aferraz Fixed in Version 3.0PR16Q4 =>
2016-08-25 14:25 aferraz Status new => scheduled
2016-08-25 14:26 aferraz Status scheduled => resolved
2016-08-25 14:26 aferraz Fixed in Version => 3.0PR16Q4
2016-08-25 14:26 aferraz Resolution open => fixed
2016-08-25 14:26 aferraz Status resolved => closed
2016-08-25 15:11 hgbot Checkin
2016-08-25 15:11 hgbot Note Added: 0089401
2016-08-25 22:53 hudsonbot Checkin
2016-08-25 22:53 hudsonbot Note Added: 0089417
2017-09-19 18:49 markmm82 Relationship added related to 0036898

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker