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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0025182
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2013-11-19 13:302014-01-20 06:25
ReporterjonalegriaesarteView Statuspublic 
Assigned Toalostale 
PriorityurgentResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision46ec5ec2839d
ProjectionnoneETAnoneTarget Version3.0PR14Q2
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toshankarb
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0025182: Query in org drop down in the list field is having bad performance

DescriptionThis is the version of the query we can see coming from the application, when trying to see the organziation drop down list in the filter:

select * from (
select distinct organizati1_.AD_Org_ID as col_0_0_, organizati1_.Name as col_1_0_
from M_Product product0_
left outer join AD_Org organizati1_ on product0_.AD_Org_ID=organizati1_.AD_Org_ID
where (upper(nvl(to_char(organizati1_.Name), '')) like '%PVM%' escape '|')
and (product0_.AD_Org_ID in ('20061' , '20063' , '20062' , '20003' , '20002' , '20023' , '20022' , '20025' , '20001' , '20026' , '20064' , '20065' ,
'20066' , '20067' , '20068' , '20052' , '20051' , '20012' , '20011' , '0' , '20016' , '20015' , '20055' , '20056' , '20053' , '20054'))
and (product0_.AD_Client_ID in ('1969' , '0'))
and product0_.IsActive='Y'
order by organizati1_.Name, organizati1_.AD_Org_ID )
where rownum <= 100 ;

The cost of this query is very high
Steps To Reproduce- Product window with 2 millions of entries
- Open the drop down list to filter by organization
Proposed SolutionThe following SQL would improve that cost:

is proposing the following query because the cost is improving:

select * from (
select distinct organizati1_.AD_Org_ID as col_0_0_, organizati1_.Name as col_1_0_
from AD_Org organizati1_
where exists (select 1 from m_product product0_
where (product0_.AD_Org_ID in ('20061' , '20063' , '20062' , '20003' , '20002' , '20023' , '20022' , '20025' , '20001' , '20026' , '20064' ,
'20065' , '20066' , '20067' , '20068' , '20052' , '20051' , '20012' , '20011' , '0' , '20016' , '20015' , '20055' , '20056' , '20053' , '20054'))
and (product0_.AD_Client_ID in ('1969' , '0'))
and product0_.IsActive='Y'
and organizati1_.ad_org_id = product0_.ad_org_id)
and (upper(nvl(to_char(organizati1_.Name), '')) like '%PVM%' escape '|')
order by organizati1_.Name, organizati1_.AD_Org_ID )
where rownum <= 100;
TagsPerformance
Attached Filesdiff file icon issue-25182.diff [^] (22,119 bytes) 2013-12-17 09:44 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]
related to defect 00252483.0MP31 closedalostale Error after sorting by a computed column 
related to design defect 0025210 newAugustoMauch Adaptive filtering not working for FK dropdown filters 
related to design defect 0025211 closedAugustoMauch Filter by selected value in FK uses identifier instead of ID 
related to defect 00263173.0PR14Q2 closedalostale Product selector is not working when you try to filter by warehouse 
related to defect 0026505 closedguillermogil Reference AD_Language does not show any results on its drop-down while filtering 
related to design defect 0028483 newAugustoMauch Improve the performance of the query done to populate FK filter dropdowns in HQL based tables 
blocks defect 00253783.0PR14Q2 closedalostale drop down filter doesn't work in FK property columns 
causes defect 00280853.0PR15Q1 closedalostale On the organization window is not possible to use the organization type filter 
causes defect 0029385 closedalostale Distinct parameter is not working in JSON REST web services 

-  Notes
(0062739)
alostale (manager)
2013-12-17 09:45

Attached fix, postponed to mp32
(0062881)
alostale (manager)
2013-12-19 17:30

fixed will require to be adapted not to break due to 0025378 and 0025248
(0063234)
alostale (manager)
2014-01-07 13:45
edited on: 2014-01-07 13:48

Executed test cases:

* In an environment with 360K products executed cases defined in https://docs.google.com/spreadsheet/ccc?key=0AmPGxKaZaJn-dFY4cjMyWXFvbG00VFkwekpDQ25Rd3c&usp=sharing [^]
* Test cases defined for issue 0025248

Performance improvement:

Tested in previous environment: https://docs.google.com/a/openbravo.com/spreadsheet/ccc?key=0AmPGxKaZaJn-dFQ1LVlnLVZOclE3QWhRYnI4aF9jUHc&usp=drive_web#gid=0 [^]

Average 47% faster using drop down list (note this hugely varies depending on data and window)

(0063276)
hgbot (developer)
2014-01-08 08:26

Repository: erp/devel/pi
Changeset: 46ec5ec2839da79ae27a5225c105c85ecde1cf6a
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Jan 07 13:20:26 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/46ec5ec2839da79ae27a5225c105c85ecde1cf6a [^]

fixed bug 25182: FK filter drop down list bad perfomance

 Changed the way FK drop down list query is composed

 For example filtering organization in product window:
  * Before it was:
    select distinct o.AD_Org_ID
      from M_Product p left join AD_Org o
                              on p.AD_Org_ID = o.AD_Org_ID
    where (//grid filter in product)

  * Now it is:
    select o.AD_Org_ID
      from AD_Org o
     where exists (select 1
                     from M_Product p
                    where p.AD_Org_ID = o.AD_Org_ID
                      and (//grid filter in product))

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
M modules/org.openbravo.service.json/src/org/openbravo/service/json/DataEntityQueryService.java
M modules/org.openbravo.service.json/src/org/openbravo/service/json/DefaultJsonDataService.java
---
(0063481)
hudsonbot (developer)
2014-01-17 15:49

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/9669102eb541 [^]
Maturity status: Test
(0063521)
shankarb (reporter)
2014-01-20 06:25

Code reviewed and verified in pi changeset 6e2d2a007761.

- Issue History
Date Modified Username Field Change
2013-11-19 13:30 jonalegriaesarte New Issue
2013-11-19 13:30 jonalegriaesarte Assigned To => AugustoMauch
2013-11-19 13:30 jonalegriaesarte Modules => Core
2013-11-19 13:30 jonalegriaesarte Triggers an Emergency Pack => No
2013-11-19 13:31 jonalegriaesarte Tag Attached: Performance
2013-11-19 17:43 alostale Assigned To AugustoMauch => shankarb
2013-11-25 12:51 alostale Relationship added related to 0025210
2013-11-25 12:57 alostale Relationship added related to 0025211
2013-12-02 09:55 jonalegriaesarte Target Version 3.0MP30 => 3.0MP31
2013-12-17 09:44 alostale File Added: issue-25182.diff
2013-12-17 09:45 alostale Note Added: 0062739
2013-12-17 09:45 alostale Target Version 3.0MP31 => 3.0MP32
2013-12-17 09:45 alostale Assigned To shankarb => alostale
2013-12-19 17:29 alostale Relationship added related to 0025378
2013-12-19 17:30 alostale Note Added: 0062881
2013-12-19 17:30 alostale Relationship added related to 0025248
2013-12-19 17:51 alostale Relationship replaced blocks 0025378
2014-01-07 13:45 alostale Note Added: 0063234
2014-01-07 13:48 alostale Note Edited: 0063234 View Revisions
2014-01-07 13:56 alostale Review Assigned To => shankarb
2014-01-08 08:26 hgbot Checkin
2014-01-08 08:26 hgbot Note Added: 0063276
2014-01-08 08:26 hgbot Status new => resolved
2014-01-08 08:26 hgbot Resolution open => fixed
2014-01-08 08:26 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/46ec5ec2839da79ae27a5225c105c85ecde1cf6a [^]
2014-01-17 15:49 hudsonbot Checkin
2014-01-17 15:49 hudsonbot Note Added: 0063481
2014-01-20 06:25 shankarb Note Added: 0063521
2014-01-20 06:25 shankarb Status resolved => closed
2014-04-22 08:37 alostale Relationship added related to 0026317
2014-05-12 12:38 guillermogil Relationship added related to 0026505
2014-11-05 10:15 alostale Relationship added causes 0028085
2014-12-24 11:51 AugustoMauch Relationship added related to 0028483
2015-03-26 14:54 alostale Relationship added causes 0029385


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker