0032493: Pareto Product Report takes too long in an environment with big amount of data

Notes
(0085746) aferraz (manager) 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 (manager) 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 (developer) 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 ---

(0094726) dmiguelez (developer) 2017-03-03 12:55	Code Review + Testing Ok

(0095254) hudsonbot (developer) 2017-03-15 20:21	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/54e102bef53e [^] Maturity status: Test

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	View Revisions
2016-03-16 17:00	ngarcia	Description Updated	View Revisions
2016-03-16 17:03	ngarcia	Description Updated	View Revisions
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	View Revisions
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	View Revisions
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