Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0016623Openbravo ERPI. Performancepublic2011-04-03 13:572011-07-14 18:44
Reportereintelau 
Assigned Toalostale 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0MP1Fixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesUser Interface Selector
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0016623: CustomQuerySelectorDatasource is very inefficient when filtering using a foreign key reference
DescriptionIf a selector has a field defined with a reference that is a foreign key reference (e.g. Table, TableDir, etc), then the query generated to filter the results by that field is extremely inefficient.

The generated query treats the id's as text fields and does a comparison using C_IGNORE_ACCENT(a) = C_IGNORE_ACCENT(b). When dealing with a large dataset can increase the query time by an order of magnitude or more.
Steps To Reproduce
Proposed SolutionAdd special case for references that are foreign key so that the generated filter
does a direct comparison of id with supplied value.
Additional Information
TagsNo tags attached.
Relationships
related to defect 00163733.0MP1 closed gorkaion Product selector & grid not usable when system has large number of products active 
related to feature request 0018283 closed dbaz 0016891: "Quick Launch" and "Create New" sensitivity Description: accents 
related to feature request 0018282 new dbaz "Quick Launch" and "Create New" sensitivity: logic when searching 
related to design defect 0018321 closed alostale On the new selectors the filtering is accent senstitive 
related to feature request 0019027 closed marvintm Filter capability should be case insensitive, ignore accents and other odd characters depending on the locale 
blocks defect 00166113.0RC7 closed mtaal Initial request done by UI selector element in form view does not use any filters or paging 
Attached Filespatch 16623.patch (1,645) 2011-04-03 13:59
https://issues.openbravo.com/file_download.php?file_id=3821&type=bug
patch 16623v2.patch (5,322) 2011-04-06 02:52
https://issues.openbravo.com/file_download.php?file_id=3838&type=bug
Issue History
Date ModifiedUsernameFieldChange
2011-04-03 13:57eintelauNew Issue
2011-04-03 13:57eintelauAssigned To => dbaz
2011-04-03 13:57eintelauModules => User Interface Selector
2011-04-03 13:59eintelauFile Added: 16623.patch
2011-04-04 10:56dbazAssigned Todbaz => mtaal
2011-04-05 14:07shuehnerCategoryB. User interface => I. Performance
2011-04-05 17:19shuehnerRelationship addedblocks 0016611
2011-04-06 02:49eintelauNote Added: 0035633
2011-04-06 02:52eintelauFile Added: 16623v2.patch
2011-04-11 10:29alostaleStatusnew => scheduled
2011-04-11 15:40mtaalAssigned Tomtaal => alostale
2011-05-18 22:22aldorisiojIssue Monitored: aldorisioj
2011-05-18 23:05aldorisiojNote Added: 0037183
2011-05-18 23:37jonalegriaesarteNote Added: 0037184
2011-05-19 08:27alostaleAssigned Toalostale => mtaal
2011-05-20 17:07iciordiaAssigned Tomtaal => alostale
2011-05-20 17:07iciordiaTarget Version => 3.0MP0
2011-05-20 17:09iciordiaNote Added: 0037288
2011-05-20 17:23alostaleRelationship addedduplicate of 0016891
2011-05-20 17:42alostaleRelationship replacedrelated to 0016891
2011-06-02 10:53dmitry_mezentsevTarget Version3.0MP0 => 3.0MP1
2011-06-17 13:14gorkaionRelationship addedblocks 0016373
2011-06-17 13:14gorkaionRelationship deletedblocks 0016373
2011-06-17 13:14gorkaionRelationship addedrelated to 0016373
2011-07-01 14:03hgbotCheckin
2011-07-01 14:03hgbotNote Added: 0038683
2011-07-01 14:03hgbotStatusscheduled => resolved
2011-07-01 14:03hgbotResolutionopen => fixed
2011-07-01 14:03hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/16ec818d352b85ad7e64eea9dd95a69383c62a28 [^]
2011-07-01 14:12hgbotCheckin
2011-07-01 14:12hgbotNote Added: 0038684
2011-07-06 02:42hudsonbotCheckin
2011-07-06 02:42hudsonbotNote Added: 0038852
2011-07-06 02:42hudsonbotCheckin
2011-07-06 02:42hudsonbotNote Added: 0038853
2011-07-14 18:44shuehnerNote Added: 0039071
2011-07-14 18:44shuehnerStatusresolved => closed
2011-08-16 13:21dbazRelationship addedrelated to 0018283
2011-08-16 13:21dbazRelationship addedrelated to 0018282
2011-08-19 12:56dbazRelationship addedrelated to 0018321
2011-11-09 16:50dbazRelationship addedrelated to 0019027

Notes
(0035633)
eintelau   
2011-04-06 02:49   
After some more investigation and tuning I have found some other parts of the query generation that are very inefficient.

1) Filtering for string domain types is done using the C_IGNORE_ACCENT on the column being filtered. This means that indexes, etc can't be used & the ignore accent function has to be evaluated for every row in the table. With a large dataset this leads to queries taking orders of magnitude longer (from < 0.5 sec to > 20 seconds on my test data). Even marking the ignore accent function as immutable rather than volatile doesn't make the query performant.

2) Filtering for ID columns is done as a string and uses the ignore accent / like clause. This should be done as a straight equality comparison.

Updated patch attached with fixes for these issues. The changes are
- Remove use of C_IGNORE_ACCENT and replace with lower (for case insensitive comparision)
- Add new domain type class (UniqueIdDomainType extends StringDomainType) for ID columns
- Update ID Reference to use new domain type
- When creating filter clause for UniqueIdDomainType use straight equality comparison instead of LIKE.
(0037183)
aldorisioj   
2011-05-18 23:05   
We have observed this issue here at SAIC as well. We're running MP24 on Oracle 11gR2. We determined that this problem is caused by the query performing a full table scan whenever searching for Products because the selector invoked the C_IGNORE_ACCENT function.

Rather than switching the function from C_IGNORE_ACCENT to LOWER or performing any other source code changes, we believe the most efficient way to resolve this issue is to create a function-based index for C_IGNORE_ACCENT. This would preserve the functionality provided by C_IGNORE_ACCENT but also address the performance concern.

To make this happen, C_IGNORE_ACCENT must first be made deterministic. Which may be accomplished by recompiling the stored procedure with that command as such:

CREATE OR REPLACE FUNCTION C_IGNORE_ACCENT(v_str IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
AS

BEGIN
  RETURN(TRANSLATE(upper(C_TRIM(v_str)), 'ÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜÑ#Ç', 'AAAAEEEEIIIIOOOOUUUUNNC'));
END C_IGNORE_ACCENT
;

You can then create the function-based index with the following command (in Oracle):

CREATE INDEX IDX_PROD_IGN_ACCENT_VALUE
    ON M_PRODUCT ( C_IGNORE_ACCENT (VALUE) );

A similar command is available for PostgreSQL.
(0037184)
jonalegriaesarte   
2011-05-18 23:37   
Reported by a professional subscriber too
(0037288)
iciordia   
2011-05-20 17:09   
Asier, it seems that the Business Partner selector is the only component in Openbravo 3 that uses c_ignore_accent. The fix to this issue is just don't use c_ignore_accent from this selector. Ismael
(0038683)
hgbot   
2011-07-01 14:03   
Repository: erp/devel/pi
Changeset: 16ec818d352b85ad7e64eea9dd95a69383c62a28
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Jul 01 14:03:32 2011 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/16ec818d352b85ad7e64eea9dd95a69383c62a28 [^]

fixed bug 16623: Don't use ignore accent in CustomQuerySelectorDatasource

---
M modules/org.openbravo.userinterface.selector/src/org/openbravo/userinterface/selector/CustomQuerySelectorDatasource.java
M src-db/database/sourcedata/AD_REFERENCE.xml
A src/org/openbravo/base/model/domaintype/UniqueIdDomainType.java
---
(0038684)
hgbot   
2011-07-01 14:12   
Repository: erp/devel/pi
Changeset: 11cdbe7be970b1ca7f31b3b8782082304fdca9db
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Jul 01 14:11:17 2011 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/11cdbe7be970b1ca7f31b3b8782082304fdca9db [^]

related to issue 16623: fixed file format

---
M src/org/openbravo/base/model/domaintype/UniqueIdDomainType.java
---
(0038852)
hudsonbot   
2011-07-06 02:42   
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/f1c481b1a146 [^]

Maturity status: Test
(0038853)
hudsonbot   
2011-07-06 02:42   
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/f1c481b1a146 [^]

Maturity status: Test
(0039071)
shuehner   
2011-07-14 18:44   
Reviewed/tested on pi/pgsql (rev: 5ba902b0d15e). String comparison now done using case-insensitive like without using the c_ignore_accent function at all. Also two new cases added for fk/id-references to use straight equality without the like overhead.