0047907: Performance issue when searching in window "Financial Account" report "CashBook"

Description

For some stores, in backoffice, window "Financial Account", if we search for report "CashBook", result returns 0 lines. In Chrome Developer we have error status 502.
There are 2 calculated colums that causes slowness: "lastreconciliation" and "lastreconbalance". These columns are calculated using this query:

SELECT fin_financ0_.Fin_Financial_Account_ID AS Fin_Fina1_321_0_,
       fin_financ0_.AD_Client_ID AS AD_Clien2_321_0_,
       fin_financ0_.AD_Org_ID AS AD_Org_I3_321_0_,

  (SELECT MAX(rec.endingbalance)
   FROM fin_reconciliation rec
   WHERE rec.fin_financial_account_id = fin_financ0_.fin_financial_account_id
     AND EXISTS
       (SELECT 1
        FROM fin_reconciliation fr
        WHERE fr.processed='Y'
          AND fr.fin_financial_account_id=fin_financ0_.fin_financial_account_id
        HAVING rec.dateto = MAX(fr.dateto))) AS formula1_0_,

  (SELECT MAX(fr.dateto)
   FROM fin_financial_account ff,
        fin_reconciliation fr
   WHERE ff.fin_financial_account_id=fr.fin_financial_account_id
     AND ff.fin_financial_account_id=fin_financ0_.fin_financial_account_id) AS formula2_0_
FROM FIN_Financial_Account fin_financ0_
WHERE fin_financ0_.Fin_Financial_Account_ID IN ($1, ...);

Steps To Reproduce

Go to backoffice, window "Financial Account", choose a store, and in "Name" field, type "CashBook".
Open the Chrome Developer Tools, to see the error status.

Proposed Solution

2 possible workarounds:
1) If we add extra filters (for example, we add in the grid view the column Updated By and select a few users), then the search will return more targeted (and less) results. Error 502 no longer occurs.

2) Second workaround: exclude from the grid view the column "Last Reconciliation Balance" ("Solde dernier rapprochement" in french). We have all the least of cashbooks. Error 502 no longer occurs.

Tags

Performance

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0133047) vmromanos (manager) 2021-11-15 13:48	I understand these columns create a performance issue in your environment, but unfortunately there is not a generic solution for this that we can include into the Product. Instead, you should try to understand how your concrete client is using that window, and then adapt it accordingly. Here you have a how-to that can guide you through the process: http://wiki.openbravo.com/wiki/How_to_configure_high_volume_windows [^] If you don't need to have the information provided by these two columns in the grid, then easiest approach would be to directly hide them. If you need any of them, then most likely you will need to create a good index for the columns you usually filter (like 'name' in your example) and configure the grid accordingly.

Issue History
Date Modified	Username	Field	Change
2021-10-21 18:05	ludmila_ursu	New Issue
2021-10-21 18:05	ludmila_ursu	Assigned To	=> Retail
2021-10-21 18:05	ludmila_ursu	Triggers an Emergency Pack	=> No
2021-10-21 18:07	ludmila_ursu	Tag Attached: Performance
2021-11-12 15:29	marvintm	Assigned To	Retail => Triage Finance
2021-11-12 15:31	marvintm	Project	Retail Modules => Openbravo ERP
2021-11-12 15:32	marvintm	Modules	=> Core
2021-11-12 15:32	marvintm	Category	Retail Analytics => 09. Financial management
2021-11-12 15:32	marvintm	version	RR20Q1.2 =>
2021-11-15 09:49	vmromanos	Assigned To	Triage Finance => vmromanos
2021-11-15 13:48	vmromanos	Review Assigned To	=> vmromanos
2021-11-15 13:48	vmromanos	Note Added: 0133047
2021-11-15 13:48	vmromanos	Status	new => closed
2021-11-15 13:48	vmromanos	Resolution	open => no change required