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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0037424
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] I. Performancemajoralways2017-11-30 11:212018-01-03 12:39
ReportergorkaionView Statuspublic 
Assigned Toalostale 
PriorityurgentResolutionfixedFixed in Version3.0PR18Q1
StatusclosedFix in branchFixed in SCM revision1c94691c4704
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tocaristu
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0037424: Export To Csv too slow with high volumes

DescriptionOn an environment with a high volume of Sales Orders. When trying to export the grid as csv it takes too much time to generate the csv.
Steps To ReproduceOn an environment with a high amount of Sales Orders.

Go to sales order window and set the "count" summary function.

Filter the grid so around 5k orders are counted.

Click on Export to Spreadsheet button.

Note that it takes several seconds to download the file.
TagsPerformance
Attached Filespng file icon before-fix.png [^] (18,294 bytes) 2017-12-01 13:28


png file icon after-fix.png [^] (18,249 bytes) 2017-12-01 13:28

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 00282143.0PR15Q1 closedAugustoMauch The _selectedProperties parameter is not working on json rest webservices when not specifying a concrete record_id 
related to defect 0025461 closedalostale JSON WebServices don't support big volumes 
related to design defect 00241043.0MP27 closedalostale Unnecessary evaluation of computed columns 

-  Notes
(0100894)
alostale (manager)
2017-12-01 13:27
edited on: 2017-12-15 11:31

 Generated 20K orders executing twice this script [1].

Showing in grid the following fields: organization,documentNo,transactionDocument,orderDate,businessPartner,partnerAddress,grandTotalAmount,currency
Exporting all those sales orders (default filters, order by document number).

Took around 10s to export (from localhost, so no wire data transfer is taken into account), see attached before-fix image.

CSV file generation and data export starts as soon as first record is retrieved from db and converted to csv, so in this case it took 143ms to generate the query execute it, receive 1st field and process 1st record.

The rest 9.69secs are spent in fetching from DB and processing the rest of the records.

Even not all record properties are requested, they were processed to later discard them (even expensive computed columns and foreing keys), preventing those to be processed reduced time to 2.5secs.

Note in this case the biggest impact was caused because of computed columns (up to 70% of the total time) which were calculated even not needed. After the fix, the biggest improvement is only noticeable removing those fields from grid.

---
[1] https://github.com/alostale/ob-scripts/blob/master/sql/data-load/create-order-headers.sql [^]
(0100895)
hgbot (developer)
2017-12-01 13:45

 Repository: erp/devel/pi
Changeset: 1c94691c47047068d24fad438ecb83846ba14be3
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Dec 01 13:39:02 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/1c94691c47047068d24fad438ecb83846ba14be3 [^]

fixed bug 37424: export to CSV too slow with high volumes

  Wehn exporting to CSV from grid, all the properties of the entity were calculated
  even if they weren't included in the final file. This computation added overhead,
  specially for computed columns.

  Now only those properties that are going to be included in the CSV file are calulated.

---
M modules/org.openbravo.service.datasource/src/org/openbravo/service/datasource/DataSourceServlet.java
---
(0100896)
hgbot (developer)
2017-12-01 13:45

 Repository: erp/devel/pi
Changeset: abaa47f3a421c0fd59f7254bca8c0e232f6f86cd
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Dec 01 13:45:14 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/abaa47f3a421c0fd59f7254bca8c0e232f6f86cd [^]

related to bug 37424: export to CSV too slow with high volumes

  Small optimization: selectedProperties field is used to only check whether a
  propertie is selected. Use a HashSet that has an O(1) contains instead of
  ArrayList, which is O(n).

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/DataToJsonConverter.java
---
(0101510)
hudsonbot (developer)
2018-01-03 12:39

 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/c81e0d3cbab5 [^]
Maturity status: Test
(0101511)
hudsonbot (developer)
2018-01-03 12:39

 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/c81e0d3cbab5 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2017-11-30 11:21 gorkaion New Issue
2017-11-30 11:21 gorkaion Assigned To => platform
2017-11-30 11:21 gorkaion Modules => Core
2017-11-30 11:21 gorkaion Resolution time => 1513810800
2017-11-30 11:21 gorkaion Triggers an Emergency Pack => No
2017-11-30 14:50 Practics Issue Monitored: Practics
2017-12-01 12:54 alostale Tag Attached: Performance
2017-12-01 13:01 alostale Relationship added related to 0028214
2017-12-01 13:02 alostale Relationship added depends on 0025461
2017-12-01 13:02 alostale Relationship replaced related to 0025461
2017-12-01 13:27 alostale Note Added: 0100894
2017-12-01 13:28 alostale File Added: before-fix.png
2017-12-01 13:28 alostale File Added: after-fix.png
2017-12-01 13:29 alostale Relationship added related to 0024104
2017-12-01 13:31 alostale Assigned To platform => alostale
2017-12-01 13:45 hgbot Checkin
2017-12-01 13:45 hgbot Note Added: 0100895
2017-12-01 13:45 hgbot Status new => resolved
2017-12-01 13:45 hgbot Resolution open => fixed
2017-12-01 13:45 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/1c94691c47047068d24fad438ecb83846ba14be3 [^]
2017-12-01 13:45 hgbot Checkin
2017-12-01 13:45 hgbot Note Added: 0100896
2017-12-04 08:29 alostale Review Assigned To => caristu
2017-12-15 11:31 caristu Note Edited: 0100894 View Revisions
2017-12-15 12:00 caristu Status resolved => closed
2017-12-15 12:00 caristu Fixed in Version => 3.0PR18Q1
2018-01-03 12:39 hudsonbot Checkin
2018-01-03 12:39 hudsonbot Note Added: 0101510
2018-01-03 12:39 hudsonbot Checkin
2018-01-03 12:39 hudsonbot Note Added: 0101511

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker