Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038785Openbravo ERP09. Financial managementpublic2018-06-18 18:292018-06-21 18:05
Reportergorkaion 
Assigned Toaferraz 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q3 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0038785: perfomance issue in add details button of payment in window
DescriptionThe grid to select Order/Invoices is very slow when trying to use the filter and having some invoices already selected.

Steps To ReproduceOn a environment with a high volume of sales invoices pending to pay.

1 Create a new payment and click on add details.
2 Clear the Order/Invoice grid default filters.
3 Select an Invoice and set an amount.
4 Try to filter by Document No. -> it is very slow.

If you try to filter by Document No with no invoice selected the search is fast.
Proposed SolutionThe issue is in the query that is build when there are records selected. It adds an OR clause that results in a bad execution plan:

AND (
     fin_paymen0_.FIN_Payment_Scheduledetail_ID IN ('17262A2A1E0E43B69935E7E2BD221BBB', 'F2FE327416A746E0A6735B21363D801D')
       OR
      invoice7_.IsSOTrx = 'Y' AND invoice7_.C_Currency_ID = '130' AND businesspa1_.C_BPartner_ID = '812938173C81527EE97A8201FD232C24'
       
)
Additional Information
TagsNo tags attached.
Relationships
related to defect 0033629 closed alostale slow order/invoice grid population in Add Payment 
related to design defect 0038930 new Triage Omni OMS Perfomance issue in add details button of payment in window 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-06-18 18:29gorkaionNew Issue
2018-06-18 18:29gorkaionAssigned To => vmromanos
2018-06-18 18:29gorkaionModules => Core
2018-06-18 18:29gorkaionResolution time => 1531087200
2018-06-18 18:29gorkaionTriggers an Emergency Pack => No
2018-06-18 18:39aferrazAssigned Tovmromanos => aferraz
2018-06-19 13:50aferrazNote Added: 0105255
2018-06-19 14:07aferrazRelationship addedrelated to 0033629
2018-06-19 15:20aferrazNote Added: 0105259
2018-06-19 16:19aferrazStatusnew => scheduled
2018-06-19 16:21aferrazNote Edited: 0105259bug_revision_view_page.php?bugnote_id=0105259#r17327
2018-06-19 16:23SandrahuguetReview Assigned To => Sandrahuguet
2018-06-20 11:38hgbotCheckin
2018-06-20 11:38hgbotNote Added: 0105274
2018-06-20 11:38hgbotStatusscheduled => resolved
2018-06-20 11:38hgbotResolutionopen => fixed
2018-06-20 11:38hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d674bd5ec9d12c22e4e89f75090be206241b808f [^]
2018-06-20 11:40SandrahuguetNote Added: 0105275
2018-06-20 11:40SandrahuguetStatusresolved => closed
2018-06-20 11:40SandrahuguetFixed in Version => 3.0PR18Q3
2018-06-21 18:05hudsonbotCheckin
2018-06-21 18:05hudsonbotNote Added: 0105317
2018-07-18 10:21aferrazRelationship addedrelated to 0038930

Notes
(0105255)
aferraz   
2018-06-19 13:50   
Test plan I:

1.- Create a record in Sales Order, Purchase Order, Sales Invoice or Purchase Invoice window, add a line and book it.
2.- Click on Add Payment
3.- Realize order/invoice is automatically selected in the grid.
4.- Filter by any other order/invoice in the grid.
5.- Realize both records appear in the grid, the first one selected and the second one not selected.
6.- Select the second record.
7.- Clear the filter.
8.- Realize the two records appear selected and the rest of records appear not selected.
9.- Filter by any other order/invoice in the grid.
10.- Realize the three records appear in the grid, the first two selected and the third one not selected.

Repeat the test using Purchase Order, Sales Invoice and Purchase Invoice windows.

Test plan II:

1.- Create a record in Payment In window, selecting a business partner.
2.- Click on Add Payment
3.- Unselect every record in the order/invoice grid.
4.- Filter by any order/invoice in the grid.
5.- Realize the record appears in the grid.
6.- Select the record.
7.- Clear the filter.
8.- Realize the record appears selected and the rest of records appear not selected.
9.- Filter by any other order/invoice in the grid.
10.- Realize both records appear in the grid, the first one selected and the second one not selected.

Repeat the test using Payment Out window, Financial Account | Transaction tab with BP Deposit and BP Withdrawal type and Add Payment from menu.

Test plan III:

1.- Create a record in Payment In window, without selecting a business partner.
2.- Click on Add Payment
3.- Unselect every record in the order/invoice grid.
4.- Filter by any order/invoice in the grid belonging to one business partner.
5.- Realize the record appears in the grid.
6.- Select the record.
7.- Clear the filter.
8.- Realize the record appears selected and the rest of records appear not selected.
9.- Filter by any other order/invoice in the grid belonging to a different business partner.
10.- Realize both records appear in the grid, the first one selected and the second one not selected.

Repeat the test using Payment Out window, Financial Account | Transaction tab with BP Deposit and BP Withdrawal type and Add Payment from menu.
(0105259)
aferraz   
2018-06-19 15:20   
(edited on: 2018-06-19 16:21)
Performance improved in customer environment from around 200 seconds to around 5 seconds.
(0105274)
hgbot   
2018-06-20 11:38   
Repository: erp/devel/pi
Changeset: d674bd5ec9d12c22e4e89f75090be206241b808f
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Tue Jun 19 10:48:12 2018 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d674bd5ec9d12c22e4e89f75090be206241b808f [^]

Fixes issue 38785: Perfomance issue in add details button of payment in window

Performance problem when filtering by order/invoice in Add Payment grid if there is any record selected.

If no record is selected in the grid, the query performs well.

However, if we filter the grid with some record selected, the query adds an OR clause that results in a bad execution plan:
AND (fin_paymen0_.FIN_Payment_Scheduledetail_ID IN ('17262A2A1E0E43B69935E7E2BD221BBB', 'F2FE327416A746E0A6735B21363D801D')
OR invoice7_.IsSOTrx = 'Y'
AND invoice7_.C_Currency_ID = '130'
AND businesspa1_.C_BPartner_ID = '812938173C81527EE97A8201FD232C24')

C_BPartner_ID filter has been moved out from the OR clause, fixing the performance problem:
AND (fin_paymen0_.FIN_Payment_Scheduledetail_ID IN ('17262A2A1E0E43B69935E7E2BD221BBB', 'F2FE327416A746E0A6735B21363D801D')
OR invoice7_.IsSOTrx = 'Y'
AND invoice7_.C_Currency_ID = '130')
AND businesspa1_.C_BPartner_ID = '812938173C81527EE97A8201FD232C24'

---
M modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/hqlinjections/AddPaymentOrderInvoicesTransformer.java
---
(0105275)
Sandrahuguet   
2018-06-20 11:40   
Code review + testing OK
(0105317)
hudsonbot   
2018-06-21 18:05   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/c34295dcdb97 [^]
Maturity status: Test