Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0038785 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] 09. Financial management | major | always | 2018-06-18 18:29 | 2018-06-21 18:05 | |||
| Reporter | gorkaion | View Status | public | |||||
| Assigned To | aferraz | |||||||
| Priority | urgent | Resolution | fixed | Fixed in Version | 3.0PR18Q3 | |||
| Status | closed | Fix in branch | Fixed in SCM revision | d674bd5ec9d1 | ||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Review Assigned To | Sandrahuguet | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0038785: perfomance issue in add details button of payment in window | |||||||
| Description | The grid to select Order/Invoices is very slow when trying to use the filter and having some invoices already selected. | |||||||
| Steps To Reproduce | On 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 Solution | The 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' ) | |||||||
| Tags | No tags attached. | |||||||
| Attached Files | ||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||
|
|||||||||||||||
 Relationships |
|
Notes |
|
|
(0105255) aferraz (manager) 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 (manager) 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 (developer) 2018-06-20 11:40 |
Code review + testing OK |
|
(0105317) hudsonbot (developer) 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 |
|
Notes |
|
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 | Modules | => Core |
| 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 |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |