Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0037515Openbravo ERP09. Financial managementpublic2017-12-19 16:482018-02-22 18:18
ReporterJONHM 
Assigned ToAtulOpenbravo 
PriorityhighSeveritymajorReproducibilityalways
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
Summary0037515: Performance issue in Orders Awaiting Invoice Report
DescriptionThere is a performance issue in Orders Awaiting Invoice Report on environments with high amount of data.
Steps To ReproduceLogin on the environment provided by support:

- Open Orders Awaiting Invoice Report and set:
  * From date: 10-08-2017
  * To date: 12-08-2017

It take about 120 seconds to finish
Proposed Solutionhttps://code.openbravo.com/erp/devel/pi/file/tip/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql [^]

The c_order table could be excluded in this part of the query:

SELECT 1
FROM c_order C2, c_orderline CL
WHERE C2.c_order_id = C.c_order_id
AND c2.c_order_id = cl.c_order_id
AND cl.qtyordered <> cl.qtyinvoiced

Also, an index could be added for that condition:
cl.qtyordered <> cl.qtyinvoiced
Additional Information
TagsNo tags attached.
Relationships
related to defect 0037516 closed AtulOpenbravo Performance issue in Orders Awaiting Delivery Report 
Attached Files? query_plan (25,737) 2017-12-19 16:49
https://issues.openbravo.com/file_download.php?file_id=11372&type=bug
? query.sql (42,006) 2017-12-19 17:03
https://issues.openbravo.com/file_download.php?file_id=11373&type=bug
txt Final_37515_Query_QueryPlan_CTE_OBEQUALS_Index.txt (37,460) 2018-01-22 18:35
https://issues.openbravo.com/file_download.php?file_id=11476&type=bug
diff ClientEnvironment37515Fix.diff (21,338) 2018-01-22 19:12
https://issues.openbravo.com/file_download.php?file_id=11477&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-12-19 16:48JONHMNew Issue
2017-12-19 16:48JONHMAssigned To => Triage Finance
2017-12-19 16:48JONHMFile Added: query.sql
2017-12-19 16:48JONHMModules => Core
2017-12-19 16:48JONHMResolution time => 1515452400
2017-12-19 16:48JONHMTriggers an Emergency Pack => No
2017-12-19 16:49JONHMProposed Solution updated
2017-12-19 16:49JONHMFile Added: query_plan
2017-12-19 16:55JONHMSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16476#r16476
2017-12-19 16:58JONHMSummaryPerformance issue in Orders Awaiting Delivery Report => Performance issue in Orders Awaiting Invoice Report
2017-12-19 16:58JONHMDescription Updatedbug_revision_view_page.php?rev_id=16478#r16478
2017-12-19 16:58JONHMSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16479#r16479
2017-12-19 17:02JONHMFile Deleted: query.sql
2017-12-19 17:03JONHMFile Added: query.sql
2017-12-19 17:09JONHMNote Added: 0101139
2017-12-19 17:11aferrazRelationship addedrelated to 0037516
2017-12-19 17:53dmiguelezAssigned ToTriage Finance => AtulOpenbravo
2018-01-15 09:55AtulOpenbravoStatusnew => scheduled
2018-01-22 11:31hgbotCheckin
2018-01-22 11:31hgbotNote Added: 0101827
2018-01-22 11:31hgbotStatusscheduled => resolved
2018-01-22 11:31hgbotResolutionopen => fixed
2018-01-22 11:31hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/1f43ecba24a4849831199964da55739b71ee35d0 [^]
2018-01-22 11:37dmiguelezReview Assigned To => dmiguelez
2018-01-22 11:37dmiguelezNote Added: 0101829
2018-01-22 11:37dmiguelezStatusresolved => closed
2018-01-22 11:37dmiguelezFixed in Version => 3.0PR18Q2
2018-01-22 18:34AtulOpenbravoNote Added: 0101858
2018-01-22 18:35AtulOpenbravoFile Added: Final_37515_Query_QueryPlan_CTE_OBEQUALS_Index.txt
2018-01-22 18:36AtulOpenbravoNote Edited: 0101858bug_revision_view_page.php?bugnote_id=0101858#r16572
2018-01-22 19:12AtulOpenbravoFile Added: ClientEnvironment37515Fix.diff
2018-02-22 18:18hudsonbotCheckin
2018-02-22 18:18hudsonbotNote Added: 0102672

Notes
(0101139)
JONHM   
2017-12-19 17:09   
See file https://code.openbravo.com/erp/devel/pi/file/tip/modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql [^]
(0101827)
hgbot   
2018-01-22 11:31   
Repository: erp/devel/pi
Changeset: 1f43ecba24a4849831199964da55739b71ee35d0
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Mon Jan 22 00:18:10 2018 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/1f43ecba24a4849831199964da55739b71ee35d0 [^]

Fixes Issue 37515:Performance issue in Orders Awaiting Invoice Report

** Split the query using CTE for pending orders to be invoiced.
** Group by Taxes sub query on Order ID and Tax ID to void duplicates.
** Modify C_OrderLine_Order index by adding function based column
OBEQUALS(QTYORDERED,QTYINVOICED)
** Rearrange parameters in xsql accordingly and pass them while calling
select method in java class.
** Filters Orders to remove the ones in Draft and Closed Status.

---
M src-db/database/model/tables/C_ORDERLINE.xml
M src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoiceJR.java
M src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
---
(0101829)
dmiguelez   
2018-01-22 11:37   
Code Review + Testing Ok
(0101858)
AtulOpenbravo   
2018-01-22 18:34   
(edited on: 2018-01-22 18:36)
- Login on the environment provided by support:

- Open Orders Awaiting Invoice Report and set:
  * From date: 10-08-2017
  * To date: 13-09-2017

- Reports takes approximtaely 5 sec to show output. Earlier it took around 18 secs.

Time taken to fetch data for select method in java class is as below:-
Run#1:- ReportOrderNotInvoiceData.select takes 2768 ms.
Run#2:- ReportOrderNotInvoiceData.select takes 2563 ms.
Run#3:- ReportOrderNotInvoiceData.select takes 2475 ms.
(0102672)
hudsonbot   
2018-02-22 18: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/980a6ad5bbf5 [^]
Maturity status: Test