Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0044281Openbravo ERPA. Platformpublic2020-06-04 11:062020-07-07 20:18
highmajorhave not tried
0044281: user window have bad performance because of HQL filter clause
The Window User have different query in the Filter clause and in the HQL filter clause.
The SQL clause check if the user is an employee or salesrepresentative doing a exists with c_bpartner window and joining with the c_bpart_ner_id of ad_user.

The HQL filter instead, makes the check with an "IN" and without joining c_bpartner_id of ad_user.

SQL query have a good performance, HQL query not.
In a high volume instance, open the window "user" and search a user filtering by any value.
Change the HQL query to have the same query as SQL clause

Attached diff file.
patch userWindow.patch (1,092) 2020-06-04 11:06
Issue History
2020-06-04 11:06ALopeteguiNew Issue
2020-06-04 11:06ALopeteguiAssigned To => platform
2020-06-04 11:06ALopeteguiFile Added: userWindow.patch
2020-06-04 11:06ALopeteguiModules => Core
2020-06-04 11:06ALopeteguiResolution time => 1592431200
2020-06-04 11:06ALopeteguiTriggers an Emergency Pack => No
2020-06-04 11:06ALopeteguiTag Attached: Performance
2020-06-12 11:22cbernerNote Added: 0120822
2020-06-12 11:22cbernerAssigned Toplatform => cberner
2020-06-12 11:22cbernerStatusnew => scheduled
2020-06-16 08:14hgbotCheckin
2020-06-16 08:14hgbotNote Added: 0120854
2020-06-16 08:14hgbotStatusscheduled => resolved
2020-06-16 08:14hgbotResolutionopen => fixed
2020-06-16 08:14hgbotFixed in SCM revision => [^]
2020-06-16 08:16cbernerReview Assigned To => alostale
2020-06-19 07:12alostaleStatusresolved => closed
2020-06-19 07:12alostaleFixed in Version => 3.0PR20Q3
2020-07-07 20:18eugeniIssue Monitored: eugeni

2020-06-12 11:22   
Merge Request: [^]
2020-06-16 08:14   
Repository: erp/devel/pi
Changeset: 462fb40027b9cc3ce723cfba74f3a95d51cf3c9e
Author: Cristian Berner <cristian.berner <at>>
Date: Fri Jun 12 11:17:35 2020 +0200
URL: [^]

Fixes ISSUE-44281: Use exists in hql filter clause of User window

HQL filter clause of User Window was previously using IN to filter from
BusinessPartner table, this resulted in a big hit in performance on
instances with a large number of BussinessPartners.

This has been fixed by using an exists and joining the table
BusinessPartner on the id being checked. It is similar to the SQL Filter

M src-db/database/sourcedata/AD_TAB.xml