Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0038785
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajoralways2018-06-18 18:292018-06-21 18:05
ReportergorkaionView Statuspublic 
Assigned Toaferraz 
PriorityurgentResolutionfixedFixed in Version3.0PR18Q3
StatusclosedFix in branchFixed in SCM revisiond674bd5ec9d1
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket2658
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0038785: 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'
       
)
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0033629 closedalostale slow order/invoice grid population in Add Payment 
related to design defect 0038930 newTriage Omni OMS Perfomance issue in add details button of payment in window 

-  Notes
(0105255)
aferraz (viewer)
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 (viewer)
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 (developer)
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 (viewer)
2018-06-20 11:40

 Code review + testing OK
(0105317)
hudsonbot (viewer)
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

- Issue History
Date Modified Username Field Change
2018-06-18 18:29 gorkaion New Issue
2018-06-18 18:29 gorkaion Assigned To => vmromanos
2018-06-18 18:29 gorkaion OBNetwork customer => Yes
2018-06-18 18:29 gorkaion Modules => Core
2018-06-18 18:29 gorkaion Support ticket => 2658
2018-06-18 18:29 gorkaion Resolution time => 1531087200
2018-06-18 18:29 gorkaion Triggers an Emergency Pack => No
2018-06-18 18:39 aferraz Assigned To vmromanos => aferraz
2018-06-19 13:50 aferraz Note Added: 0105255
2018-06-19 14:07 aferraz Relationship added related to 0033629
2018-06-19 15:20 aferraz Note Added: 0105259
2018-06-19 16:19 aferraz Status new => scheduled
2018-06-19 16:21 aferraz Note Edited: 0105259 View Revisions
2018-06-19 16:23 Sandrahuguet Review Assigned To => Sandrahuguet
2018-06-20 11:38 hgbot Checkin
2018-06-20 11:38 hgbot Note Added: 0105274
2018-06-20 11:38 hgbot Status scheduled => resolved
2018-06-20 11:38 hgbot Resolution open => fixed
2018-06-20 11:38 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d674bd5ec9d12c22e4e89f75090be206241b808f [^]
2018-06-20 11:40 Sandrahuguet Note Added: 0105275
2018-06-20 11:40 Sandrahuguet Status resolved => closed
2018-06-20 11:40 Sandrahuguet Fixed in Version => 3.0PR18Q3
2018-06-21 18:05 hudsonbot Checkin
2018-06-21 18:05 hudsonbot Note Added: 0105317
2018-07-18 10:21 aferraz Relationship added related to 0038930

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker