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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0024104
TypeCategorySeverityReproducibilityDate SubmittedLast Update
design defect[Openbravo ERP] A. Platformmajorhave not tried2013-06-18 08:312013-08-27 09:04
ReporteralostaleView Statuspublic 
Assigned Toalostale 
PriorityurgentResolutionfixedFixed in Version3.0MP27
StatusclosedFix in branchFixed in SCM revision7d6d644b9b9e
ProjectionnoneETAnoneTarget Version3.0MP27
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned ToAugustoMauch
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0024104: Unnecessary evaluation of computed columns

DescriptionComputed columns are internally evaluated as a subselect in the main entity's select.

When a tab with high volume of data is sorted by a column without index, and the applied filter is not too restrictive, computed columns are calculated (due to database planner) not only for the records that are in the grid's page but for all records matching the filtering. This is columns are computed after filtering but before limit. This results in a poor performance due to unnecessary database computations.

Additionally, in Java processes using DAL, these columns are calculated, at least for the retrieved records, even they are not going to be used.
Steps To ReproduceHaving big volume of Sales Orders (tested with 120K rows).

-Go to Sales Order window
-Remove all filters -> Request to Order datasource takes ~6s
Proposed SolutionMake computed columns calculation lazy:

When an entity has computed columns, an extra many-to-one property named _computedColumns is created in the hibernate mapping. This property points to a virtual entity named as the main one + _ComputedColumns. This virtual entity linked to the same table as the main entity contains only the properties defined as computed columns in the main one. These properties are no longer present in the main entity.

To make available without changing the API, computed columns are still accessible from the main entity generated class with the standard getter (though they are not using HQL), these getters, internally consume the proxy property.

NOTE, API CHANGE: In case computed columns are needed to be accessed from HQL, they are not directly available but through the _computedColumns property. As example, this HQL:
  from Order o where o.deliveredStatus = 100
should be rewritten to:
  from Order o where o._computedColumns.deliveredStatus = 100

When a row of the main entity is obtained using DAL, its _computedColumns entity works as a proxy for the computed columns. Only when that property is accessed, the computed columns are actually calculated (note in case the entity has more than one computed column, all of them are calculated at once).

In the case of the grid datasource, computed columns are calculated only for the rows in the page (defaulted to 100). This means, extra queries are required to get them, but Hibernate optimizes these queries not to execute it 100 times but grouping them in chunks (select computedCol from C_Order where C_Order_ID in (?,?,?,?,?,?);).

NOTE, PERFORMANCE POTENTIAL ISSUE: In grid it is still allowed to filter/sort by computed columns, to achieve this query builder has been modified to deal with this special case. When filtering or sorting by one of these columns, it cannot be avoided to calculate them restricted to the rest of the filtering, so in this case, performance problems can arise when having big volumes.

TagsPerformance
Attached Filesdiff file icon computedColumns.diff [^] (33,741 bytes) 2013-06-18 13:09 [Show Content]
diff file icon computed-columns-mp21.1.diff [^] (33,167 bytes) 2013-06-18 13:22 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]
duplicate of defect 00246483.0MP27 closedalostale Openbravo ERP Open/Close Period Control window is not working 
has duplicate defect 0025773 closedalostale Openbravo ERP Error thrown in web services if the request includes a field that references a computed column 
related to defect 0024516RMP26 closedguilleaer Retail Modules WebPOS relies on delivery status to check if a ticket is a layaway 
related to defect 0025862 closedalostale Openbravo ERP get_computedColumns() method returns null under some circumstances 
related to defect 0036624 closedplatform Openbravo ERP Error thrown in web services if the URL includes a where parameter that references a computed column 
related to defect 0037424 closedalostale Openbravo ERP Export To Csv too slow with high volumes 
blocks defect 0024495 closedalostale Openbravo ERP Lazy computed columns API chage 
causes defect 0037363 closedalostale Openbravo ERP Linked item does not work on Organization window 

-  Notes
(0059484)
alostale (developer)
2013-06-18 13:09

Attached proposed solution
(0059486)
alostale (developer)
2013-06-18 13:16

Testing proposed fix in Sales Order window with 120K rows is reduced from ~6s to ~1s.
(0060548)
hgbot (developer)
2013-08-09 07:42

Repository: erp/devel/pi
Changeset: 7d6d644b9b9e3b9ca55c7be357af7bb86a257263
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Jul 19 09:39:21 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/7d6d644b9b9e3b9ca55c7be357af7bb86a257263 [^]

fixed issue 24104: unnecessary evaluation of computed columns

  Computed columns are no longer directly evaluated when retriving DAL objects
  they are now lazily calculated through a proxy.

---
M modules/org.openbravo.service.datasource/src/org/openbravo/service/datasource/DefaultDataSourceService.java
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
M modules/org.openbravo.service.json/src/org/openbravo/service/json/JsonToDataConverter.java
M src-test/org/openbravo/test/AllAntTaskTests.java
M src-test/org/openbravo/test/AllTests.java
M src-test/org/openbravo/test/AntTaskTests.java
M src-test/org/openbravo/test/model/RuntimeModelTest.java
M src/org/openbravo/base/gen/GenerateEntitiesTask.java
M src/org/openbravo/base/gen/entity.ftl
M src/org/openbravo/base/model/Entity.java
M src/org/openbravo/base/model/ModelProvider.java
M src/org/openbravo/base/model/Property.java
M src/org/openbravo/dal/core/DalMappingGenerator.java
M src/org/openbravo/dal/core/template.hbm.xml
M src/org/openbravo/dal/core/template_main.hbm.xml
M src/org/openbravo/dal/xml/EntityXMLConverter.java
M src/org/openbravo/dal/xml/StaxXMLEntityConverter.java
M src/org/openbravo/dal/xml/XMLEntityConverter.java
M src/org/openbravo/service/db/DataImportService.java
M src/org/openbravo/service/rest/DalWebService.java
M src/org/openbravo/service/system/SystemService.java
A src-test/org/openbravo/test/dal/ComputedColumnsTest.java
A src/org/openbravo/base/gen/entityComputedColumns.ftl
---
(0060557)
hgbot (developer)
2013-08-09 15:13

Repository: erp/devel/pi
Changeset: 38dc65517c76d85dc28741ec4bf00756460bf0d5
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Aug 09 12:48:41 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/38dc65517c76d85dc28741ec4bf00756460bf0d5 [^]

related to issue 24104: clean up

  Cleanup of small items

---
M src-test/org/openbravo/test/dal/ComputedColumnsTest.java
M src/org/openbravo/base/gen/entity.ftl
M src/org/openbravo/base/gen/entityComputedColumns.ftl
M src/org/openbravo/base/model/Entity.java
M src/org/openbravo/dal/core/DalMappingGenerator.java
M src/org/openbravo/dal/core/template_main.hbm.xml
M src/org/openbravo/dal/xml/StaxXMLEntityConverter.java
---
(0060639)
hudsonbot (developer)
2013-08-16 20:32

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/59a1180e7f4f [^]

Maturity status: Test
(0060641)
hudsonbot (developer)
2013-08-16 20:33

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/59a1180e7f4f [^]

Maturity status: Test
(0060778)
AugustoMauch (developer)
2013-08-27 09:04

Code reviewed and verified in pi@327ce5aac264

- Issue History
Date Modified Username Field Change
2013-06-18 08:31 alostale New Issue
2013-06-18 08:31 alostale Assigned To => alostale
2013-06-18 08:31 alostale Modules => Core
2013-06-18 08:31 alostale Triggers an Emergency Pack => No
2013-06-18 08:32 alostale Tag Attached: Performanceç+
2013-06-18 08:32 alostale Tag Detached: Performanceç+
2013-06-18 08:32 alostale Tag Attached: Performance
2013-06-18 13:06 alostale Steps to Reproduce Updated View Revisions
2013-06-18 13:06 alostale Proposed Solution updated
2013-06-18 13:09 alostale File Added: computedColumns.diff
2013-06-18 13:09 alostale Note Added: 0059484
2013-06-18 13:16 alostale Note Added: 0059486
2013-06-18 13:22 alostale File Added: computed-columns-mp21.1.diff
2013-07-23 12:39 AugustoMauch Target Version 3.0MP26 => 3.0MP27
2013-08-07 09:42 alostale Relationship added blocks 0024495
2013-08-08 17:46 alostale Relationship added related to 0024516
2013-08-09 07:42 hgbot Checkin
2013-08-09 07:42 hgbot Note Added: 0060548
2013-08-09 07:42 hgbot Status new => resolved
2013-08-09 07:42 hgbot Resolution open => fixed
2013-08-09 07:42 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7d6d644b9b9e3b9ca55c7be357af7bb86a257263 [^]
2013-08-09 15:13 hgbot Checkin
2013-08-09 15:13 hgbot Note Added: 0060557
2013-08-16 20:32 hudsonbot Checkin
2013-08-16 20:32 hudsonbot Note Added: 0060639
2013-08-16 20:33 hudsonbot Checkin
2013-08-16 20:33 hudsonbot Note Added: 0060641
2013-08-27 09:04 AugustoMauch Review Assigned To => AugustoMauch
2013-08-27 09:04 AugustoMauch Note Added: 0060778
2013-08-27 09:04 AugustoMauch Status resolved => closed
2013-08-27 09:04 AugustoMauch Fixed in Version => 3.0MP27
2013-09-02 10:36 alostale Relationship added duplicate of 0024648
2014-03-03 09:02 alostale Relationship added related to 0025862
2014-03-03 09:59 alostale Relationship added has duplicate 0025773
2017-08-09 14:04 caristu Relationship added related to 0036624
2017-11-22 16:09 alostale Relationship added causes 0037363
2017-12-01 13:29 alostale Relationship added related to 0037424


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker