Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038605Openbravo ERPI. Performancepublic2018-05-09 16:362018-05-23 15:30
ReporterJONHM 
Assigned Todmiguelez 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR18Q2.1Fixed in Version3.0PR18Q2.1 
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
Summary0038605: Slow updating to 18Q2 on an environment with high loads of data
DescriptionSlow updating to 18Q2 on an environment with high loads of data. It seems to be related to UpdateIsCompletelyInvoiced modulescript, published on that same version.
Steps To ReproduceHaving an environment with high loads of data:
m_inout = 628114
m_inoutline = 2983732
c_invoice = 585358
c_invoiceline = 1547408

Update to 18Q2. The performance of this query will last for hours:

UPDATE M_InOut
        SET iscompletelyinvoiced = 'Y'
        WHERE issotrx = 'Y'
        AND NOT EXISTS (SELECT 1 FROM M_INOUTLINE l
                        LEFT JOIN c_invoiceline il ON l.m_inoutline_id = il.m_inoutline_id
                        LEFT JOIN c_invoice i ON il.c_invoice_id = i.c_invoice_id
                        WHERE l.M_INOUT_ID = M_InOut.M_INOUT_ID
                        GROUP BY l.m_inoutline_id, l.movementqty
                        HAVING ( l.movementqty >= 0 AND l.movementqty > Sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)) )
                             OR ( l.movementqty < 0 AND l.movementqty < Sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)) ) )
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks defect 0038510 closed dmiguelez Slow updating to 18Q2 on an environment with high loads of data 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-05-23 13:10dmiguelezTypedefect => backport
2018-05-23 13:10dmiguelezTarget Version => 3.0PR18Q2.1
2018-05-23 13:34hgbotCheckin
2018-05-23 13:34hgbotNote Added: 0104647
2018-05-23 13:34hgbotStatusscheduled => resolved
2018-05-23 13:34hgbotResolutionopen => fixed
2018-05-23 13:34hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/7e3181b595b0a65ddc7d04c42e41a40bbfb5cc0c [^] => http://code.openbravo.com/erp/backports/3.0PR18Q2.1/rev/9ac3c2f0a875663adc151dc7a39f0acac8c3bf0a [^]
2018-05-23 13:34hgbotCheckin
2018-05-23 13:34hgbotNote Added: 0104648
2018-05-23 13:34dmiguelezNote Added: 0104649
2018-05-23 13:34dmiguelezStatusresolved => closed
2018-05-23 13:34dmiguelezFixed in Version => 3.0PR18Q2.1
2018-05-23 15:30hgbotCheckin
2018-05-23 15:30hgbotNote Added: 0104653

Notes
(0104647)
hgbot   
2018-05-23 13:34   
Repository: erp/backports/3.0PR18Q2.1
Changeset: 9ac3c2f0a875663adc151dc7a39f0acac8c3bf0a
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed May 16 00:04:54 2018 +0530
URL: http://code.openbravo.com/erp/backports/3.0PR18Q2.1/rev/9ac3c2f0a875663adc151dc7a39f0acac8c3bf0a [^]

Fixes Issue 38605:Slow updating to 18Q2 on an environment
with high loads of data

In general no of shipment completely invoice are more as compare to
that are not completely invoiced. Hence mddulescript logic is changed
to update iscompletelyinvoiced as No for the applicable shipments.
For rest of the existing shipments flag is set as Yes using
onCreateDefault for iscompletelyinvoiced column of M_Inout table.

---
M src-db/database/model/tables/M_INOUT.xml
M src-util/modulescript/src/org/openbravo/modulescript/UpdateIsCompletelyInvoiced_data.xsql
---
(0104648)
hgbot   
2018-05-23 13:34   
Repository: erp/backports/3.0PR18Q2.1
Changeset: 36053cb67cf9f96b0faa4e521ae8f7ab687439fb
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed May 16 14:31:08 2018 +0530
URL: http://code.openbravo.com/erp/backports/3.0PR18Q2.1/rev/36053cb67cf9f96b0faa4e521ae8f7ab687439fb [^]

Related to Issue 38605:Sample data updated in QA testing for
IsCompletelyInvoiced flag in M_Inout

---
M referencedata/sampledata/QA_Testing/M_INOUT.xml
---
(0104649)
dmiguelez   
2018-05-23 13:34   
Code Review + Testing Ok
(0104653)
hgbot   
2018-05-23 15:30   
Repository: erp/backports/3.0PR18Q2.1
Changeset: 4dc6ea55b227497484c6278425c22bc53b38b9d9
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Wed May 23 15:30:28 2018 +0200
URL: http://code.openbravo.com/erp/backports/3.0PR18Q2.1/rev/4dc6ea55b227497484c6278425c22bc53b38b9d9 [^]

Related to Issue 38605.

Added missing .class file

---
M src-util/modulescript/build/classes/org/openbravo/modulescript/UpdateIsCompletelyInvoicedData.class
---