Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035203Openbravo ERP09. Financial managementpublic2017-02-08 16:062017-03-15 20:19
Reportermalsasua 
Assigned Tovmromanos 
PrioritynormalSeveritymajorReproducibilitysometimes
StatusclosedResolutionfixed 
PlatformOS20OS VersionCommunity Appliance
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Tovmromanos
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035203: performance problem in DocInvoice with cashvat functionality
DescriptionIf you have using Cash Vat functionality in invoices, and it is a instance with a high volumes (>5 millions invoice lines), when you try to post one invoice, it takes more than 30 seconds
Steps To Reproducewith high volume, create and process one invoice with the "cash vat" option selected
. run post process
it takes more than 30 seconds
Proposed SolutionThe problem is in this function
https://code.openbravo.com/erp/devel/pi/file/37570fc76755/src/org/openbravo/erpCommon/utility/CashVATUtil.java#l393 [^]

it is very slow because it is doing a join between c_invoicetax_cashvat_v view and c_invoicetax and c_invoice tables.

The joins could be removed from query if two new columns are added in the view c_invoicetax_cashvat_v: c_invoice_id and c_tax_id (see attachments). This info is available in the tables used in the view, but they are not returned in the select section
Additional Information
TagsPerformance
Relationships
Attached Filesdiff diffCashVatPerformance.diff (12,856) 2017-02-08 17:25
https://issues.openbravo.com/file_download.php?file_id=10430&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-02-08 16:06malsasuaNew Issue
2017-02-08 16:06malsasuaAssigned To => Triage Finance
2017-02-08 16:06malsasuaModules => Core
2017-02-08 16:06malsasuaResolution time => 1488063600
2017-02-08 16:06malsasuaTriggers an Emergency Pack => No
2017-02-08 17:10markmm82Assigned ToTriage Finance => Sanjota
2017-02-08 17:25malsasuaFile Added: diffCashVatPerformance.diff
2017-02-08 17:38malsasuaTag Attached: Performance
2017-02-14 09:53vmromanosAssigned ToSanjota => vmromanos
2017-02-14 12:56vmromanosStatusnew => scheduled
2017-02-14 14:34vmromanosNote Added: 0094319
2017-02-15 09:22hgbotCheckin
2017-02-15 09:22hgbotNote Added: 0094346
2017-02-15 09:22hgbotStatusscheduled => resolved
2017-02-15 09:22hgbotResolutionopen => fixed
2017-02-15 09:22hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/37941a0223cf729a6859a3d2c71058195da909b6 [^]
2017-02-15 09:36vmromanosReview Assigned To => vmromanos
2017-02-15 09:36vmromanosNote Added: 0094348
2017-02-15 09:36vmromanosStatusresolved => closed
2017-03-15 20:19hudsonbotCheckin
2017-03-15 20:19hudsonbotNote Added: 0095184

Notes
(0094319)
vmromanos   
2017-02-14 14:34   
Install Cash VAT module

Log in as Openbravo, client FB
Go to Tax Rate window, create a new record:
 * Name = Cash VAT
 * Rate = 21%
 * Valid from = Today
 * Country = Spain
 * Cash Vat = Y
Go to accounting tab and enter any account in temporary Cash VAT accounts for each of the general ledgers.

Test I:
Go to Sales Invoice and create a new record for Spain org. Set Cash VAT = Y
Enter any line and select Cash VAT tax rate
Complete it
Post it.
Verify tax amounts have been assigned to temporary cash vat accounts since no prepayment has been made.

Test II:
Go to Sales order and create a new record for Spain org. Set Cash VAT = Y. Invoice terms = immediate.
Enter any line and select Cash VAT tax rate
Complete it.
Add a payment that fully pays the order.
Go to Sales Invoice window and create a new invoice using the Create Lines from button and selecting the previously fully paid order.
Complete it.
Post it.
Verify tax amounts have been assigned to the final tax accounts (not the temporary cash vat ones)


Test III:
Go to Sales order and create a new record for Spain org. Set Cash VAT = Y. Invoice terms = immediate.
Enter any line and select Cash VAT tax rate
Complete it.
Add a payment that partially pays the order.
Go to Sales Invoice window and create a new invoice using the Create Lines from button and selecting the previously partially paid order.
Complete it.
Post it.
Verify part of the tax amounts have been assigned to the final tax accounts (which corresponds to the amount already prepaid), and the rest of the tax amounts have been assigned to the temporary tax accounts (percentage not paid yet).
(0094346)
hgbot   
2017-02-15 09:22   
Repository: erp/devel/pi
Changeset: 37941a0223cf729a6859a3d2c71058195da909b6
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Tue Feb 14 14:44:17 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/37941a0223cf729a6859a3d2c71058195da909b6 [^]

Fixed bug 35203: performance problem in DocInvoice with cashvat functionality

Solution based on patch supplied by MAL.

Added three new columns to C_INVOICETAX_CASHVAT_V view: c_invoice_id, c_tax_id and isprepayment. Note that this won't create a performance issue, since the correspondent tables are already included in the view.
Redesign HQL query in CashVATUtil.calculatePrepaidPercentageForCashVATTax() method to use those new columns avoiding slow joins to external tables.

After the change the query for this method just uses the C_InvoiceTax_CashVAT_V view without joining with other tables thus improving the performance.

---
M src-db/database/model/views/C_INVOICETAX_CASHVAT_V.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src/org/openbravo/erpCommon/utility/CashVATUtil.java
---
(0094348)
vmromanos   
2017-02-15 09:36   
Code review + testing OK.

Note the code is highly based on the one provided by MAL, that's why the resolver and the reviewer are the same person in this issue.
(0095184)
hudsonbot   
2017-03-15 20:19   
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/54e102bef53e [^]
Maturity status: Test