Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0025182Openbravo ERPA. Platformpublic2013-11-19 13:302014-01-20 06:25
Reporterjonalegriaesarte 
Assigned Toalostale 
PriorityurgentSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR14Q2Fixed in Version 
Merge Request Status
Review Assigned Toshankarb
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket24785
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0025182: 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;
Additional Information
TagsPerformance
Relationships
related to defect 00252483.0MP31 closed alostale Error after sorting by a computed column 
related to design defect 0025210 new AugustoMauch Adaptive filtering not working for FK dropdown filters 
related to design defect 0025211 closed AugustoMauch Filter by selected value in FK uses identifier instead of ID 
related to defect 00263173.0PR14Q2 closed alostale Product selector is not working when you try to filter by warehouse 
related to defect 0026505 closed guillermogil Reference AD_Language does not show any results on its drop-down while filtering 
related to design defect 0028483 new AugustoMauch Improve the performance of the query done to populate FK filter dropdowns in HQL based tables 
blocks defect 00253783.0PR14Q2 closed alostale drop down filter doesn't work in FK property columns 
causes defect 00280853.0PR15Q1 closed alostale On the organization window is not possible to use the organization type filter 
causes defect 0029385 closed alostale Distinct parameter is not working in JSON REST web services 
Attached Filesdiff issue-25182.diff (22,119) 2013-12-17 09:44
https://issues-new.openbravo.com/file_download.php?file_id=6571&type=bug
Issue History
Date ModifiedUsernameFieldChange
2013-11-19 13:30jonalegriaesarteNew Issue
2013-11-19 13:30jonalegriaesarteAssigned To => AugustoMauch
2013-11-19 13:30jonalegriaesarteModules => Core
2013-11-19 13:30jonalegriaesarteOBNetwork customer => Yes
2013-11-19 13:30jonalegriaesarteSupport ticket => 24785
2013-11-19 13:30jonalegriaesarteTriggers an Emergency Pack => No
2013-11-19 13:31jonalegriaesarteTag Attached: Performance
2013-11-19 17:43alostaleAssigned ToAugustoMauch => shankarb
2013-11-25 12:51alostaleRelationship addedrelated to 0025210
2013-11-25 12:57alostaleRelationship addedrelated to 0025211
2013-12-02 09:55jonalegriaesarteTarget Version3.0MP30 => 3.0MP31
2013-12-17 09:44alostaleFile Added: issue-25182.diff
2013-12-17 09:45alostaleNote Added: 0062739
2013-12-17 09:45alostaleTarget Version3.0MP31 => 3.0MP32
2013-12-17 09:45alostaleAssigned Toshankarb => alostale
2013-12-19 17:29alostaleRelationship addedrelated to 0025378
2013-12-19 17:30alostaleNote Added: 0062881
2013-12-19 17:30alostaleRelationship addedrelated to 0025248
2013-12-19 17:51alostaleRelationship replacedblocks 0025378
2014-01-07 13:45alostaleNote Added: 0063234
2014-01-07 13:48alostaleNote Edited: 0063234bug_revision_view_page.php?bugnote_id=0063234#r5292
2014-01-07 13:56alostaleReview Assigned To => shankarb
2014-01-08 08:26hgbotCheckin
2014-01-08 08:26hgbotNote Added: 0063276
2014-01-08 08:26hgbotStatusnew => resolved
2014-01-08 08:26hgbotResolutionopen => fixed
2014-01-08 08:26hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/46ec5ec2839da79ae27a5225c105c85ecde1cf6a [^]
2014-01-17 15:49hudsonbotCheckin
2014-01-17 15:49hudsonbotNote Added: 0063481
2014-01-20 06:25shankarbNote Added: 0063521
2014-01-20 06:25shankarbStatusresolved => closed
2014-04-22 08:37alostaleRelationship addedrelated to 0026317
2014-05-12 12:38guillermogilRelationship addedrelated to 0026505
2014-11-05 10:15alostaleRelationship addedcauses 0028085
2014-12-24 11:51AugustoMauchRelationship addedrelated to 0028483
2015-03-26 14:54alostaleRelationship addedcauses 0029385

Notes
(0062739)
alostale   
2013-12-17 09:45   
Attached fix, postponed to mp32
(0062881)
alostale   
2013-12-19 17:30   
fixed will require to be adapted not to break due to 0025378 and 0025248
(0063234)
alostale   
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   
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   
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   
2014-01-20 06:25   
Code reviewed and verified in pi changeset 6e2d2a007761.