Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0037357Openbravo ERPI. Performancepublic2017-11-22 12:122018-06-26 11:44
Reportergorkaion 
Assigned ToAtulOpenbravo 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q2 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0037357: Create Lines From popup on Invoice windows slow with high number of shipments
DescriptionThe Create Lines From popup is very slow when having a high number of Shipments. From both Purchase and Sales Invoice windows.

Note that issue 0037236 is already applied in the environment.
Steps To ReproduceOn a environment with a high volume of shipments. Around 2 million.

Create a new Purchase Invoice Header and open the Create Lines popup.

In the customer environment it takes minutes to load the popup.

It is also reproduced in the same button of Sales Invoice
Proposed SolutionReview the selectFromShipmentSOTrxCombo and selectFromShipment queries of CreateFrom_Invoice_data.xsql file.

We have reviewed that those are taking too much time.
Additional Information
TagsNo tags attached.
Relationships
related to defect 0039386 closed Triage Omni OMS Goods Shipment IsCompletelyInvoiced flag is not properly updated when voiding Sales Invoice 
related to defect 0049449 closed gsalas The "Fully Invoiced" check is not checked on some delivery notes. 
causes defect 0037996 closed AtulOpenbravo Sales Order with Immediate Invoice terms does not appear in Create Invoices From Order and Create Lines From process 
causes defect 0038510 closed dmiguelez Slow updating to 18Q2 on an environment with high loads of data 
Attached Filestxt QueryPlan.txt (61,989) 2018-02-14 05:00
https://issues.openbravo.com/file_download.php?file_id=11574&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-11-22 12:12gorkaionNew Issue
2017-11-22 12:12gorkaionAssigned To => platform
2017-11-22 12:12gorkaionModules => Core
2017-11-22 12:12gorkaionResolution time => 1513119600
2017-11-22 12:12gorkaionTriggers an Emergency Pack => No
2017-11-22 12:12gorkaionAssigned Toplatform => Triage Finance
2017-11-23 09:29vmromanosAssigned ToTriage Finance => AtulOpenbravo
2017-11-30 13:18AtulOpenbravoStatusnew => scheduled
2017-12-01 12:58AtulOpenbravoStatusscheduled => feedback
2017-12-20 08:07AtulOpenbravoStatusfeedback => scheduled
2018-01-15 10:35dmiguelezTypedefect => design defect
2018-02-14 04:59AtulOpenbravoNote Added: 0102383
2018-02-14 05:00AtulOpenbravoFile Added: QueryPlan.txt
2018-02-21 11:32hgbotCheckin
2018-02-21 11:32hgbotNote Added: 0102548
2018-02-21 11:32hgbotStatusscheduled => resolved
2018-02-21 11:32hgbotResolutionopen => fixed
2018-02-21 11:32hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/04e13cbe699011586c6681dd57733123666a2071 [^]
2018-02-21 12:00dmiguelezReview Assigned To => dmiguelez
2018-02-21 12:00dmiguelezStatusresolved => closed
2018-02-21 12:00dmiguelezFixed in Version => 3.0PR18Q2
2018-02-21 12:53hgbotCheckin
2018-02-21 12:53hgbotNote Added: 0102552
2018-02-22 09:10hgbotCheckin
2018-02-22 09:10hgbotNote Added: 0102574
2018-02-22 11:23hgbotCheckin
2018-02-22 11:23hgbotNote Added: 0102587
2018-02-22 11:26gorka_gilNote Added: 0102589
2018-02-22 11:27gorka_gilNote Edited: 0102589bug_revision_view_page.php?bugnote_id=0102589#r16759
2018-02-22 11:29gorka_gilNote Edited: 0102589bug_revision_view_page.php?bugnote_id=0102589#r16760
2018-02-22 18:19hudsonbotCheckin
2018-02-22 18:19hudsonbotNote Added: 0102784
2018-02-22 18:19hudsonbotCheckin
2018-02-22 18:19hudsonbotNote Added: 0102786
2018-02-22 18:19hudsonbotCheckin
2018-02-22 18:19hudsonbotNote Added: 0102792
2018-02-23 09:18AtulOpenbravoRelationship addedcauses 0037996
2018-02-27 12:59hgbotCheckin
2018-02-27 12:59hgbotNote Added: 0102851
2018-03-02 21:18hudsonbotCheckin
2018-03-02 21:18hudsonbotNote Added: 0102957
2018-05-09 16:36JONHMRelationship addedcauses 0038510
2018-05-09 16:50JONHMIssue Monitored: JONHM
2018-05-09 16:50JONHMIssue End Monitor: JONHM
2018-06-26 11:44hgbotCheckin
2018-06-26 11:44hgbotNote Added: 0105440
2018-06-26 14:03gorka_gilNote Deleted: 0105440
2018-10-02 08:59AtulOpenbravoRelationship addedrelated to 0039386
2022-06-14 13:45aferrazRelationship addedrelated to 0049449

Notes
(0102383)
AtulOpenbravo   
2018-02-14 04:59   
In customer environment,

Modulescript updated 2244037 shipments in 3919177 ms.

Sales flow:-

Sales Invoice Document No SCHISI6823, create lines from popup opens in
Run#1: 28 sec
Run#2: 9 sec
Run#3: 8 sec
Run#4: 8 sec

Purchase flow:-

Recreating index:

DROP INDEX m_matchpo_ship;

CREATE INDEX m_matchpo_ship
  ON m_matchpo
  USING btree
  (c_orderline_id COLLATE pg_catalog."default", m_inoutline_id COLLATE pg_catalog."default");

ANALYZE M_MATCHPO;

Purchase Invoice Document No ISPI108,create lines from popup opens in
Run#1: 3.57 sec
Run#2: 3.23 sec
Run#3: 2.83 sec
Run#4: 2.67 sec
(0102548)
hgbot   
2018-02-21 11:32   
Repository: erp/devel/pi
Changeset: 04e13cbe699011586c6681dd57733123666a2071
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Tue Feb 20 18:12:35 2018 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/04e13cbe699011586c6681dd57733123666a2071 [^]

Fixes Issue 37357:Create Lines From popup on Invoice windows slow
with high number of shipments

** Add iscompletelyinvoiced column in m_inout, add AD elements for it.
** Implement modulescript to update iscompletelyinvoiced flag for old
shipments.
** Define index for iscompletelyinvoiced flag on m_inout and make
use of flag in the where clause of the query.
** Modify M_InOut_Post and C_Invoice_Post db procedures to update
iscompletelyinvoiced flag properly.
** Iscompletelyinvoiced flag is used only in Sales Flow and shown in
Goods Shipment window.
** Add functional index OBEQUALS(QTYDELIVERED,QTYINVOICED) on c_orderline.
** Modify C_Invoice_Candidate_V to make use functional index.
** Add junit test cases to test iscompletelyinvoiced flag updation.

---
M src-db/database/model/functions/C_INVOICE_POST.xml
M src-db/database/model/functions/M_INOUT_POST.xml
M src-db/database/model/tables/C_ORDERLINE.xml
M src-db/database/model/tables/M_INOUT.xml
M src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_ELEMENT.xml
M src-db/database/sourcedata/AD_FIELD.xml
M src-test/src/org/openbravo/test/AllAntTaskTests.java
M src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIConstants.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIInvoiceHeaderParameters.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIInvoiceLineParameters.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIOrderHeaderParameters.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIOrderLineParameters.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIShipmentHeaderParameters.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIShipmentLineParameters.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIUtility.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/IsCompletelyInvoicedShipment.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/IsCompletelyInvoicedShipmentMultiLineTest.java
A src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/IsCompletelyInvoicedShipmentOneLineTest.java
A src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateIsCompletelyInvoiced.class
A src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateIsCompletelyInvoicedData.class
A src-util/modulescript/src/org/openbravo/modulescript/UpdateIsCompletelyInvoiced.java
A src-util/modulescript/src/org/openbravo/modulescript/UpdateIsCompletelyInvoiced_data.xsql
---
(0102552)
hgbot   
2018-02-21 12:53   
Repository: erp/devel/pi
Changeset: 5591d21a0627d2299c6a60fad1fa0ab553cb7175
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Wed Feb 21 12:53:07 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/5591d21a0627d2299c6a60fad1fa0ab553cb7175 [^]

Related to issue 37357. Exported database to fix the order
of the indexes in the orderline table

---
M src-db/database/model/tables/C_ORDERLINE.xml
---
(0102574)
hgbot   
2018-02-22 09:10   
Repository: erp/devel/pi
Changeset: 6b20e7da0de33d7247e09c566a056be6bc6f79ce
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Thu Feb 22 09:09:51 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/6b20e7da0de33d7247e09c566a056be6bc6f79ce [^]

Related to Issue 37357.

* Changed initialization of Longs. Instead of new Long("10") use 10L
  to avoid warnings in Java 9
* Use setAdminMode(true) instead of setAdminMode()
* Remove unnecessary flush
* Log correctly the exceptions and do not launch e.printStackTrace()

---
M src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIInvoiceLineParameters.java
M src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIOrderLineParameters.java
M src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/ICIUtility.java
M src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/IsCompletelyInvoicedShipmentMultiLineTest.java
M src-test/src/org/openbravo/test/materialMgmt/iscompletelyinvoicedshipment/IsCompletelyInvoicedShipmentOneLineTest.java
---
(0102587)
hgbot   
2018-02-22 11:23   
Repository: erp/pmods/org.openbravo.retail.sampledata
Changeset: 286d585b614ab590a1ea8793679719a664663a52
Author: Gorka Gil <gorka.gil <at> openbravo.com>
Date: Thu Feb 22 11:10:23 2018 +0100
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.sampledata/rev/286d585b614ab590a1ea8793679719a664663a52 [^]

Related to issue 37357: Fix retail pack sampledata dbconsistency

---
M referencedata/sampledata/The_White_Valley_Group/M_INOUT.xml
---
(0102589)
gorka_gil   
2018-02-22 11:26   
(edited on: 2018-02-22 11:29)
Repository: erp/pmods/org.openbravo.retail.sampledata
Changeset: 34b97501a0b8
Author: Gorka Gil <gorka.gil <at> openbravo.com>
Date: Thu Feb 22 11:22:18 2018 +0100
URL: http://code.openbravo.com/erp/pmods-branches/org.openbravo.test.mobile.sampledata/rev/34b97501a0b8 [^]

Related to issue 37357: Fix retail modules sampledata dbconsistency

---
M referencedata/sampledata/The_White_Valley_Group/M_INOUT.xml
---
(0102784)
hudsonbot   
2018-02-22 18:19   
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/980a6ad5bbf5 [^]
Maturity status: Test
(0102786)
hudsonbot   
2018-02-22 18:19   
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/980a6ad5bbf5 [^]
Maturity status: Test
(0102792)
hudsonbot   
2018-02-22 18:19   
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/980a6ad5bbf5 [^]
Maturity status: Test
(0102851)
hgbot   
2018-02-27 12:59   
Repository: erp/devel/pi
Changeset: 149e5d9ac93d9fd5e98a7aa99feb0a306dc06f59
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Tue Feb 27 12:59:41 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/149e5d9ac93d9fd5e98a7aa99feb0a306dc06f59 [^]

Related to issue 37357. Removed unnecessary index.

There is already a similar index, with the c_order_id included.
This index is not needed since the other one is used (orderlines are
also filtered by order)

---
M src-db/database/model/tables/C_ORDERLINE.xml
---
(0102957)
hudsonbot   
2018-03-02 21:18   
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/05dece3dc80b [^]
Maturity status: Test