Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0052072Openbravo ERPA. Platformpublic2023-04-05 14:402023-06-13 17:32
malsasua 
AugustoMauch 
highmajoralways
closedfixed 
5
 
PR23Q3 
Core
No
0052072: Heavy query is executed in Sales Order window
when the access is done to one record in the Sales Order window, if the record contains one id in the column cancelledorder_id but does not exist a c_order with this ID, it is executed this query:

SELECT td0.C_Order_ID AS ID,
 COALESCE(TO_CHAR(COALESCE(TO_CHAR(td0.DocumentNo),'')),'')) AS NAME, '' AS DESCRIPTION
FROM C_Order td0
WHERE (td0.isActive = 'Y' OR td0.C_Order_ID = ($1) )
AND td0.C_Order_ID = $2

with big data, this query takes several hours
. connect to database
. in one c_order record, set the cancelledorder_id to one ID that it does not exists in c_order table. In my example, Im updating the column to '12341234123412341234123412341234' for the c_order with id "B6E14AC1552B4F8FA03B74851DAAE71C":

update c_order set cancelledorder_id='12341234123412341234123412341234' where c_order_id='B6E14AC1552B4F8FA03B74851DAAE71C';

. configure the log in postgres to print all queries
. go to Sales Order window for the previous record updated:
http://localhost:8080/openbravo/?tabId=186&recordId=B6E14AC1552B4F8FA03B74851DAAE71C [^]

the query is displayed in the log
No tags attached.
Issue History
2023-04-05 14:40malsasuaNew Issue
2023-04-05 14:40malsasuaAssigned To => Triage Platform Base
2023-04-05 14:40malsasuaModules => Core
2023-04-05 14:40malsasuaTriggers an Emergency Pack => No
2023-04-05 15:06gorkaionIssue Monitored: gorkaion
2023-05-07 23:52hgbotNote Added: 0149398
2023-05-15 00:19AugustoMauchNote Added: 0149666
2023-05-29 12:27hgbotNote Added: 0150407
2023-05-29 15:54hgbotNote Added: 0150433
2023-05-29 16:16AugustoMauchNote Deleted: 0149398
2023-05-29 16:16AugustoMauchNote Deleted: 0149666
2023-05-29 16:16AugustoMauchNote Deleted: 0150407
2023-06-13 17:29AugustoMauchAssigned ToTriage Platform Base => AugustoMauch
2023-06-13 17:30AugustoMauchStatusnew => scheduled
2023-06-13 17:32hgbotResolutionopen => fixed
2023-06-13 17:32hgbotStatusscheduled => closed
2023-06-13 17:32hgbotFixed in Version => PR23Q3
2023-06-13 17:32hgbotNote Added: 0151125
2023-06-13 17:32hgbotNote Added: 0151126

Notes
(0150433)
hgbot   
2023-05-29 15:54   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/896 [^]
(0151125)
hgbot   
2023-06-13 17:32   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: 526a4c13798c465a101769aa895a7faa28919d8d
Author: Augusto Mauch <augusto.mauch@openbravo.com>
Date: 13-06-2023 09:43:09
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/526a4c13798c465a101769aa895a7faa28919d8d [^]

Fixes ISSUE-52072: Updates reference of c_order.Cancelledorder_id from table to selector to improve performance

The use of the previous reference was resulting in very slow queries that could take hours to complete, such us:

SELECT td0.C_Order_ID AS ID, ...
        FROM C_Order td0
        WHERE ...
        AND (td0.isActive = Y OR td0.C_Order_ID = ($1) )

The new reference used does not generate those potentially slow queries

---
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_REFERENCE.xml
M src-db/database/sourcedata/OBUISEL_SELECTOR.xml
M src-db/database/sourcedata/OBUISEL_SELECTOR_FIELD.xml
---
(0151126)
hgbot   
2023-06-13 17:32   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/896 [^]