0051902: Performance issues on distribution orders module.

Description

There is a query that take long.

The query is the following

explain analyze
SELECT
  obdo_distr0_.Obdo_Distorder_V_ID AS Obdo1_713_,
  obdo_distr0_.AD_Client_ID AS AD2_713_,
  obdo_distr0_.AD_Org_ID AS AD3_713_,
  obdo_distr0_.Isactive AS Isactive713_,
  obdo_distr0_.Created AS Created713_,
  obdo_distr0_.Createdby AS Createdby713_,
  obdo_distr0_.Updated AS Updated713_,
  obdo_distr0_.Updatedby AS Updatedby713_,
  obdo_distr0_.Name AS Name713_,
  obdo_distr0_.Documentno AS Documentno713_,
  obdo_distr0_.Movementdate AS Movemen11_713_,
  obdo_distr0_.Processed AS Processed713_,
  obdo_distr0_.Issue AS Issue713_,
  obdo_distr0_.Issuestatus AS Issuest14_713_,
  obdo_distr0_.Receipt AS Receipt713_,
  obdo_distr0_.Receiptstatus AS Receipt16_713_,
  obdo_distr0_.Docstatus AS Docstatus713_
FROM
  OBDO_DistOrder_V obdo_distr0_
  LEFT OUTER JOIN AD_Org organizati1_ ON obdo_distr0_.AD_Org_ID = organizati1_.AD_Org_ID
WHERE
  (
    upper (
      (
        COALESCE (
          to_char (organizati1_.Name),
          ''
        ) || ' - ' || COALESCE (
          to_char (obdo_distr0_.Name),
          ''
        ) || ' - ' || COALESCE (
          to_char (obdo_distr0_.Documentno),
          ''
        ) || ' - ' || COALESCE (
          to_char (obdo_distr0_.Issue),
          ''
        ) || ' - ' || COALESCE (
          to_char (obdo_distr0_.Receipt),
          ''
        )
      )
    ) LIKE upper ('%e%') ESCAPE '|'
  )
  AND obdo_distr0_.Processed = 'Y'
  AND obdo_distr0_.Receiptstatus < 100
  AND obdo_distr0_.Issuestatus <= 100
  AND obdo_distr0_.AD_Org_ID <> 'FB3D5BC10CB143448FAAEDA82EE87109'
  AND NOT (
    EXISTS (
      SELECT
        1
      FROM
        M_Movement materialmg2_
      WHERE
        materialmg2_.EM_Obdo_Issue_Movement_ID = obdo_distr0_.Obdo_Distorder_V_ID
    )
  )
  AND (
    obdo_distr0_.Docstatus NOT IN ('NR', 'RP')
  )
  AND (
    obdo_distr0_.AD_Client_ID IN (
      'F95317157F4B40D78D85716D6AF4C672',
      '0'
    )
  )
  AND obdo_distr0_.Isactive = 'Y'
ORDER BY
  organizati1_.Name,
  obdo_distr0_.Name,
  obdo_distr0_.Documentno,
  obdo_distr0_.Issue,
  obdo_distr0_.Receipt,
  obdo_distr0_.Obdo_Distorder_V_ID
LIMIT
  '76';

Steps To Reproduce

n/a

Proposed Solution

Create a combined index on aux_distorder table

create index __NAME__ on obdo_distorder (obdo_distorder_id,receiptstatus);

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0147909) mtaal (manager) 2023-03-23 19:27	For this issue, you propose this index: create index __NAME__ on obdo_distorder (obdo_distorder_id,receiptstatus); But would this index not be better: create index __NAME__ on obdo_distorder (receiptstatus); or create index __NAME__ on obdo_distorder (receiptstatus, obdo_distorder_id); And from which java class is the query fired?

(0148431) mtaal (manager) 2023-04-13 11:21	Can you share the explain analyze before and after the change.

(0148988) mtaal (manager) 2023-04-27 11:40	Good day, any news on this?

(0149056) mtaal (manager) 2023-04-28 09:59	closing after aligning with support

Issue History
Date Modified	Username	Field	Change
2023-03-17 11:01	egoitz	New Issue
2023-03-17 11:01	egoitz	Assigned To	=> Triage Omni WMS
2023-03-23 19:27	mtaal	Note Added: 0147909
2023-04-13 11:21	mtaal	Note Added: 0148431
2023-04-13 11:21	mtaal	Status	new => feedback
2023-04-27 11:40	mtaal	Note Added: 0148988
2023-04-28 09:59	mtaal	Note Added: 0149056
2023-04-28 09:59	mtaal	Status	feedback => closed
2023-04-28 09:59	mtaal	Resolution	open => unable to reproduce