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.

Tags

NOR

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

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.

Notes
(0139293) egoitz (developer) 2022-07-13 10:12	Note: in one of the customers affected the table OBCFR_BlockchainIssue has not data and the query is still problematic because the size of the table OBCFR_Ticket We have tested also by creating a index on the seqno column but the issue is not fixed.

(0139638) aferraz (developer) 2022-07-27 11:06	We are waiting for a test environment where the problem is reproduced. It's likely that the problem is fixed by applying 0047331.

(0139664) sebastien_liron (reporter) 2022-07-28 10:30	as per victor : Please note we haven't transplanted yet to pmods; we will do it in several weeks as part of the preparation for the new certification process (which will take place in one month's time). It does not seems to me that the MR was done on the standard module

Issue History
Date Modified	Username	Field	Change
2022-07-13 08:53	egoitz	New Issue
2022-07-13 08:53	egoitz	Issue generated from	0049691
2022-07-13 08:53	egoitz	Relationship added	related to 0049691
2022-07-13 09:46	rafaroda	Tag Attached: NOR
2022-07-13 09:46	aferraz	Assigned To	=> igor_trebol
2022-07-13 10:12	egoitz	Note Added: 0139293
2022-07-25 12:41	sebastien_liron	Issue Monitored: sebastien_liron
2022-07-27 11:06	aferraz	Note Added: 0139638
2022-07-27 11:06	aferraz	Assigned To	igor_trebol => egoitz
2022-07-27 11:06	aferraz	Status	new => feedback
2022-07-28 08:57	hgbot	Note Added: 0139656
2022-07-28 09:01	hgbot	Note Added: 0139657
2022-07-28 09:27	aferraz	Note Deleted: 0139656
2022-07-28 09:27	aferraz	Note Deleted: 0139657
2022-07-28 10:30	sebastien_liron	Note Added: 0139664
2023-03-20 13:26	aferraz	Relationship added	duplicate of 0051089
2023-03-20 13:26	aferraz	Status	feedback => closed
2023-03-20 13:26	aferraz	Resolution	open => duplicate