0038117: Performance problem in Warehouse Picking List when processing 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 (manager) 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:06	aferraz	Resolution time	=> 1522706400
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