Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0029664Openbravo ERP02. Master data managementpublic2015-04-22 10:182015-06-03 18:55
ReporterioritzCia 
Assigned Tovmromanos 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS30OS VersionUbuntu 14.04
Product Version3.0PR15Q1 
Target Version3.0PR15Q3Fixed in Versionpi 
Merge Request Status
Review Assigned Toumartirena
OBNetwork customer
Web browserGoogle Chrome
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0029664: Create Price List process is slow
DescriptionDepending 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)
from ad_session_usage_audit
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
Proposed Solution
Additional Information
TagsPerformance
Relationships
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 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-04-22 10:18ioritzCiaNew Issue
2015-04-22 10:18ioritzCiaAssigned To => Triage Finance
2015-04-22 10:18ioritzCiaWeb browser => Google Chrome
2015-04-22 10:18ioritzCiaModules => Core
2015-04-22 10:18ioritzCiaTriggers an Emergency Pack => No
2015-04-22 10:19ioritzCiaTag Attached: Performance
2015-04-23 10:09jonalegriaesarteWeb browserGoogle Chrome => Google Chrome
2015-04-23 10:09jonalegriaesarteResolution time => 1432245600
2015-04-23 10:09jonalegriaesarteTarget Version => 3.0PR15Q3
2015-04-28 15:53shuehnerIssue Monitored: shuehner
2015-05-20 15:52vmromanosStatusnew => scheduled
2015-05-20 15:52vmromanosAssigned ToTriage Finance => vmromanos
2015-05-20 15:53vmromanosRelationship addedrelated to 0029943
2015-05-20 16:36vmromanosNote Added: 0077602
2015-05-20 17:16vmromanosNote Added: 0077603
2015-06-02 16:51hgbotCheckin
2015-06-02 16:51hgbotNote Added: 0077993
2015-06-02 16:51hgbotStatusscheduled => resolved
2015-06-02 16:51hgbotResolutionopen => fixed
2015-06-02 16:51hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/0b60872f78f5802ca0edab16e7432a0c59e5d3ce [^]
2015-06-02 16:52umartirenaReview Assigned To => umartirena
2015-06-02 16:52umartirenaNote Added: 0077994
2015-06-02 16:52umartirenaStatusresolved => closed
2015-06-02 16:52umartirenaFixed in Version => pi
2015-06-03 18:55hudsonbotCheckin
2015-06-03 18:55hudsonbotNote Added: 0078040
2017-07-04 16:20vmromanosRelationship addedrelated to 0036403

Notes
(0077602)
vmromanos   
2015-05-20 16:36   
Database with 5.418 records run on my laptop

Original performance results (f_orig and s_orig)
After the fix performance results (f_fix and s_fix)

https://docs.google.com/spreadsheets/d/1DU-jqccFefFRUBXDTTsCe0jF8BI_Uv6llmwBvEKrX4k/edit#gid=1059414576 [^]

The execution time has been reduced around 80%
The sequential scans on m_product, m_product_po and c_temp_selection have been drastically reduced
(0077603)
vmromanos   
2015-05-20 17:16   
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 [^]
(0077993)
hgbot   
2015-06-02 16:51   
Repository: erp/devel/pi
Changeset: 0b60872f78f5802ca0edab16e7432a0c59e5d3ce
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

---
M src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml
M src-db/database/model/functions/M_PRICELIST_CREATE.xml
M src-db/database/model/tables/M_PRODUCT_PO.xml
---
(0077994)
umartirena   
2015-06-02 16:52   
Code Review + Testing OK
(0078040)
hudsonbot   
2015-06-03 18:55   
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/084e25f8f2cd [^]
Maturity status: Test