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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0038117
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Modules] Warehouse Pickingmajoralways2018-03-13 13:312018-05-09 19:29
ReporterJONHMView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityhighResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revisiond35a17b83874
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Regression date
Regression introduced by commit
Regression level
Review Assigned Todmiguelez
Regression introduced in release
Summary

0038117: Performance problem in Warehouse Picking List when processing a picking with >100 lines

DescriptionPerformance problem in Warehouse Picking List when processing a picking with >100 lines
Steps To ReproduceAsk environment to support

- Open "Warehouse Picking List" window
- Select some picking in status 'draft' and which have >100 lines:
 - PL1023443
 - PL1023444
 - PL1023445
 - PL1023446
 - PL1023447
 - PL1023448

- Press 'Process' button

It takes 66864.939 ms to finish
Proposed SolutionSeems to be related with A_ASSET_CREATE function inside the loop of M_Inout_POST function. It takes about 1 minute to finish:
2018-03-13 12:21:16 enter loop
2018-03-13 12:22:11 exit loop
TagsNo tags attached.
Attached Fileslog file icon M_Inout_Post.log [^] (136,064 bytes) 2018-03-20 17:03

- Relationships Relation Graph ] Dependency Graph ]
related to defect 0038121 closedTriage Finance Performance problem in Warehouse Picking List when cancelling a picking with >100 lines 

-  Notes
(0103278)
vmromanos (manager)
2018-03-15 13:03

Hi Support,

Can you please answer the following 2 questions?

Which Openbravo Core version is running?
How many organizations does the system have?
(0103315)
aferraz (developer)
2018-03-16 12:47

Following commit might solve the problem: https://code.openbravo.com/erp/devel/pi/rev/8cb1e05233d4 [^]
(0103374)
JONHM (developer)
2018-03-20 17:01

The commit was applied and there is still a performance problem when processing the picking. Attached is a postgresql log set at NOTICE, see that every iteration on the loop takes about 4 seconds to finish.
Probably the problem would be on the A_Asset_Create function or the loop on which that function is included on M_Inout_Post function.
(0103555)
vmromanos (manager)
2018-03-27 10:04

Hi Support,

Does this instance have a custom extension point on M_INOUT_POST?
(0103679)
AtulOpenbravo (developer)
2018-04-04 19:52
edited on: 2018-04-04 19:58

In client environment,

1. Activate option in postgresql.conf: track_functions = all. Reload postgresql.
2. Run select pg_stat_reset(); in pgadmin to reset statistics.
3. Run Process Warehouse Picking List Single Record at once.
4. Run select * from pg_stat_user_functions; to get number of calls and the total_time and self_time.
5. Run select pg_stat_reset(); in pgadmin to reset statistics.
6. Run Process Warehouse Picking List Five Record at once.
7. Run select * from pg_stat_user_functions; to get number of calls and the total_time and self_time.

Before Change

Function/Trigger : m_transaction_trg2()

Process single warehouse picking list having 200 lines.

No of calls : 200
Total Time : 142684.894
Self Time : 142677.622

Process 5 warehouse picking list having 200 lines each.

No of calls : 1000
Total Time : 3137734.768
Self Time : 3137698.425

Proposed Change:- In m_transaction_trg2, fetching of pending reservedqty against released qty from m_reservation, m_reservation_stock and qtyonhand from m_storage_detail is done in the same query by joining high volume tables in client environment hence performance of query is poor. To improve the performance split the query into two to get same results.

After Change:-

Function/Trigger : m_transaction_trg2()

Process single warehouse picking list having 200 lines.

No of calls : 200
Total Time : 5690.695
Self Time : 5683.838

Process 5 warehouse picking list having 200 lines each.

No of calls : 1000
Total Time : 18482.246
Self Time : 18448.121

(0103696)
hgbot (developer)
2018-04-05 17:13

Repository: erp/devel/pi
Changeset: d35a17b838747532cf07e0470b6526bea0ec630c
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed Apr 04 22:56:13 2018 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/d35a17b838747532cf07e0470b6526bea0ec630c [^]

Fixes Issue 38117: Performance problem in Warehouse Picking List when
processing a picking with >100 lines

In m_transaction_trg2, split query into two.
A. Fetch pending reserved qty against released quantity
from m_reservation and m_reservation_stock
B. Fetch qtyonhand from m_storage_detail
In high volume environment, separate query improves
performance significantly.

---
M src-db/database/model/triggers/M_TRANSACTION_TRG2.xml
---
(0103697)
dmiguelez (developer)
2018-04-05 17:13

Solved in Core for PR18Q3
(0104365)
hudsonbot (developer)
2018-05-09 19:29

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

- Issue History
Date Modified Username Field Change
2018-03-13 13:31 JONHM New Issue
2018-03-13 13:31 JONHM Assigned To => Triage Finance
2018-03-13 13:31 JONHM Regression date => 2018-04-03
2018-03-13 14:57 JONHM Reproducibility have not tried => always
2018-03-13 17:06 aferraz Regression date 2018-04-03 =>
2018-03-13 17:13 JONHM Relationship added related to 0038121
2018-03-13 22:43 jfrances Issue Monitored: jfrances
2018-03-15 13:03 vmromanos Note Added: 0103278
2018-03-15 13:03 vmromanos Status new => feedback
2018-03-16 12:47 aferraz Note Added: 0103315
2018-03-16 12:48 aferraz Assigned To Triage Finance => JONHM
2018-03-20 17:01 JONHM Note Added: 0103374
2018-03-20 17:02 JONHM Assigned To JONHM => aferraz
2018-03-20 17:02 JONHM Status feedback => new
2018-03-20 17:03 JONHM File Added: M_Inout_Post.log
2018-03-20 17:13 JONHM Steps to Reproduce Updated View Revisions
2018-03-20 17:16 aferraz Assigned To aferraz => AtulOpenbravo
2018-03-26 19:17 AtulOpenbravo Status new => scheduled
2018-03-27 10:04 vmromanos Note Added: 0103555
2018-04-04 19:52 AtulOpenbravo Note Added: 0103679
2018-04-04 19:54 AtulOpenbravo Note Edited: 0103679 View Revisions
2018-04-04 19:54 AtulOpenbravo Note Edited: 0103679 View Revisions
2018-04-04 19:58 AtulOpenbravo Note Edited: 0103679 View Revisions
2018-04-05 17:13 hgbot Checkin
2018-04-05 17:13 hgbot Note Added: 0103696
2018-04-05 17:13 hgbot Status scheduled => resolved
2018-04-05 17:13 hgbot Resolution open => fixed
2018-04-05 17:13 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d35a17b838747532cf07e0470b6526bea0ec630c [^]
2018-04-05 17:13 dmiguelez Review Assigned To => dmiguelez
2018-04-05 17:13 dmiguelez Note Added: 0103697
2018-04-05 17:13 dmiguelez Status resolved => closed
2018-05-09 19:29 hudsonbot Checkin
2018-05-09 19:29 hudsonbot Note Added: 0104365


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker