Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0024104Openbravo ERPA. Platformpublic2013-06-18 08:312013-08-27 09:04
urgentmajorhave not tried
0024104: Unnecessary evaluation of computed columns
Computed 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.
Having big volume of Sales Orders (tested with 120K rows).

-Go to Sales Order window
-Remove all filters -> Request to Order datasource takes ~6s
Make 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.

duplicate of defect 00246483.0MP27 closed alostale Openbravo ERP Open/Close Period Control window is not working 
has duplicate defect 0025773 closed alostale Openbravo ERP Error thrown in web services if the request includes a field that references a computed column 
related to defect 0024516RMP26 closed guilleaer Retail Modules WebPOS relies on delivery status to check if a ticket is a layaway 
related to defect 0025862 closed alostale Openbravo ERP get_computedColumns() method returns null under some circumstances 
related to defect 0036624 closed platform Openbravo ERP Error thrown in web services if the URL includes a where parameter that references a computed column 
related to defect 0037424 closed alostale Openbravo ERP Export To Csv too slow with high volumes 
blocks defect 0024495 closed alostale Openbravo ERP Lazy computed columns API chage 
causes defect 0037363 closed alostale Openbravo ERP Linked item does not work on Organization window 
diff computedColumns.diff (33,741) 2013-06-18 13:09
diff computed-columns-mp21.1.diff (33,167) 2013-06-18 13:22
Issue History
2013-06-18 08:31alostaleNew Issue
2013-06-18 08:31alostaleAssigned To => alostale
2013-06-18 08:31alostaleModules => Core
2013-06-18 08:31alostaleTriggers an Emergency Pack => No
2013-06-18 08:32alostaleTag Attached: Performanceç+
2013-06-18 08:32alostaleTag Detached: Performanceç+
2013-06-18 08:32alostaleTag Attached: Performance
2013-06-18 13:06alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=4807#r4807
2013-06-18 13:06alostaleProposed Solution updated
2013-06-18 13:09alostaleFile Added: computedColumns.diff
2013-06-18 13:09alostaleNote Added: 0059484
2013-06-18 13:16alostaleNote Added: 0059486
2013-06-18 13:22alostaleFile Added: computed-columns-mp21.1.diff
2013-07-23 12:39AugustoMauchTarget Version3.0MP26 => 3.0MP27
2013-08-07 09:42alostaleRelationship addedblocks 0024495
2013-08-08 17:46alostaleRelationship addedrelated to 0024516
2013-08-09 07:42hgbotCheckin
2013-08-09 07:42hgbotNote Added: 0060548
2013-08-09 07:42hgbotStatusnew => resolved
2013-08-09 07:42hgbotResolutionopen => fixed
2013-08-09 07:42hgbotFixed in SCM revision => [^]
2013-08-09 15:13hgbotCheckin
2013-08-09 15:13hgbotNote Added: 0060557
2013-08-16 20:32hudsonbotCheckin
2013-08-16 20:32hudsonbotNote Added: 0060639
2013-08-16 20:33hudsonbotCheckin
2013-08-16 20:33hudsonbotNote Added: 0060641
2013-08-27 09:04AugustoMauchReview Assigned To => AugustoMauch
2013-08-27 09:04AugustoMauchNote Added: 0060778
2013-08-27 09:04AugustoMauchStatusresolved => closed
2013-08-27 09:04AugustoMauchFixed in Version => 3.0MP27
2013-09-02 10:36alostaleRelationship addedduplicate of 0024648
2014-03-03 09:02alostaleRelationship addedrelated to 0025862
2014-03-03 09:59alostaleRelationship addedhas duplicate 0025773
2017-08-09 14:04caristuRelationship addedrelated to 0036624
2017-11-22 16:09alostaleRelationship addedcauses 0037363
2017-12-01 13:29alostaleRelationship addedrelated to 0037424

2013-06-18 13:09   
Attached proposed solution
2013-06-18 13:16   
Testing proposed fix in Sales Order window with 120K rows is reduced from ~6s to ~1s.
2013-08-09 07:42   
Repository: erp/devel/pi
Changeset: 7d6d644b9b9e3b9ca55c7be357af7bb86a257263
Author: Asier Lostalé <asier.lostale <at>>
Date: Fri Jul 19 09:39:21 2013 +0200
URL: [^]

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/
M modules/org.openbravo.service.json/src/org/openbravo/service/json/
M modules/org.openbravo.service.json/src/org/openbravo/service/json/
M src-test/org/openbravo/test/
M src-test/org/openbravo/test/
M src-test/org/openbravo/test/
M src-test/org/openbravo/test/model/
M src/org/openbravo/base/gen/
M src/org/openbravo/base/gen/entity.ftl
M src/org/openbravo/base/model/
M src/org/openbravo/base/model/
M src/org/openbravo/base/model/
M src/org/openbravo/dal/core/
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/
M src/org/openbravo/dal/xml/
M src/org/openbravo/dal/xml/
M src/org/openbravo/service/db/
M src/org/openbravo/service/rest/
M src/org/openbravo/service/system/
A src-test/org/openbravo/test/dal/
A src/org/openbravo/base/gen/entityComputedColumns.ftl
2013-08-09 15:13   
Repository: erp/devel/pi
Changeset: 38dc65517c76d85dc28741ec4bf00756460bf0d5
Author: Asier Lostalé <asier.lostale <at>>
Date: Fri Aug 09 12:48:41 2013 +0200
URL: [^]

related to issue 24104: clean up

  Cleanup of small items

M src-test/org/openbravo/test/dal/
M src/org/openbravo/base/gen/entity.ftl
M src/org/openbravo/base/gen/entityComputedColumns.ftl
M src/org/openbravo/base/model/
M src/org/openbravo/dal/core/
M src/org/openbravo/dal/core/template_main.hbm.xml
M src/org/openbravo/dal/xml/
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: [^]

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

Maturity status: Test
2013-08-27 09:04   
Code reviewed and verified in pi@327ce5aac264