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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0029664
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 02. Master data managementmajoralways2015-04-22 10:182015-06-03 18:55
ReporterioritzCiaView Statuspublic 
Assigned Tovmromanos 
PriorityhighResolutionfixedFixed in Versionpi
StatusclosedFix in branchFixed in SCM revision0b60872f78f5
ProjectionnoneETAnoneTarget Version3.0PR15Q3
OSLinux 64 bitDatabasePostgreSQLJava version6
OS VersionUbuntu 14.04Database version9.3Ant version
Product Version3.0PR15Q1SCM revision 
Review Assigned Toumartirena
Web browserGoogle Chrome
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0029664: 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
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to feature request 0029943 closedalostale support to define DB functions volatility 
related to defect 0036403 closedmalsasua M_PriceList_Create process is very slow with a big volume 

-  Notes
(0077602)
vmromanos (manager)
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 (manager)
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 (developer)
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 (reporter)
2015-06-02 16:52

 Code Review + Testing OK
(0078040)
hudsonbot (developer)
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

- Issue History
Date Modified Username Field Change
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 Resolution time => 1432245600
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 Checkin
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 Checkin
2015-06-03 18:55 hudsonbot Note Added: 0078040
2017-07-04 16:20 vmromanos Relationship added related to 0036403

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker