Openbravo Issue Tracking System - Openbravo ERP |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0032493 | Openbravo ERP | 04. Warehouse management | public | 2016-03-16 15:02 | 2017-03-15 20:21 |
|
| Reporter | ngarcia | |
| Assigned To | vmromanos | |
| Priority | urgent | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | |
| Platform | | OS | 5 | OS Version | |
| Product Version | | |
| Target Version | | Fixed in Version | | |
| Merge Request Status | |
| Review Assigned To | dmiguelez |
| OBNetwork customer | |
| Web browser | |
| Modules | Core |
| Support ticket | |
| Regression level | |
| Regression date | |
| Regression introduced in release | |
| Regression introduced by commit | |
| Triggers an Emergency Pack | No |
|
| Summary | 0032493: Pareto Product Report takes too long in an environment with big amount of data |
| Description | Pareto Product Report takes too long in an environment with big amount of data.
In a customer environment it lasted three hours with 401191 records in the m_transaction_cost table
The problem is in the select function of the ReportParetoProduct_data.xsql |
| Steps To Reproduce | As group admin role:
Go to Pareto Product Report window
Add the required filters
Launch it
Check it does not show any values in a while
Session is closed
|
| Proposed Solution | |
| Additional Information | |
| Tags | Performance |
| Relationships | | related to | design defect | 0031874 | | closed | Triage Omni OMS | Modules | Performance problems in Valued Stock Report Enhanced | | related to | defect | 0032504 | 3.0PR16Q3 | closed | aferraz | Openbravo ERP | Huge performance problem in the costing background process and using the Negative stock correction too | | related to | design defect | 0022417 | | new | Triage Omni OMS | Openbravo ERP | The Pareto Product Legacy reports takes long to show a big amount of data | | related to | design defect | 0035410 | | closed | vmromanos | Openbravo ERP | Default currency in Pareto Product Report | | related to | defect | 0035415 | | closed | dmiguelez | Openbravo ERP | Generate Aggregated Data Background doesn't work in Oracle | | related to | design defect | 0035416 | | new | Triage Omni WMS | Openbravo ERP | Update ABC in Pareto Product Report should be refactor | | related to | feature request | 0035417 | | new | Triage Omni WMS | Openbravo ERP | ABC percentages should be user defined | | related to | feature request | 0035418 | | new | Triage Omni WMS | Openbravo ERP | Update ABC button should be disabled when any Warehouse has been used in the filters | | related to | defect | 0035427 | | closed | markmm82 | Openbravo ERP | Conversion of Valuation is not correct in Pareto Product Report |
|
| Attached Files | |
|
| Issue History |
| Date Modified | Username | Field | Change |
| 2016-03-16 15:02 | ngarcia | New Issue | |
| 2016-03-16 15:02 | ngarcia | Assigned To | => Triage Finance |
| 2016-03-16 15:02 | ngarcia | Modules | => Core |
| 2016-03-16 15:02 | ngarcia | Triggers an Emergency Pack | => No |
| 2016-03-16 15:02 | ngarcia | Issue Monitored: networkb | |
| 2016-03-16 15:02 | ngarcia | Tag Attached: Performance | |
| 2016-03-16 15:03 | ngarcia | Resolution time | => 1460757600 |
| 2016-03-16 16:59 | ngarcia | Description Updated | bug_revision_view_page.php?rev_id=11470#r11470 |
| 2016-03-16 17:00 | ngarcia | Description Updated | bug_revision_view_page.php?rev_id=11471#r11471 |
| 2016-03-16 17:03 | ngarcia | Description Updated | bug_revision_view_page.php?rev_id=11472#r11472 |
| 2016-04-05 12:09 | aferraz | Assigned To | Triage Finance => AtulOpenbravo |
| 2016-04-18 12:34 | AtulOpenbravo | Status | new => scheduled |
| 2016-04-19 14:43 | aferraz | Note Added: 0085746 | |
| 2016-04-19 14:43 | aferraz | Type | defect => design defect |
| 2016-04-19 14:44 | aferraz | Resolution time | 1460757600 => |
| 2016-04-19 14:45 | aferraz | Relationship added | related to 0031874 |
| 2016-04-19 14:45 | aferraz | Relationship added | related to 0032504 |
| 2016-04-19 14:46 | aferraz | Assigned To | AtulOpenbravo => Triage Finance |
| 2016-04-19 14:46 | aferraz | Status | scheduled => feedback |
| 2016-04-19 14:46 | aferraz | Status | feedback => new |
| 2016-06-28 12:19 | heccam | Issue Monitored: heccam | |
| 2016-08-02 13:38 | ngarcia | Relationship added | has duplicate 0022417 |
| 2016-08-02 13:40 | ngarcia | Relationship deleted | has duplicate 0022417 |
| 2016-08-02 14:34 | ngarcia | Relationship added | related to 0022417 |
| 2016-08-09 10:35 | aferraz | Note Edited: 0085746 | bug_revision_view_page.php?bugnote_id=0085746#r12808 |
| 2016-12-28 11:00 | vmromanos | Status | new => scheduled |
| 2016-12-28 11:00 | vmromanos | Assigned To | Triage Finance => Sanjota |
| 2017-03-02 14:22 | vmromanos | Relationship added | related to 0035410 |
| 2017-03-02 14:24 | vmromanos | Assigned To | Sanjota => vmromanos |
| 2017-03-02 14:24 | vmromanos | Review Assigned To | => dmiguelez |
| 2017-03-02 16:22 | vmromanos | Note Added: 0094691 | |
| 2017-03-02 17:12 | vmromanos | Note Edited: 0094691 | bug_revision_view_page.php?bugnote_id=0094691#r14722 |
| 2017-03-02 17:40 | vmromanos | Relationship added | related to 0035415 |
| 2017-03-02 17:53 | vmromanos | Relationship added | related to 0035416 |
| 2017-03-02 17:57 | vmromanos | Relationship added | related to 0035417 |
| 2017-03-02 18:03 | vmromanos | Relationship added | related to 0035418 |
| 2017-03-03 12:48 | hgbot | Checkin | |
| 2017-03-03 12:48 | hgbot | Note Added: 0094723 | |
| 2017-03-03 12:48 | hgbot | Status | scheduled => resolved |
| 2017-03-03 12:48 | hgbot | Resolution | open => fixed |
| 2017-03-03 12:48 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/a5058ea3c279dbeb6670bea46c5fd43f5c095999 [^] |
| 2017-03-03 12:53 | dmiguelez | Relationship added | related to 0035427 |
| 2017-03-03 12:55 | dmiguelez | Note Added: 0094726 | |
| 2017-03-03 12:55 | dmiguelez | Status | resolved => closed |
| 2017-03-15 20:21 | hudsonbot | Checkin | |
| 2017-03-15 20:21 | hudsonbot | Note Added: 0095254 | |
|
Notes |
|
|
(0085746)
|
|
aferraz
|
2016-04-19 14:43
(edited on: 2016-08-09 10:35) |
|
Moved to design defect.
The problem is similar to what we have in Valued Stock Report: to get the valuation of a product we need to add all the records in the M_Transaction_Cost table as we do not have aggregated values right now. The more transactions we make for a product the slower the report will be.
In this report the problem is double as we need to check the M_Transaction_Cost table twice (one grouping by product and the other one without grouping).
To fix it we need to implement the same solution as in Valued Stock Report: use periods to aggregate information and check M_Transaction_Cost table only starting from this point (see issue 0031874).
Another approach would be to use new cumcost column in M_Costing table developed in issue 0032504, but this would not work in case your costing rule is not configured for warehouse dimension as you will have accumulated valuation per legal entity but not per warehouse.
|
|
|
|
(0094691)
|
|
vmromanos
|
2017-03-02 16:22
(edited on: 2017-03-02 17:12) |
|
Test plan I (performance):
It has been tested that, even without aggregated data, the report performs really well on high volume environments.
* A real environment without aggregated data and with 7.7E+6 transactions took only 38 seconds to get the data for an organization with 250E+3 transactions.
* An artificial environment without aggregated data and with 25E+03 transactions took only less than 4 seconds to get the data for 10E+03 transactions.
Test plan II (data consistency):
In an environment without aggregated data (you can truncate m_valued_stock_agg table to be sure), run the Pareto Report and the Valued Stock Report for the same organization, currency and date.
Verify exactly the same products, quantities, unit, unit cost and valuation is shown in both reports.
This test has been executed in F&B US
Test plan III (data consistency filtering by warehouse):
Run Pareto Product Report for F&B US.
Verify Organizations F&B East and West are shown in results.
Filter by Warehouse US East Cost and verify only organization F&B East is shown in results.
Test plan IV (aggregation of data):
In an environment without data aggregation
Launch the Pareto Report for F&B US and take a screenshot
Go to Open/Close Period Control window and select all the previous years and close them
Go to Process Request, create a new record for * and Generate Aggregated Data Background process. Schedule it and wait till it's finished.
Launch again the Pareto Report for F&B US and compare with the screenshot. It should show exactly the same values.
Test plan V (mix non-aggregated and aggregated data):
After launching previous Test Plan, create a new Product for East organization, set a purchase price.
Create a physical inventory for East organization. Enter a line with this product and quantity = 60. Process it.
Wait till the costing background process the transaction.
Launch the Pareto Product Report for US organization.
Verify the report is exactly the same as in the screenshot, but adding a line for the new product.
All these tests have been executed in Oracle and PostgreSQL
|
|
|
|
(0094723)
|
|
hgbot
|
|
2017-03-03 12:48
|
|
Repository: erp/devel/pi
Changeset: a5058ea3c279dbeb6670bea46c5fd43f5c095999
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Thu Mar 02 14:03:01 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a5058ea3c279dbeb6670bea46c5fd43f5c095999 [^]
Fixed bug 32493: Pareto Product Report performance refactor
The Pareto Product report had very important performance issues due to the way data was retrieved from the database.
Multiple (and unnecessary) sequencial scans were executed on high volume tables making the report unusable on real environments.
Besides it has been detected that the information shown was not right: products without stock might appear in the report, the percentages were clearly not right and the ABC category was also wrong because it was based on wrong percentages.
The fix mainly includes a total refactor of the queries, which fixes most of the performance issues.
It also adds data aggregation support based on Valued Stock aggregated data, which should help to keep the report's performance when the time goes by.
It has been tested that, even without aggregated data, the report performs really well on high volume environments. Example: An environment without aggregated data and with 7.7E+6 transactions took only 38 seconds to get the data for an organization with 250E+3 transactions; before it took "days".
List of important changes:
* Deprecated M_GET_PARETO_ABC function and implemented the same logic directly into the SQL query
* Added index on M_TRANSACTION_COST table to the DATEACCT column, which is heavily used as a filter criteria for many reports (included Pareto Product)
* Modified index M_VALUED_STOCK_AGG_DATETO on M_VALUED_STOCK_AGG to take into account AD_ORG_ID and DATETO. This makes the queries to get data from this aggregated table to perform really well.
* Modified ReportParetoProduct_data.xsq select query. This is actually the key to get the performance improvement:
* Usage of window functions to calculate:
* the total value per organization's warehouse,
* the percentage per product and organization's warehouse,
* the accumulated percentage per product and organization's warehouse.
* The ABC is calculated looking at the accumulated percentage gotten before instead of calling the M_GET_PARETO_ABC db function.
* Reduced the usage of functions in select clause because, although individually they are quite fast, they must be executed over multiple records thus making the whole query really slow (example 2ms multiplied by 10E3 records delays the query 20 seconds!):
* ad_get_org_le_bu is not needed anymore because the legal entity will be always the same for each record in the report. Note that the Pareto's organization combo only shows legal entities or child organizations (which only have a unique legal entity).
* c_currency_convert_precision is only called when a currency conversion is needed, thus reducing unnecessary overhead.
* ad_column_identifier calls have been removed. Instead we get static (translated) values.
* Transactions are now filtered by trxprocessdate when the costing rule was started, so we avoid to compute legacy records.
* Although not strictly necessary to get a performance improvement, the query has been split in several CTEs, thus making the query more readable and easy to maintain.
---
M src-db/database/model/functions/M_GET_PARETO_ABC.xml
M src-db/database/model/tables/M_TRANSACTION_COST.xml
M src-db/database/model/tables/M_VALUED_STOCK_AGG.xml
M src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct.java
M src/org/openbravo/erpCommon/ad_reports/ReportParetoProduct_data.xsql
M src/org/openbravo/erpCommon/ad_reports/ReportValuationStock.java
---
|
|
|
|
|
|
|
|
|
|