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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0037515
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajoralways2017-12-19 16:482018-02-22 18:18
ReporterJONHMView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityhighResolutionfixedFixed in Version3.0PR18Q2
StatusclosedFix in branchFixed in SCM revision1f43ecba24a4
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

0037515: 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
TagsNo tags attached.
Attached Files file icon query_plan [^] (25,737 bytes) 2017-12-19 16:49 [Show Content]
? file icon query.sql [^] (42,006 bytes) 2017-12-19 17:03
txt file icon Final_37515_Query_QueryPlan_CTE_OBEQUALS_Index.txt [^] (37,460 bytes) 2018-01-22 18:35 [Show Content]
diff file icon ClientEnvironment37515Fix.diff [^] (21,338 bytes) 2018-01-22 19:12 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]
related to defect 0037516 closedAtulOpenbravo Performance issue in Orders Awaiting Delivery Report 

-  Notes
(0101139)
JONHM (developer)
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 (developer)
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 (developer)
2018-01-22 11:37

Code Review + Testing Ok
(0101858)
AtulOpenbravo (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2017-12-19 16:48 JONHM New Issue
2017-12-19 16:48 JONHM Assigned To => Triage Finance
2017-12-19 16:48 JONHM File Added: query.sql
2017-12-19 16:48 JONHM Modules => Core
2017-12-19 16:48 JONHM Triggers an Emergency Pack => No
2017-12-19 16:49 JONHM Proposed Solution updated
2017-12-19 16:49 JONHM File Added: query_plan
2017-12-19 16:55 JONHM Steps to Reproduce Updated View Revisions
2017-12-19 16:58 JONHM Summary Performance issue in Orders Awaiting Delivery Report => Performance issue in Orders Awaiting Invoice Report
2017-12-19 16:58 JONHM Description Updated View Revisions
2017-12-19 16:58 JONHM Steps to Reproduce Updated View Revisions
2017-12-19 17:02 JONHM File Deleted: query.sql
2017-12-19 17:03 JONHM File Added: query.sql
2017-12-19 17:09 JONHM Note Added: 0101139
2017-12-19 17:11 aferraz Relationship added related to 0037516
2017-12-19 17:53 dmiguelez Assigned To Triage Finance => AtulOpenbravo
2018-01-15 09:55 AtulOpenbravo Status new => scheduled
2018-01-22 11:31 hgbot Checkin
2018-01-22 11:31 hgbot Note Added: 0101827
2018-01-22 11:31 hgbot Status scheduled => resolved
2018-01-22 11:31 hgbot Resolution open => fixed
2018-01-22 11:31 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/1f43ecba24a4849831199964da55739b71ee35d0 [^]
2018-01-22 11:37 dmiguelez Review Assigned To => dmiguelez
2018-01-22 11:37 dmiguelez Note Added: 0101829
2018-01-22 11:37 dmiguelez Status resolved => closed
2018-01-22 11:37 dmiguelez Fixed in Version => 3.0PR18Q2
2018-01-22 18:34 AtulOpenbravo Note Added: 0101858
2018-01-22 18:35 AtulOpenbravo File Added: Final_37515_Query_QueryPlan_CTE_OBEQUALS_Index.txt
2018-01-22 18:36 AtulOpenbravo Note Edited: 0101858 View Revisions
2018-01-22 19:12 AtulOpenbravo File Added: ClientEnvironment37515Fix.diff
2018-02-22 18:18 hudsonbot Checkin
2018-02-22 18:18 hudsonbot Note Added: 0102672


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker