Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038510Openbravo ERPI. Performancepublic2018-05-09 16:362018-05-23 17:12
ReporterJONHM 
Assigned Todmiguelez 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q3 
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
Summary0038510: 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
depends on backport 00386053.0PR18Q2.1 closed dmiguelez Slow updating to 18Q2 on an environment with high loads of data 
caused by design defect 0037357 closed AtulOpenbravo Create Lines From popup on Invoice windows slow with high number of shipments 
related to design defect 0039815 scheduled markmm82 Module script UpdateIsCompletelyInvoiced cannot be executed due to poor performance 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-05-09 16:36JONHMNew Issue
2018-05-09 16:36JONHMAssigned To => platform
2018-05-09 16:36JONHMModules => Core
2018-05-09 16:36JONHMResolution time => 1527631200
2018-05-09 16:36JONHMTriggers an Emergency Pack => No
2018-05-09 16:36JONHMRelationship addedcaused by 0037357
2018-05-09 16:44JONHMAssigned Toplatform => Triage Finance
2018-05-09 18:16aferrazAssigned ToTriage Finance => AtulOpenbravo
2018-05-10 09:17vmromanosReview Assigned To => vmromanos
2018-05-10 09:17vmromanosNote Added: 0104442
2018-05-10 09:17vmromanosStatusnew => closed
2018-05-10 09:17vmromanosResolutionopen => invalid
2018-05-10 09:38vmromanosNote Added: 0104444
2018-05-10 09:38vmromanosStatusclosed => new
2018-05-10 09:38vmromanosResolutioninvalid => open
2018-05-10 22:52AtulOpenbravoStatusnew => scheduled
2018-05-11 10:42PracticsIssue Monitored: Practics
2018-05-16 12:05ngarciaIssue Monitored: ngarcia
2018-05-16 19:45AtulOpenbravoNote Added: 0104550
2018-05-16 19:46AtulOpenbravoNote Edited: 0104550bug_revision_view_page.php?bugnote_id=0104550#r17151
2018-05-23 12:52hgbotCheckin
2018-05-23 12:52hgbotNote Added: 0104641
2018-05-23 12:52hgbotStatusscheduled => resolved
2018-05-23 12:52hgbotResolutionopen => fixed
2018-05-23 12:52hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7e3181b595b0a65ddc7d04c42e41a40bbfb5cc0c [^]
2018-05-23 12:52hgbotCheckin
2018-05-23 12:52hgbotNote Added: 0104642
2018-05-23 12:53dmiguelezReview Assigned Tovmromanos => dmiguelez
2018-05-23 12:53dmiguelezNote Added: 0104643
2018-05-23 12:53dmiguelezStatusresolved => closed
2018-05-23 12:53dmiguelezFixed in Version => 3.0PR18Q3
2018-05-23 13:06dmiguelezAssigned ToAtulOpenbravo => dmiguelez
2018-05-23 13:06dmiguelezStatusclosed => new
2018-05-23 13:06dmiguelezResolutionfixed => open
2018-05-23 13:06dmiguelezFixed in Version3.0PR18Q3 =>
2018-05-23 13:07dmiguelezStatusnew => scheduled
2018-05-23 13:07dmiguelezStatusscheduled => resolved
2018-05-23 13:07dmiguelezResolutionopen => fixed
2018-05-23 13:07dmiguelezStatusresolved => closed
2018-05-23 13:08dmiguelezFixed in Version => 3.0PR18Q3
2018-05-23 13:10dmiguelezNote Added: 0104645
2018-05-23 13:10dmiguelezStatusclosed => new
2018-05-23 13:10dmiguelezResolutionfixed => open
2018-05-23 13:10dmiguelezFixed in Version3.0PR18Q3 =>
2018-05-23 13:10dmiguelezStatusnew => scheduled
2018-05-23 13:10dmiguelezStatusscheduled => resolved
2018-05-23 13:10dmiguelezFixed in Version => 3.0PR18Q3
2018-05-23 13:10dmiguelezResolutionopen => fixed
2018-05-23 13:10dmiguelezStatusresolved => closed
2018-05-23 17:12hudsonbotCheckin
2018-05-23 17:12hudsonbotNote Added: 0104660
2018-05-23 17:12hudsonbotCheckin
2018-05-23 17:12hudsonbotNote Added: 0104661
2018-12-17 12:23adrianromeroRelationship addedrelated to 0039815

Notes
(0104442)
vmromanos   
2018-05-10 09:17   
This module script is slow because it must execute a complex query that requires several sequencial scans on high volume tables.
That's why a log was added to the compilation process informing about this:
https://code.openbravo.com/erp/devel/pi/file/tip/src-util/modulescript/src/org/openbravo/modulescript/UpdateIsCompletelyInvoiced.java#l40 [^]

As a workaround ONLY if you can't wait several hours, you can delete the module script, update your system AND [MUST BE DONE] execute these two queries BEFORE working with any goods receipt/shipment or creating an invoice from a goods receipt/shipment:

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)) ) );

 INSERT INTO ad_preference (
           ad_preference_id, ad_client_id, ad_org_id, isactive,
           createdby, created, updatedby, updated, attribute
           ) VALUES (
           get_uuid(), '0', '0', 'Y',
           '0', NOW(), '0', NOW(), 'UpdateShipmentIsCompletelyInvoiced');


IMPORTANT: this is a workaround to be executed under your own responsibility. It's highly recommended to wait till the module script finishes.
(0104444)
vmromanos   
2018-05-10 09:38   
Reopened as I see margin to improve
(0104550)
AtulOpenbravo   
2018-05-16 19:45   
(edited on: 2018-05-16 19:46)
Previously modulescript used to set IsCompletelyInvoiced = Yes for the applicable records and by default flag is set IsCompletelyInvoiced = No. As the no of records being updated with this approach are more the time taken is more to execute the update query. Hence solution is implemented exactly in the reverse way. Modulescript now updates applicable records with IsCompletelyInvoiced = No. Using OnCreateDefault property of column, for existing records in M_Inout flag is set as Yes. However when a new shipment is created, default value of column sets it to No.


In client environment, modulescript updated 12229 shipments in 1295382 ms (approx. 21.5 mins)
(0104641)
hgbot   
2018-05-23 12:52   
Repository: erp/devel/pi
Changeset: 7e3181b595b0a65ddc7d04c42e41a40bbfb5cc0c
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed May 16 00:04:54 2018 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/7e3181b595b0a65ddc7d04c42e41a40bbfb5cc0c [^]

Fixes Issue 38510: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/build/classes/org/openbravo/modulescript/UpdateIsCompletelyInvoicedData.class
M src-util/modulescript/src/org/openbravo/modulescript/UpdateIsCompletelyInvoiced_data.xsql
---
(0104642)
hgbot   
2018-05-23 12:52   
Repository: erp/devel/pi
Changeset: 07860e9e9adc4365589dc7b2feffa91c7ad9cd21
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed May 16 14:31:08 2018 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/07860e9e9adc4365589dc7b2feffa91c7ad9cd21 [^]

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

---
M referencedata/sampledata/QA_Testing/M_INOUT.xml
---
(0104643)
dmiguelez   
2018-05-23 12:53   
Code Review + Testing Ok
(0104645)
dmiguelez   
2018-05-23 13:10   
Reopened to create backport for PR18Q2.1
(0104660)
hudsonbot   
2018-05-23 17:12   
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/e2e136f3822e [^]
Maturity status: Test
(0104661)
hudsonbot   
2018-05-23 17:12   
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/e2e136f3822e [^]
Maturity status: Test