Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0037236Openbravo ERP03. Procurement managementpublic2017-11-06 17:272018-01-03 12:38
gorkaion 
dmiguelez 
urgentmajoralways
closedfixed 
5
 
3.0PR18Q1 
vmromanos
Core
No
0037236: Create Lines From popup on Purchase Invoice slow when having many Orders
On a environment with a high volume of Orders. Try to open the Create Lines From in Purchase Invoice window.

The popup can take up to 30 seconds to Open. Selecting a Shipment or Receipt the page is reloaded taking another 30 seconds.
Get an environment with a high volume of orders. ~1 million

Have a vendor with some open orders pending to invoice.

Try to open the Create Lines From window.
The issue is in the Query to load the combo of Purchase Orders.

selectFromPOCombo of CreateFromInvoice_data.xsql

This query is doing a Seq Scan on M_MatchPO. This can be avoided by an index on c_orderline_id similar to the existing one on m_inoutline_id

"m_matchpo_inout" btree (m_inoutline_id) WHERE m_inoutline_id IS NOT NULL

Consider if the new index can replace the "m_matchpo_ship" index.
No tags attached.
Issue History
2017-11-06 17:27gorkaionNew Issue
2017-11-06 17:27gorkaionAssigned To => Triage Finance
2017-11-06 17:27gorkaionModules => Core
2017-11-06 17:27gorkaionResolution time => 1511737200
2017-11-06 17:27gorkaionTriggers an Emergency Pack => No
2017-11-06 17:50dmiguelezAssigned ToTriage Finance => dmiguelez
2017-11-07 11:23dmiguelezNote Added: 0100301
2017-11-07 17:15hgbotCheckin
2017-11-07 17:15hgbotNote Added: 0100311
2017-11-07 17:15hgbotStatusnew => resolved
2017-11-07 17:15hgbotResolutionopen => fixed
2017-11-07 17:15hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/38268cab93c5aa3f60ea3bcfa812168eec308d44 [^]
2017-11-07 18:01vmromanosReview Assigned To => vmromanos
2017-11-07 18:01vmromanosNote Added: 0100312
2017-11-07 18:01vmromanosStatusresolved => closed
2017-11-08 10:39dmiguelezFixed in Version => 3.0PR18Q1
2018-01-03 12:38hudsonbotCheckin
2018-01-03 12:38hudsonbotNote Added: 0101378

Notes
(0100301)
dmiguelez   
2017-11-07 11:23   
Tested with client's data.

Without the patch took 30 seconds.
With the patch less than a second.
(0100311)
hgbot   
2017-11-07 17:15   
Repository: erp/devel/pi
Changeset: 38268cab93c5aa3f60ea3bcfa812168eec308d44
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Tue Nov 07 11:20:04 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/38268cab93c5aa3f60ea3bcfa812168eec308d44 [^]

Fixes Issue 37236. Modifies index over c_orderline and m_inoutline
to remove the clause "where m_inoutline_id is null"

By doing so, the c_orderline is fully indexed and improves performance.
There is no need to create a partial index for the c_orderline,
since this column is mandatory.

---
M src-db/database/model/tables/M_MATCHPO.xml
---
(0100312)
vmromanos   
2017-11-07 18:01   
Code review OK
(0101378)
hudsonbot   
2018-01-03 12:38   
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/c81e0d3cbab5 [^]
Maturity status: Test