Openbravo Issue Tracking System - Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0051902ModulesDistribution Orderspublic2023-03-17 11:012023-04-28 09:59
Reporteregoitz 
Assigned ToTriage Omni WMS 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionunable to reproduce 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Regression date
Regression introduced by commit
Regression level
Review Assigned To
Support ticket
OBNetwork customerGold
Regression introduced in release
Summary0051902: Performance issues on distribution orders module.
DescriptionThere 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 Reproducen/a
Proposed SolutionCreate a combined index on aux_distorder table

create index __NAME__ on obdo_distorder (obdo_distorder_id,receiptstatus);
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2023-03-17 11:01egoitzNew Issue
2023-03-17 11:01egoitzAssigned To => Triage Omni WMS
2023-03-17 11:01egoitzOBNetwork customer => Gold
2023-03-23 19:27mtaalNote Added: 0147909
2023-04-13 11:21mtaalNote Added: 0148431
2023-04-13 11:21mtaalStatusnew => feedback
2023-04-27 11:40mtaalNote Added: 0148988
2023-04-28 09:59mtaalNote Added: 0149056
2023-04-28 09:59mtaalStatusfeedback => closed
2023-04-28 09:59mtaalResolutionopen => unable to reproduce

Notes
(0147909)
mtaal   
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   
2023-04-13 11:21   
Can you share the explain analyze before and after the change.
(0148988)
mtaal   
2023-04-27 11:40   
Good day, any news on this?
(0149056)
mtaal   
2023-04-28 09:59   
closing after aligning with support