0024104: Unnecessary evaluation of computed columns

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0024104

Openbravo ERP

A. Platform

public

2013-06-18 08:31

2013-08-27 09:04

Reporter

alostale

Assigned To

alostale

Priority

urgent

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

3.0MP27

Fixed in Version

3.0MP27

Merge Request Status

Review Assigned To

AugustoMauch

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0024104: Unnecessary evaluation of computed columns

Description

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.

Steps To Reproduce

Having big volume of Sales Orders (tested with 120K rows).

-Go to Sales Order window
-Remove all filters -> Request to Order datasource takes ~6s

Proposed Solution

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.

Additional Information

Tags

Performance

Relationships

duplicate of	defect	0024648	3.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	0024516	RMP26	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

Attached Files

computedColumns.diff (33,741) 2013-06-18 13:09
https://issues.openbravo.com/file_download.php?file_id=6202&type=bug
diff

computed-columns-mp21.1.diff (33,167) 2013-06-18 13:22
https://issues.openbravo.com/file_download.php?file_id=6203&type=bug

Issue History

Date Modified

Username

Field

Change

2013-06-18 08:31