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

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0025182

Openbravo ERP

A. Platform

public

2013-11-19 13:30

2014-01-20 06:25

Reporter

jonalegriaesarte

Assigned To

alostale

Priority

urgent

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

3.0PR14Q2

Fixed in Version

Merge Request Status

Review Assigned To

shankarb

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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

Description

This 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 Solution

The 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

Tags

Performance

Relationships

related to	defect	0025248	3.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	0026317	3.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	0025378	3.0PR14Q2	closed	alostale	drop down filter doesn't work in FK property columns
causes	defect	0028085	3.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