0049792: Some queries are slow, and read lot of information from disk on environment with millioins of transation.

Openbravo Issue Tracking System - Openbravo Localizations

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0049792

Openbravo Localizations

Localization France

public

2022-07-13 08:53

2023-03-20 13:26

Reporter

egoitz

Assigned To

egoitz

Priority

urgent

Severity

major

Reproducibility

always

Status

closed

Resolution

duplicate

Platform

OS Version

Product Version

Target Version

Fixed in Version

Merge Request Status

Regression date

Regression introduced by commit

Regression level

Support ticket

OBNetwork customer

Regression introduced in release

Summary

0049792: Some queries are slow, and read lot of information from disk on environment with millioins of transation.

Description

There are some queries related to the french localization and the fiscal certification that are creating high disk usage /contention on environments with million of records.

Query executed on the loging of the terminal.
[http-8] INFO org.openbravo.retail.posterminal.term.Terminal - [TER] Load query terminal class org.openbravo.certification.france.ui.UIComponentQuery

select
  obpos_appl0_.EM_Obcfr_Isblockchained as col_0_0_,
  coalesce(obcfrticke1_.Seqno, $5) as col_1_0_,
  obcfrticke1_.Hash as col_2_0_,
  $6 as col_3_0_,
  (
    select
      coalesce(
        max(obcfr_bloc2_.Seqno),
        $7
      )
    from
      OBCFR_BlockchainIssue obcfr_bloc2_
    where
      obcfr_bloc2_.Entity = $1
      and coalesce(
        obcfr_bloc2_.Obpos_Applications_ID,
        obcfr_bloc2_.AD_Client_ID
      )= $2
  ) as col_4_0_
from
  OBPOS_APPLICATIONS obpos_appl0_
  left outer join OBCFR_Ticket obcfrticke1_ on obpos_appl0_.Obpos_Applications_ID = obcfrticke1_.Obpos_Applications_ID
where
  obpos_appl0_.Obpos_Applications_ID = $3
  and coalesce(obcfrticke1_.Seqno, $8)= coalesce(
    (
      select
        max(obcfr_tick3_.Seqno)
      from
        OBCFR_Ticket obcfr_tick3_
      where
        obcfr_tick3_.Obpos_Applications_ID = $4
    ),
    $9
  )

Steps To Reproduce

Check database statistics.

Execute the query on an environmnent with million of records in the table.

Proposed Solution

Keep last seqno on the obpos_application so the query is much faster than going throw the big table.

Additional Information

Tags

NOR

Relationships

duplicate of	defect	0051089		closed		Modules	Slowness on some queries of the module
related to	defect	0049691		closed	egoitz	Openbravo Localizations	Some queries are slow, and read lot of information from disk on environment with millioins of transation.