Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0038510
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] I. Performancemajoralways2018-05-09 16:362018-05-23 17:12
ReporterJONHMView Statuspublic 
Assigned Todmiguelez 
PriorityurgentResolutionfixedFixed in Version3.0PR18Q3
StatusclosedFix in branchFixed in SCM revision7e3181b595b0
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Todmiguelez
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0038510: 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)) ) )
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 00386053.0PR18Q2.1 closeddmiguelez Slow updating to 18Q2 on an environment with high loads of data 
caused by design defect 0037357 closedAtulOpenbravo Create Lines From popup on Invoice windows slow with high number of shipments 
related to design defect 0039815 scheduledmarkmm82 Module script UpdateIsCompletelyInvoiced cannot be executed due to poor performance 

-  Notes
(0104442)
vmromanos (manager)
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 (manager)
2018-05-10 09:38

 Reopened as I see margin to improve
(0104550)
AtulOpenbravo (developer)
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 (developer)
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 (developer)
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 (developer)
2018-05-23 12:53

 Code Review + Testing Ok
(0104645)
dmiguelez (developer)
2018-05-23 13:10

 Reopened to create backport for PR18Q2.1
(0104660)
hudsonbot (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2018-05-09 16:36 JONHM New Issue
2018-05-09 16:36 JONHM Assigned To => platform
2018-05-09 16:36 JONHM Modules => Core
2018-05-09 16:36 JONHM Resolution time => 1527631200
2018-05-09 16:36 JONHM Triggers an Emergency Pack => No
2018-05-09 16:36 JONHM Relationship added caused by 0037357
2018-05-09 16:44 JONHM Assigned To platform => Triage Finance
2018-05-09 18:16 aferraz Assigned To Triage Finance => AtulOpenbravo
2018-05-10 09:17 vmromanos Review Assigned To => vmromanos
2018-05-10 09:17 vmromanos Note Added: 0104442
2018-05-10 09:17 vmromanos Status new => closed
2018-05-10 09:17 vmromanos Resolution open => invalid
2018-05-10 09:38 vmromanos Note Added: 0104444
2018-05-10 09:38 vmromanos Status closed => new
2018-05-10 09:38 vmromanos Resolution invalid => open
2018-05-10 22:52 AtulOpenbravo Status new => scheduled
2018-05-11 10:42 Practics Issue Monitored: Practics
2018-05-16 12:05 ngarcia Issue Monitored: ngarcia
2018-05-16 19:45 AtulOpenbravo Note Added: 0104550
2018-05-16 19:46 AtulOpenbravo Note Edited: 0104550 View Revisions
2018-05-23 12:52 hgbot Checkin
2018-05-23 12:52 hgbot Note Added: 0104641
2018-05-23 12:52 hgbot Status scheduled => resolved
2018-05-23 12:52 hgbot Resolution open => fixed
2018-05-23 12:52 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7e3181b595b0a65ddc7d04c42e41a40bbfb5cc0c [^]
2018-05-23 12:52 hgbot Checkin
2018-05-23 12:52 hgbot Note Added: 0104642
2018-05-23 12:53 dmiguelez Review Assigned To vmromanos => dmiguelez
2018-05-23 12:53 dmiguelez Note Added: 0104643
2018-05-23 12:53 dmiguelez Status resolved => closed
2018-05-23 12:53 dmiguelez Fixed in Version => 3.0PR18Q3
2018-05-23 13:06 dmiguelez Assigned To AtulOpenbravo => dmiguelez
2018-05-23 13:06 dmiguelez Status closed => new
2018-05-23 13:06 dmiguelez Resolution fixed => open
2018-05-23 13:06 dmiguelez Fixed in Version 3.0PR18Q3 =>
2018-05-23 13:07 dmiguelez Status new => scheduled
2018-05-23 13:07 dmiguelez Status scheduled => resolved
2018-05-23 13:07 dmiguelez Resolution open => fixed
2018-05-23 13:07 dmiguelez Status resolved => closed
2018-05-23 13:08 dmiguelez Fixed in Version => 3.0PR18Q3
2018-05-23 13:10 dmiguelez Note Added: 0104645
2018-05-23 13:10 dmiguelez Status closed => new
2018-05-23 13:10 dmiguelez Resolution fixed => open
2018-05-23 13:10 dmiguelez Fixed in Version 3.0PR18Q3 =>
2018-05-23 13:10 dmiguelez Status new => scheduled
2018-05-23 13:10 dmiguelez Status scheduled => resolved
2018-05-23 13:10 dmiguelez Fixed in Version => 3.0PR18Q3
2018-05-23 13:10 dmiguelez Resolution open => fixed
2018-05-23 13:10 dmiguelez Status resolved => closed
2018-05-23 17:12 hudsonbot Checkin
2018-05-23 17:12 hudsonbot Note Added: 0104660
2018-05-23 17:12 hudsonbot Checkin
2018-05-23 17:12 hudsonbot Note Added: 0104661
2018-12-17 12:23 adrianromero Relationship added related to 0039815

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker