Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0047907Openbravo ERP09. Financial managementpublic2021-10-21 18:052021-11-15 13:48
Reporterludmila_ursu 
Assigned Tovmromanos 
PrioritynormalSeveritymajorReproducibilitysometimes
StatusclosedResolutionno change required 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Tovmromanos
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0047907: Performance issue when searching in window "Financial Account" report "CashBook"
DescriptionFor 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 ReproduceGo 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 Solution2 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.
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-10-21 18:05ludmila_ursuNew Issue
2021-10-21 18:05ludmila_ursuAssigned To => Retail
2021-10-21 18:05ludmila_ursuTriggers an Emergency Pack => No
2021-10-21 18:07ludmila_ursuTag Attached: Performance
2021-11-12 15:29marvintmAssigned ToRetail => Triage Finance
2021-11-12 15:31marvintmProjectRetail Modules => Openbravo ERP
2021-11-12 15:32marvintmModules => Core
2021-11-12 15:32marvintmCategoryRetail Analytics => 09. Financial management
2021-11-12 15:32marvintmversionRR20Q1.2 =>
2021-11-15 09:49vmromanosAssigned ToTriage Finance => vmromanos
2021-11-15 13:48vmromanosReview Assigned To => vmromanos
2021-11-15 13:48vmromanosNote Added: 0133047
2021-11-15 13:48vmromanosStatusnew => closed
2021-11-15 13:48vmromanosResolutionopen => no change required

Notes
(0133047)
vmromanos   
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.