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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0016373
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] I. Performancemajoralways2011-03-18 05:102012-09-26 10:06
ReportereintelauView Statuspublic 
Assigned Togorkaion 
PriorityhighResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revisionf45b5982de9a
ProjectionnoneETAnoneTarget Version3.0MP1
OSAnyDatabasePostgreSQLJava version6
OS VersionDatabase version9Ant version
Product VersionpiSCM revision 
Review Assigned Todmiguelez
Web browser
ModulesUser Interface Selector
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0016373: 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.
TagsNo tags attached.
Attached Filespatch file icon 16373.patch [^] (3,127 bytes) 2011-04-03 13:09 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 00166113.0RC7 closedmtaal Initial request done by UI selector element in form view does not use any filters or paging 
related to defect 00166213.0RC7 closedmtaal Selector item does an unnecessary count operation on server 
related to defect 0016622 closedalostale ReadOnlyDataSourceService doesn't honour the prevent count parameter 
related to defect 00166233.0MP1 closedalostale CustomQuerySelectorDatasource is very inefficient when filtering using a foreign key reference 

-  Notes
(0035026)
eintelau (developer)
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 (administrator)
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 (developer)
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 (developer)
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 (administrator)
2011-04-05 12:44

 ->rc7 as rc6 freezing today and full fix will not be ready today.
(0035599)
shuehner (administrator)
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 (developer)
2011-04-26 12:27

 Provided patch will be reviewed and applied.
(0036105)
hgbot (developer)
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 (developer)
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 (developer)
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 (developer)
2011-05-16 13:46

 Hello Ben,

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

Thanks.
(0037023)
eintelau (developer)
2011-05-16 14:21

 Hi Dimitry,

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

regards
Ben
(0037026)
shuehner (administrator)
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 (developer)
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 (developer)
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 (developer)
2011-05-17 20:00

 Hi Ben,
OK, thanks for the update.
(0037118)
eintelau (developer)
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 (developer)
2011-05-19 11:22

 There is a performance improvement but still it is not enough.
(0038412)
hgbot (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
2012-09-26 10:06

 Code Review + Testing OK

- Issue History
Date Modified Username Field Change
2011-03-18 05:10 eintelau New Issue
2011-03-18 05:10 eintelau Assigned To => adrianromero
2011-03-18 05:10 eintelau Modules => User Interface Selector
2011-03-18 05:16 eintelau Note Added: 0035026
2011-03-18 11:50 adrianromero Assigned To adrianromero => alostale
2011-03-21 10:12 alostale Priority high => urgent
2011-03-21 10:12 alostale Target Version => 3.0RC6
2011-03-21 10:12 alostale Status new => scheduled
2011-03-21 10:12 alostale Assigned To alostale => shuehner
2011-03-21 10:12 alostale fix_in_branch => pi
2011-04-01 16:28 shuehner Relationship added related to 0016611
2011-04-01 16:29 shuehner Note Added: 0035474
2011-04-03 13:09 eintelau Note Added: 0035494
2011-04-03 13:09 eintelau File Added: 16373.patch
2011-04-03 13:11 eintelau Note Added: 0035495
2011-04-03 14:05 eintelau Note Edited: 0035495 View Revisions
2011-04-05 12:44 shuehner Note Added: 0035597
2011-04-05 12:44 shuehner Target Version 3.0RC6 => 3.0RC7
2011-04-05 12:44 shuehner fix_in_branch pi =>
2011-04-05 12:47 shuehner Note Added: 0035599
2011-04-05 14:06 shuehner Category 07. Sales management => I. Performance
2011-04-05 17:18 shuehner Assigned To shuehner => gorkaion
2011-04-26 12:27 gorkaion Note Added: 0036089
2011-04-26 12:27 gorkaion Proposed Solution updated
2011-04-26 17:02 hgbot Checkin
2011-04-26 17:02 hgbot Note Added: 0036105
2011-04-26 17:02 hgbot Status scheduled => resolved
2011-04-26 17:02 hgbot Resolution open => fixed
2011-04-26 17:02 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a3d93e831e8a3f8ab456d8590772f7d05052ef9d [^]
2011-05-12 11:58 mirurita Note Added: 0036715
2011-05-12 13:26 hudsonbot Checkin
2011-05-12 13:26 hudsonbot Note Added: 0036770
2011-05-16 13:46 dmitry_mezentsev Note Added: 0037020
2011-05-16 14:21 eintelau Note Added: 0037023
2011-05-16 14:44 shuehner Note Added: 0037026
2011-05-16 15:21 dmitry_mezentsev Note Added: 0037028
2011-05-17 17:21 eintelau Note Added: 0037099
2011-05-17 20:00 dmitry_mezentsev Note Added: 0037111
2011-05-18 06:44 eintelau Note Added: 0037118
2011-05-19 11:22 dmitry_mezentsev Note Added: 0037198
2011-05-19 11:22 dmitry_mezentsev Status resolved => new
2011-05-19 11:22 dmitry_mezentsev Resolution fixed => open
2011-05-19 11:22 dmitry_mezentsev Target Version 3.0RC7 => 3.0MP0
2011-05-23 13:50 dmitry_mezentsev Priority urgent => high
2011-06-02 10:54 dmitry_mezentsev Target Version 3.0MP0 => 3.0MP1
2011-06-17 13:13 gorkaion Relationship added related to 0016621
2011-06-17 13:14 gorkaion Relationship added related to 0016622
2011-06-17 13:14 gorkaion Relationship added depends on 0016623
2011-06-17 13:14 gorkaion Relationship deleted depends on 0016623
2011-06-17 13:14 gorkaion Relationship added related to 0016623
2011-06-21 13:08 hgbot Checkin
2011-06-21 13:08 hgbot Note Added: 0038412
2011-06-21 13:08 hgbot Status new => resolved
2011-06-21 13:08 hgbot Resolution open => fixed
2011-06-21 13:08 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/a3d93e831e8a3f8ab456d8590772f7d05052ef9d [^] => http://code.openbravo.com/erp/devel/pi/rev/f45b5982de9a7848f2fce6422a148610dba28b36 [^]
2011-06-22 11:44 hudsonbot Checkin
2011-06-22 11:44 hudsonbot Note Added: 0038473
2011-07-12 11:58 gorkaion Note Added: 0039008
2011-07-14 16:03 eintelau Note Added: 0039065
2011-07-14 16:33 dmitry_mezentsev Note Added: 0039067
2012-09-26 10:06 dmiguelez Closed by => dmiguelez
2012-09-26 10:06 dmiguelez Note Added: 0052609
2012-09-26 10:06 dmiguelez Status resolved => closed

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker