Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0016373Openbravo ERPI. Performancepublic2011-03-18 05:102012-09-26 10:06
Reportereintelau 
Assigned Togorkaion 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target Version3.0MP1Fixed in Version 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customer
Web browser
ModulesUser Interface Selector
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0016373: Product selector & grid not usable when system has large number of products active
DescriptionWhen there are a large number of products set up in the system (eg 1000+) then the product selector and popup are not usable.

The query(s) that are sent to the database run for a minute or more before they complete & the database process is at 100% cpu for this period.

The underlying issue seems to be that the product selector is retrieving its data from the M_Product_Price_Warehouse_v view, and this view has several columns that are derived by calling stored procedures.

I have checked the calls and limits are being applied properly to the queries, but because there are also order by clauses, the database has to completely resolve the underlying view, sort it by the required column, then return the limited number of rows.
i.e. it has to process all the rows in the view before it can return a result
Steps To Reproduce1) Setup a test system with 1000+ products in a price list.
2) Create a sales order using this price list
3) Create a line and try to select a product.
Proposed SolutionChange selector's view avoiding the usage of 'm_bom_*' pl/sql functions. This change will slightly modify the results. As the available quantity won't include quantities potentially available via BOM productions.
Additional Information
TagsNo tags attached.
Relationships
related to defect 00166113.0RC7 closed mtaal Initial request done by UI selector element in form view does not use any filters or paging 
related to defect 00166213.0RC7 closed mtaal Selector item does an unnecessary count operation on server 
related to defect 0016622 closed alostale ReadOnlyDataSourceService doesn't honour the prevent count parameter 
related to defect 00166233.0MP1 closed alostale CustomQuerySelectorDatasource is very inefficient when filtering using a foreign key reference 
Attached Filespatch 16373.patch (3,127) 2011-04-03 13:09
https://issues.openbravo.com/file_download.php?file_id=3817&type=bug
Issue History
Date ModifiedUsernameFieldChange
2011-03-18 05:10eintelauNew Issue
2011-03-18 05:10eintelauAssigned To => adrianromero
2011-03-18 05:10eintelauModules => User Interface Selector
2011-03-18 05:16eintelauNote Added: 0035026
2011-03-18 11:50adrianromeroAssigned Toadrianromero => alostale
2011-03-21 10:12alostalePriorityhigh => urgent
2011-03-21 10:12alostaleTarget Version => 3.0RC6
2011-03-21 10:12alostaleStatusnew => scheduled
2011-03-21 10:12alostaleAssigned Toalostale => shuehner
2011-03-21 10:12alostalefix_in_branch => pi
2011-04-01 16:28shuehnerRelationship addedrelated to 0016611
2011-04-01 16:29shuehnerNote Added: 0035474
2011-04-03 13:09eintelauNote Added: 0035494
2011-04-03 13:09eintelauFile Added: 16373.patch
2011-04-03 13:11eintelauNote Added: 0035495
2011-04-03 14:05eintelauNote Edited: 0035495bug_revision_view_page.php?bugnote_id=0035495#r1759
2011-04-05 12:44shuehnerNote Added: 0035597
2011-04-05 12:44shuehnerTarget Version3.0RC6 => 3.0RC7
2011-04-05 12:44shuehnerfix_in_branchpi =>
2011-04-05 12:47shuehnerNote Added: 0035599
2011-04-05 14:06shuehnerCategory07. Sales management => I. Performance
2011-04-05 17:18shuehnerAssigned Toshuehner => gorkaion
2011-04-26 12:27gorkaionNote Added: 0036089
2011-04-26 12:27gorkaionProposed Solution updated
2011-04-26 17:02hgbotCheckin
2011-04-26 17:02hgbotNote Added: 0036105
2011-04-26 17:02hgbotStatusscheduled => resolved
2011-04-26 17:02hgbotResolutionopen => fixed
2011-04-26 17:02hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a3d93e831e8a3f8ab456d8590772f7d05052ef9d [^]
2011-05-12 11:58miruritaNote Added: 0036715
2011-05-12 13:26hudsonbotCheckin
2011-05-12 13:26hudsonbotNote Added: 0036770
2011-05-16 13:46dmitry_mezentsevNote Added: 0037020
2011-05-16 14:21eintelauNote Added: 0037023
2011-05-16 14:44shuehnerNote Added: 0037026
2011-05-16 15:21dmitry_mezentsevNote Added: 0037028
2011-05-17 17:21eintelauNote Added: 0037099
2011-05-17 20:00dmitry_mezentsevNote Added: 0037111
2011-05-18 06:44eintelauNote Added: 0037118
2011-05-19 11:22dmitry_mezentsevNote Added: 0037198
2011-05-19 11:22dmitry_mezentsevStatusresolved => new
2011-05-19 11:22dmitry_mezentsevResolutionfixed => open
2011-05-19 11:22dmitry_mezentsevTarget Version3.0RC7 => 3.0MP0
2011-05-23 13:50dmitry_mezentsevPriorityurgent => high
2011-06-02 10:54dmitry_mezentsevTarget Version3.0MP0 => 3.0MP1
2011-06-17 13:13gorkaionRelationship addedrelated to 0016621
2011-06-17 13:14gorkaionRelationship addedrelated to 0016622
2011-06-17 13:14gorkaionRelationship addeddepends on 0016623
2011-06-17 13:14gorkaionRelationship deleteddepends on 0016623
2011-06-17 13:14gorkaionRelationship addedrelated to 0016623
2011-06-21 13:08hgbotCheckin
2011-06-21 13:08hgbotNote Added: 0038412
2011-06-21 13:08hgbotStatusnew => resolved
2011-06-21 13:08hgbotResolutionopen => fixed
2011-06-21 13:08hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/a3d93e831e8a3f8ab456d8590772f7d05052ef9d [^] => http://code.openbravo.com/erp/devel/pi/rev/f45b5982de9a7848f2fce6422a148610dba28b36 [^]
2011-06-22 11:44hudsonbotCheckin
2011-06-22 11:44hudsonbotNote Added: 0038473
2011-07-12 11:58gorkaionNote Added: 0039008
2011-07-14 16:03eintelauNote Added: 0039065
2011-07-14 16:33dmitry_mezentsevNote Added: 0039067
2012-09-26 10:06dmiguelezClosed by => dmiguelez
2012-09-26 10:06dmiguelezNote Added: 0052609
2012-09-26 10:06dmiguelezStatusresolved => closed

Notes
(0035026)
eintelau   
2011-03-18 05:16   
FYI - We found this problem when testing a dataset from a potential client that has 100,000 + products.
(0035474)
shuehner   
2011-04-01 16:29   
Adding relation to another issue found while verifying/debugging this issue.

Note: in 2.50 there corresponding query did contain some a bit unwieldy sub-selects to work around the problem with the orderby/limit. To only order by a much smaller/simpler set of columns and evaluate the pl-functions used only afterwards.
(0035494)
eintelau   
2011-04-03 13:09   
I was able to significantly improve the performance of the selector by reimplementing m_product_price_warehouse_v without the use of stored procedures in the select columns.

It is doesn't give exactly the same result as it doesn't take into account quantities potentially available vi bom production, but I believe getting the performance and usability is more important than this functionality.

Patch with view implementation attached
(0035495)
eintelau   
2011-04-03 13:11   
(edited on: 2011-04-03 14:05)
Note: Although this patch did greatly improve performance it was not sufficient to make our test dataset (with 100,000+ products) usable.

To get sufficient performance I had to create a selector using a custom query & also fix the following issues : 16621, 16622, 16623, 16611 (for custom queries)

The custom selector has less data than the normal one but is much more efficient. I am still working on the implementation to see I can reproduce the standard selector's data/functionality.
(0035597)
shuehner   
2011-04-05 12:44   
->rc7 as rc6 freezing today and full fix will not be ready today.
(0035599)
shuehner   
2011-04-05 12:47   
eintelau: Thx for the patches (+ links to the other) issues. Haven'T been aware of those until now (besides the count issue which was known). Will review
(0036089)
gorkaion   
2011-04-26 12:27   
Provided patch will be reviewed and applied.
(0036105)
hgbot   
2011-04-26 17:02   
Repository: erp/devel/pi
Changeset: a3d93e831e8a3f8ab456d8590772f7d05052ef9d
Author: Gorka Ion Damián <gorkaion.damian <at> openbravo.com>
Date: Tue Apr 26 16:54:48 2011 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/a3d93e831e8a3f8ab456d8590772f7d05052ef9d [^]

Fixed issue 16373.Modified view to avoid usage of pl functions.

---
M src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml
A src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml
---
(0036715)
mirurita   
2011-05-12 11:58   
@eintelau: we are about to freeze RC7 and we need to close all the issues with target RC7. Could you please review this issue and close if it has been properly resolved.

Thanks.
(0036770)
hudsonbot   
2011-05-12 13:26   
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/c40e579fcd38 [^]

Maturity status: Test
(0037020)
dmitry_mezentsev   
2011-05-16 13:46   
Hello Ben,

Can we expect you to review this issue today-tomorrow?

Thanks.
(0037023)
eintelau   
2011-05-16 14:21   
Hi Dimitry,

Sorry, missed this one. Will review tomorrow (my time)

regards
Ben
(0037026)
shuehner   
2011-05-16 14:44   
Hi i did some mini-check as part of reviewing another issue (16611). And looks better but not perfect. Using 10k products + pricelist entries open of selector is now fine scrolling a page to load next batch of row is still slow (4-8s for the request) but doesn't fail at least.
(0037028)
dmitry_mezentsev   
2011-05-16 15:21   
Thanks, Stefan.

Let´s see Ben´s opinion on current system behavior.

Anyway I agree with you and for me 4-8s is a lot.
The only thing is that if in principle it is now working and is not stopping system operation I would not stop RC7 for this but would make sure that for MP0 we have much better ("agile" :-)) results.
(0037099)
eintelau   
2011-05-17 17:21   
Hi,
Have had some system environment issues here that have prevented me testing this. Will hopefully resolve tomorrow & get back to you.
(0037111)
dmitry_mezentsev   
2011-05-17 20:00   
Hi Ben,
OK, thanks for the update.
(0037118)
eintelau   
2011-05-18 06:44   
Hi,

I've tested the changes and it has improved performance a lot. The base query is now down to the 3 to 5 second range on my system which is orders of magnitude better than the 60+ secs I was seeing previously.

The response is still a bit too slow for good usability, especially when typing into the product selector on a edit screen (you end up trigger several concurrent queries as you type, which really hammers the database/CPU).

I am also seeing garbage collection exceptions in my logs "java.lang.OutOfMemoryError: GC overhead limit exceeded" while paging through the grid and selecting products. That may be my environment, I will investigate a bit more & see if I can narrow it down.
(0037198)
dmitry_mezentsev   
2011-05-19 11:22   
There is a performance improvement but still it is not enough.
(0038412)
hgbot   
2011-06-21 13:08   
Repository: erp/devel/pi
Changeset: f45b5982de9a7848f2fce6422a148610dba28b36
Author: Gorka Ion Damián <gorkaion.damian <at> openbravo.com>
Date: Tue Jun 21 13:08:09 2011 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/f45b5982de9a7848f2fce6422a148610dba28b36 [^]

Fixed issue 16373.Modified views to avoid union clause.

---
M modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/FIN_ExecutePayment.java
M src-db/database/model/views/M_PRODUCT_STOCK_V.xml
M src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml
---
(0038473)
hudsonbot   
2011-06-22 11:44   
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/abb438586c0e [^]

Maturity status: Test
(0039008)
gorkaion   
2011-07-12 11:58   
Hi Ben,

Please, could you review if the latest fix has a better performance on your environment?

Thanks
(0039065)
eintelau   
2011-07-14 16:03   
Hi Gorka,

I did an update and couple of weeks back (for MP0) & I think the performance was still too slow. I will run the tests again next week and let you know.

regards
Ben
(0039067)
dmitry_mezentsev   
2011-07-14 16:33   
Hi Ben,

This fix is in MP1 (now in QA maturity status but tomorrow will reach QA Approved) so you would need to update to it to validate.


Regards,
Dmitry.
(0052609)
dmiguelez   
2012-09-26 10:06   
Code Review + Testing OK