Openbravo Issue Tracking System - Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038117ModulesWarehouse Pickingpublic2018-03-13 13:312018-05-09 19:29
ReporterJONHM 
Assigned ToAtulOpenbravo 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Regression date
Regression introduced by commit
Regression level
Review Assigned Todmiguelez
Support ticket472
OBNetwork customerOBPS
Regression introduced in release
Summary0038117: 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
Additional Information
TagsNo tags attached.
Relationships
related to defect 0038121 closed Triage Omni WMS Performance problem in Warehouse Picking List when cancelling a picking with >100 lines 
Attached Fileslog M_Inout_Post.log (136,064) 2018-03-20 17:03
https://issues.openbravo.com/file_download.php?file_id=11662&type=bug
Issue History
Date ModifiedUsernameFieldChange
2018-03-13 13:31JONHMNew Issue
2018-03-13 13:31JONHMAssigned To => Triage Finance
2018-03-13 13:31JONHMRegression date => 2018-04-03
2018-03-13 13:31JONHMSupport ticket => 472
2018-03-13 13:31JONHMOBNetwork customer => Yes
2018-03-13 14:57JONHMReproducibilityhave not tried => always
2018-03-13 17:06aferrazRegression date2018-04-03 =>
2018-03-13 17:06aferrazResolution time => 1522706400
2018-03-13 17:13JONHMRelationship addedrelated to 0038121
2018-03-13 22:43jfrancesIssue Monitored: jfrances
2018-03-15 13:03vmromanosNote Added: 0103278
2018-03-15 13:03vmromanosStatusnew => feedback
2018-03-16 12:47aferrazNote Added: 0103315
2018-03-16 12:48aferrazAssigned ToTriage Finance => JONHM
2018-03-20 17:01JONHMNote Added: 0103374
2018-03-20 17:02JONHMAssigned ToJONHM => aferraz
2018-03-20 17:02JONHMStatusfeedback => new
2018-03-20 17:03JONHMFile Added: M_Inout_Post.log
2018-03-20 17:13JONHMSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16894#r16894
2018-03-20 17:16aferrazAssigned Toaferraz => AtulOpenbravo
2018-03-26 19:17AtulOpenbravoStatusnew => scheduled
2018-03-27 10:04vmromanosNote Added: 0103555
2018-04-04 19:52AtulOpenbravoNote Added: 0103679
2018-04-04 19:54AtulOpenbravoNote Edited: 0103679bug_revision_view_page.php?bugnote_id=0103679#r16952
2018-04-04 19:54AtulOpenbravoNote Edited: 0103679bug_revision_view_page.php?bugnote_id=0103679#r16953
2018-04-04 19:58AtulOpenbravoNote Edited: 0103679bug_revision_view_page.php?bugnote_id=0103679#r16954
2018-04-05 17:13hgbotCheckin
2018-04-05 17:13hgbotNote Added: 0103696
2018-04-05 17:13hgbotStatusscheduled => resolved
2018-04-05 17:13hgbotResolutionopen => fixed
2018-04-05 17:13hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d35a17b838747532cf07e0470b6526bea0ec630c [^]
2018-04-05 17:13dmiguelezReview Assigned To => dmiguelez
2018-04-05 17:13dmiguelezNote Added: 0103697
2018-04-05 17:13dmiguelezStatusresolved => closed
2018-05-09 19:29hudsonbotCheckin
2018-05-09 19:29hudsonbotNote Added: 0104365

Notes
(0103278)
vmromanos   
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   
2018-03-16 12:47   
Following commit might solve the problem: https://code.openbravo.com/erp/devel/pi/rev/8cb1e05233d4 [^]
(0103374)
JONHM   
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   
2018-03-27 10:04   
Hi Support,

Does this instance have a custom extension point on M_INOUT_POST?
(0103679)
AtulOpenbravo   
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   
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   
2018-04-05 17:13   
Solved in Core for PR18Q3
(0104365)
hudsonbot   
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