Openbravo Issue Tracking System - Openbravo ERP
|View Issue Details|
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0029664||Openbravo ERP||02. Master data management||public||2015-04-22 10:18||2015-06-03 18:55|
|Assigned To||vmromanos|| |
|Platform||OS||30||OS Version||Ubuntu 14.04|
|Product Version||3.0PR15Q1|| |
|Target Version||3.0PR15Q3||Fixed in Version||pi|| |
|Review Assigned To||umartirena|
|Web browser||Google Chrome|
|Regression introduced in release|
|Regression introduced by commit|
|Triggers an Emergency Pack||No|
|Summary||0029664: Create Price List process is slow|
|Description||Depending the instance and the amount of products (3000 to 7000) in a price list, Create Price List process takes too long to finish.|
In the instance the avg time of the process is 156 seg, max 241 seg and min 77 seg.
To see this, you can execute:
select classname, command, count(*), sum(process_time), min(process_time), max(process_time), avg(process_time)
where created >= (some recent date)
and command like 'SAVE_BUTTONProcCreate103'
group by classname, command order by sum desc;
We have an instance to reproduce this.
|Steps To Reproduce||- Log in an instance with a lot of products|
- Go to Price List window
- Generate a new price list version
- Click on create price list button
- Execute the previous query
|related to ||feature request ||0029943|| ||closed ||alostale ||support to define DB functions volatility |
|related to ||defect ||0036403|| ||closed ||malsasua ||M_PriceList_Create process is very slow with a big volume |
|2015-04-22 10:18||ioritzCia||New Issue|
|2015-04-22 10:18||ioritzCia||Assigned To|| => Triage Finance|
|2015-04-22 10:18||ioritzCia||Web browser|| => Google Chrome|
|2015-04-22 10:18||ioritzCia||Modules|| => Core|
|2015-04-22 10:18||ioritzCia||Triggers an Emergency Pack|| => No|
|2015-04-22 10:19||ioritzCia||Tag Attached: Performance|
|2015-04-23 10:09||jonalegriaesarte||Web browser||Google Chrome => Google Chrome|
|2015-04-23 10:09||jonalegriaesarte||Target Version|| => 3.0PR15Q3|
|2015-04-28 15:53||shuehner||Issue Monitored: shuehner|
|2015-05-20 15:52||vmromanos||Status||new => scheduled|
|2015-05-20 15:52||vmromanos||Assigned To||Triage Finance => vmromanos|
|2015-05-20 15:53||vmromanos||Relationship added||related to 0029943|
|2015-05-20 16:36||vmromanos||Note Added: 0077602|
|2015-05-20 17:16||vmromanos||Note Added: 0077603|
|2015-06-02 16:51||hgbot||Note Added: 0077993|
|2015-06-02 16:51||hgbot||Status||scheduled => resolved|
|2015-06-02 16:51||hgbot||Resolution||open => fixed|
|2015-06-02 16:51||hgbot||Fixed in SCM revision|| => http://code.openbravo.com/erp/devel/pi/rev/0b60872f78f5802ca0edab16e7432a0c59e5d3ce [^]|
|2015-06-02 16:52||umartirena||Review Assigned To|| => umartirena|
|2015-06-02 16:52||umartirena||Note Added: 0077994|
|2015-06-02 16:52||umartirena||Status||resolved => closed|
|2015-06-02 16:52||umartirena||Fixed in Version|| => pi|
|2015-06-03 18:55||hudsonbot||Note Added: 0078040|
|2017-07-04 16:20||vmromanos||Relationship added||related to 0036403|
Test plan must be run in PostgreSQL and Oracle!
Test plan I (no regression):
Go to Price List window
Select Tarifa de ventas
Go to Price List version
Create a new record. Leave default values and set Base Version (default) = Tarifa de ventas
Press Create Price List
Verify process is run successfully
Test plan II (no regression):
Remove Product Prices created before
Go to Price List version record created before and set Base Version (default) = null
Go to the Price list tab, and set Costing Price List = Y (if the field is not displayed, you can show it or directly update it through the DB)
Run again the Price List process.
Verify process is run successfully
Test plan III (performance):
In an environment with thousands of products and without the fix, run the Test Plan I and II. Take note of the total time.
Now apply the patch and run the Test Plan I and II. Take note of the total time.
Verify the time consumed is lower after applying the patch.
See my number in https://docs.google.com/spreadsheets/d/1DU-jqccFefFRUBXDTTsCe0jF8BI_Uv6llmwBvEKrX4k/edit#gid=1059414576 [^]
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Wed May 20 16:30:14 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0b60872f78f5802ca0edab16e7432a0c59e5d3ce [^]
Fixed bug 29664: Create Price List performance
The patch contains the following changes that improve the process performance (in my local environment the performance is increased an average of 80%):
1. count(*) inside M_GET_NO_TRX_PRODUCT_COST has been rewritten to use exists clause
2. Added 2 indices to M_Product_PO table on M_PRODUCT_ID and C_BPARTNER_ID columns to reduce seq. scans on this table
3. M_PRICELIST_CREATE: Force to analyze C_TEMP_Selection table in Postgres
According to PostgreSQL documentation, the autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. So it's recommended to run ANALYZE after the table is populated to improve performance.
Oracle doesn't seem to be affected by this problem, so the analyze is only executed for postgres
4. M_PRICELIST_CREATE: Complex queries that fill the C_TEMP_Selection has been rewritten using a dynamic SQL run through the EXECUTE command.
Although these queries are in general fast, they are executed as many times as records to be inserted into the Price List. So, the bigger amount of records the slower the process is.
It has been detected that the execution plan created for these queries can be improved a lot if we remove useless parts in the where clause (OR stuff). That's why we dynamically build the sql based on the parameters, which drastically improves the execution plan performance.
5. M_PRICELIST_CREATE: Removed useless join to ad_client table to calculate the client's currency. Instead we get it at the beginning of the process just one time.
6. M_PRICELIST_CREATE: Removed code included into the IF (v_Costbased = 'N'AND (v_PriceList_Version_Base_ID IS NULL)). This code is never executed, because there is a validation at the beginning of the process to avoid that situation.
Other changes not included that could improve performance:
1. Changing the db functions ad_isorgincluded and ad_org_isinnaturaltree from VOLATILE to STABLE reduces execution time for cost based price lists.
Right now the DBSM doesn't support this flag, so a feature request 0029943 has been created