Openbravo Issue Tracking System - Openbravo ERP |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0035959 | Openbravo ERP | 04. Warehouse management | public | 2017-05-09 11:51 | 2017-09-21 16:49 |
|
| Reporter | ngarcia | |
| Assigned To | markmm82 | |
| Priority | urgent | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | |
| Platform | | OS | 5 | OS Version | |
| Product Version | | |
| Target Version | | Fixed in Version | 3.0PR17Q3 | |
| Merge Request Status | |
| Review Assigned To | aferraz |
| OBNetwork customer | |
| Web browser | |
| Modules | Core |
| Support ticket | |
| Regression level | |
| Regression date | |
| Regression introduced in release | |
| Regression introduced by commit | |
| Triggers an Emergency Pack | No |
|
| Summary | 0035959: Performance problem in Costing Migration Process with high volume of records in m_transaction table |
| Description | Performance 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 Reproduce | As 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 |
| Proposed Solution | |
| Additional Information | |
| Tags | No tags attached. |
| Relationships | | related to | defect | 0034130 | | closed | aferraz | Costing Migration Process takes too long with big amount of records in m_transaction table | | related to | defect | 0032504 | 3.0PR16Q3 | closed | aferraz | Huge performance problem in the costing background process and using the Negative stock correction too | | related to | defect | 0032023 | 3.0PR16Q2 | closed | aferraz | CostingUtils.getCurrentValuedStock method consumes unneeded memory | | related to | defect | 0032190 | 3.0PR16Q2 | closed | aferraz | Performance problems on the PriceDifferenceBackground process | | related to | defect | 0030451 | | closed | aferraz | Performance Problems on the Costing Migration Process | | related to | defect | 0024006 | 3.0MP25 | closed | pramakrishnan | The Costing Migration Process should be improved to avoid performance problems. | | related to | defect | 0034564 | | closed | Sanjota | Duplicated records in m_transactioncost table after launching the Costing Migration Process | | causes | defect | 0036675 | | closed | markmm82 | [Costing Migration Process]: Transactions related with closing inventory lines do not have an entry in Transaction Cost tab |
|
| Attached Files | |
|
| 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 | |
|
Notes |
|
|
(0096810)
|
|
hgbot
|
|
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
|
|
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
---
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
(0098717)
|
|
hgbot
|
|
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
|
|
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
---
|
|
|
|
|
|