Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0052072Openbravo ERPA. Platformpublic2023-04-05 14:402023-06-13 17:32
Reportermalsasua 
Assigned ToAugustoMauch 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR23Q3 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0052072: Heavy query is executed in Sales Order window
Descriptionwhen 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
Steps To Reproduce. 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
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
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 [^]