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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035959
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 04. Warehouse managementmajoralways2017-05-09 11:512017-09-21 16:49
ReporterngarciaView Statuspublic 
Assigned Tomarkmm82 
PriorityurgentResolutionfixedFixed in Version3.0PR17Q3
StatusclosedFix in branchFixed in SCM revision0bfbdaa123e3
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toaferraz
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035959: Performance problem in Costing Migration Process with high volume of records in m_transaction table

DescriptionPerformance problem in Costing Migration Process with high volume of records in m_transaction table

The process gets stuck in the first call to the insertTrxCosts function, the one made from updateLegacyCosts function of the CostingMigrationProcess class
Steps To ReproduceAs group admin role:
   Open the current period

As system admin role:
   Launch the Costing Migration Process
   Once it finishes set the alert as inactive
   Launch the Costing Migration Process again
   Check it takes to long

   You could check the insert into m_transaction_cost... query remains active in pg_stat_activity for too long
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0034130 closedaferraz Costing Migration Process takes too long with big amount of records in m_transaction table 
related to defect 00325043.0PR16Q3 closedaferraz Huge performance problem in the costing background process and using the Negative stock correction too 
related to defect 00320233.0PR16Q2 closedaferraz CostingUtils.getCurrentValuedStock method consumes unneeded memory 
related to defect 00321903.0PR16Q2 closedaferraz Performance problems on the PriceDifferenceBackground process 
related to defect 0030451 closedaferraz Performance Problems on the Costing Migration Process 
related to defect 00240063.0MP25 closedpramakrishnan The Costing Migration Process should be improved to avoid performance problems. 
related to defect 0034564 closedSanjota Duplicated records in m_transactioncost table after launching the Costing Migration Process 
causes defect 0036675 closedmarkmm82 [Costing Migration Process]: Transactions related with closing inventory lines do not have an entry in Transaction Cost tab 

-  Notes
(0096810)
hgbot (developer)
2017-05-25 16:18

 Repository: erp/devel/pi
Changeset: 0bfbdaa123e3639859bb030f17050942f90465a3
Author: Mark <markmm82 <at> gmail.com>
Date: Wed May 24 16:13:14 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/0bfbdaa123e3639859bb030f17050942f90465a3 [^]

Fixes issue 35959: Performance problem in Costing Migration Process

Due the big quantity of records in m_transaction table, the insertTrx() method of
CostingMigrationProcess was consuming a lot of time to process and it was making
an important use of the server's resources.

To avoid that, problem was splitted in more simple tasks, instead of insert all
records in an unique query transaction, process will be iterative, limiting in
each iteration the record's count to insert. This way the use of the resources is
balanced and more efficient.

Also was improved the updateLegacyCosts() method, increasing the value of i to
avoid flush and session clear in every iteration when legacy costs are being
processed.

---
M src/org/openbravo/costing/CostingMigrationProcess.java
M src/org/openbravo/costing/CostingUtils_data.xsql
---
(0096811)
hgbot (developer)
2017-05-25 16:18

 Repository: erp/devel/pi
Changeset: b622f00e5e44d819de43e68860bfd93872ff01e5
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Thu May 25 14:13:21 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/b622f00e5e44d819de43e68860bfd93872ff01e5 [^]

Related to issue 35959: Code review improvements

---
M src/org/openbravo/costing/CostingMigrationProcess.java
M src/org/openbravo/costing/CostingUtils_data.xsql
---
(0096812)
aferraz (manager)
2017-05-25 16:18

 Code review + Testing OK
(0096991)
hudsonbot (developer)
2017-05-29 17:43

 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/1ee70113bdc4 [^]
Maturity status: Test
(0096992)
hudsonbot (developer)
2017-05-29 17:43

 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/1ee70113bdc4 [^]
Maturity status: Test
(0098717)
hgbot (developer)
2017-08-30 10:10

 Repository: erp/backports/3.0PR17Q3
Changeset: 87bb93f75b522109909d9e12331638ee84be20f6
Author: Mark <markmm82 <at> gmail.com>
Date: Wed Aug 23 14:16:09 2017 -0400
URL: http://code.openbravo.com/erp/backports/3.0PR17Q3/rev/87bb93f75b522109909d9e12331638ee84be20f6 [^]

Fixes issue 36692: Transaction Cost not created for closing inventory lines

After cost was assigned to closing inventory lines transactions,
when calling to insertTrxCosts() from calculateCosts() method (second call),
CostingUtilsData.countTrxCosts(conn) was getting 0 in countTrx.
It was caused because closing inventory lines cost calculation
was done by OBDal connection, and methods invoked in CostingUtilsData are done
using different connection provided by SQLC, and the transactions are not available
at this moment. See: http://wiki.openbravo.com/wiki/Data_Access_Layer#Transaction_and_Session [^]

To find the best solution for this issue and don't affect the performance (see issue 35959),
different solutions were tested to always use DAL and make process use the same connection.

1.- Doing a single insert-select query with limits is not supported in DAL. We tried it in two different ways (see attached TestCosting_v1.diff):
- Use setMaxResults(). It only works for selects but not for inserts/updates.
- Use setMaxResults() in select query and append it to insert query using getQueryString(). It appends the select without any limit.

2.- Another option could be to refactor insertTrxCosts() method to do a multiple insert query:
We can create the select query with limits using setMaxResults(), iterate it using an scroll
and create and save in each iteration a new TransactionCost. Flush won't be done in every iteration.
When flush is done, it raises every insert in multiple single-row inserts instead of only one multiple-row insert.
Single-row inserts performs worst than multiple-row insert, at least with not many rows (see attached TestCosting_v2.diff).

3.- Finally, we have refactor the process to avoid insertTrxCosts() method:
Our solution makes an insertion in M_Transaction_Cost table each
time we make the updation of related transaction cost in M_Transaction table.
We will do single-row inserts in two methods: updateTrxLegacyCosts() and calculateCosts(),
and multiple-row insert in one method: updateWithZeroCostRemainingTrx().
Thus, we split the number of TransactionCost records to be created in three different steps.

---
M src/org/openbravo/costing/CostingMigrationProcess.java
M src/org/openbravo/costing/CostingUtils_data.xsql
---
(0098720)
hgbot (developer)
2017-08-30 10:13

 Repository: erp/devel/pi
Changeset: 5cd05d20d9cfdd4f380f7de4a2f9f4c24f554845
Author: Mark <markmm82 <at> gmail.com>
Date: Wed Aug 23 14:16:09 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/5cd05d20d9cfdd4f380f7de4a2f9f4c24f554845 [^]

Fixes issue 36675: Transaction Cost not created for closing inventory lines

After cost was assigned to closing inventory lines transactions,
when calling to insertTrxCosts() from calculateCosts() method (second call),
CostingUtilsData.countTrxCosts(conn) was getting 0 in countTrx.
It was caused because closing inventory lines cost calculation
was done by OBDal connection, and methods invoked in CostingUtilsData are done
using different connection provided by SQLC, and the transactions are not available
at this moment. See: http://wiki.openbravo.com/wiki/Data_Access_Layer#Transaction_and_Session [^]

To find the best solution for this issue and don't affect the performance (see issue 35959),
different solutions were tested to always use DAL and make process use the same connection.

1.- Doing a single insert-select query with limits is not supported in DAL. We tried it in two different ways (see attached TestCosting_v1.diff):
- Use setMaxResults(). It only works for selects but not for inserts/updates.
- Use setMaxResults() in select query and append it to insert query using getQueryString(). It appends the select without any limit.

2.- Another option could be to refactor insertTrxCosts() method to do a multiple insert query:
We can create the select query with limits using setMaxResults(), iterate it using an scroll
and create and save in each iteration a new TransactionCost. Flush won't be done in every iteration.
When flush is done, it raises every insert in multiple single-row inserts instead of only one multiple-row insert.
Single-row inserts performs worst than multiple-row insert, at least with not many rows (see attached TestCosting_v2.diff).

3.- Finally, we have refactor the process to avoid insertTrxCosts() method:
Our solution makes an insertion in M_Transaction_Cost table each
time we make the updation of related transaction cost in M_Transaction table.
We will do single-row inserts in two methods: updateTrxLegacyCosts() and calculateCosts(),
and multiple-row insert in one method: updateWithZeroCostRemainingTrx().
Thus, we split the number of TransactionCost records to be created in three different steps.

---
M src/org/openbravo/costing/CostingMigrationProcess.java
M src/org/openbravo/costing/CostingUtils_data.xsql
---
(0099344)
hudsonbot (developer)
2017-09-21 16:49

 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/9750b78d3e5c [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2017-05-09 11:51 ngarcia New Issue
2017-05-09 11:51 ngarcia Assigned To => Triage Finance
2017-05-09 11:51 ngarcia Modules => Core
2017-05-09 11:51 ngarcia Resolution time => 1496095200
2017-05-09 11:51 ngarcia Triggers an Emergency Pack => No
2017-05-09 11:54 ngarcia Issue Monitored: networkb
2017-05-09 12:30 aferraz Relationship added related to 0034130
2017-05-09 12:30 aferraz Relationship added related to 0032504
2017-05-09 12:31 aferraz Relationship added related to 0032023
2017-05-09 12:31 aferraz Relationship added related to 0032190
2017-05-09 12:32 aferraz Relationship added related to 0030451
2017-05-09 12:37 aferraz Relationship added related to 0024006
2017-05-09 13:35 aferraz Assigned To Triage Finance => markmm82
2017-05-09 13:42 aferraz Relationship added related to 0034564
2017-05-09 21:34 markmm82 Status new => scheduled
2017-05-25 16:18 hgbot Checkin
2017-05-25 16:18 hgbot Note Added: 0096810
2017-05-25 16:18 hgbot Status scheduled => resolved
2017-05-25 16:18 hgbot Resolution open => fixed
2017-05-25 16:18 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/0bfbdaa123e3639859bb030f17050942f90465a3 [^]
2017-05-25 16:18 hgbot Checkin
2017-05-25 16:18 hgbot Note Added: 0096811
2017-05-25 16:18 aferraz Review Assigned To => aferraz
2017-05-25 16:18 aferraz Note Added: 0096812
2017-05-25 16:18 aferraz Status resolved => closed
2017-05-25 16:18 aferraz Fixed in Version => 3.0PR17Q3
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096991
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096992
2017-08-22 13:18 aferraz Relationship added causes 0036675
2017-08-30 10:10 hgbot Checkin
2017-08-30 10:10 hgbot Note Added: 0098717
2017-08-30 10:13 hgbot Checkin
2017-08-30 10:13 hgbot Note Added: 0098720
2017-09-21 16:49 hudsonbot Checkin
2017-09-21 16:49 hudsonbot Note Added: 0099344

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker