Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0024920Openbravo ERP09. Financial managementpublic2013-10-09 19:582013-11-28 19:57
Reportershuehner 
Assigned Toeduardo_Argal 
PriorityurgentSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Toeduardo_Argal
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0024920: AcctServer.checkDocumentDates is very slow and is missing fix from issue 23420
DescriptionIn the old issue 23420 a fix was implemented to speed up the checkDocuments method. However another change done shortly before made that method completely unused and replaced it by a checkDocumentsDates method.

So the fix for 23420 is not working at all in the product. However the same conceptional fix can be applied instead to the checkDocumentDates method.

Note: the unsued checkDocuments method has now been deleted in the 24919 issue to avoid that confusion again.
Steps To Reproducerun install.source on oracle on pi
start tomcat
Notice that accounting server takes 3-5 minutes of runtime.
When instrumenting the code notice that the checkDocumentDates methods is called several times and every invocation for the table 319 (invoice) is very slow (i.e. 25seconds).
Proposed SolutionApply conception fix from issue 23420 to the new checkDocumentDates methods.

Checking caller of that method shows that the only important result of that check is if there is any unposted document in a open period.
The concrete id of such a document is only used in a debug level log statement.

In later processing using debug level the accounting server does already report all id's it is processing.

So that debug level statement here does not add a lot of value.

So query can be changed to only report existence of such a document without requiring the min(id) scanning of the table.

See attached patch for an idea.
That version of the query does give very good runtime on oracle/pi.
Preliminary testing also shows it to be better then the pi version on 2 other bigger customer dbs on postgres (ask etxarri to for details).
Additional Information
TagsPerformance
Relationships
related to defect 00234203.0MP22 closed jecharri Accounting background process has really poor performance 
related to defect 0024919 closed shuehner AccountingServer.checkDocuments is too slow and repeats same work multiple times 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2013-10-09 19:58shuehnerNew Issue
2013-10-09 19:58shuehnerAssigned To => eduardo_Argal
2013-10-09 19:58shuehnerModules => Core
2013-10-09 19:58shuehnerOBNetwork customer => No
2013-10-09 19:58shuehnerTriggers an Emergency Pack => No
2013-10-09 19:58shuehnerRelationship addedrelated to 0023420
2013-10-09 19:58shuehnerRelationship addedrelated to 0024919
2013-10-09 20:00shuehnerNote Added: 0061644
2013-11-27 11:15hgbotCheckin
2013-11-27 11:15hgbotNote Added: 0062398
2013-11-27 11:15hgbotStatusnew => resolved
2013-11-27 11:15hgbotResolutionopen => fixed
2013-11-27 11:15hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/225fca243c8171ffc42b5989308a3f0f83e5c47f [^]
2013-11-27 11:19alostaleTag Attached: Performance
2013-11-27 11:40eduardo_ArgalReview Assigned To => eduardo_Argal
2013-11-27 11:40eduardo_ArgalNote Added: 0062403
2013-11-27 11:40eduardo_ArgalStatusresolved => closed
2013-11-28 19:57hudsonbotCheckin
2013-11-28 19:57hudsonbotNote Added: 0062463

Notes
(0061644)
shuehner   
2013-10-09 20:00   
Prototype work of new query, essentially rewrite to only report existence and pull up all inner exists parts into the main query:

select 1 as id from dual where exists (
SELECT 1
         FROM C_Invoice a, c_periodcontrol, c_period
         WHERE a.ad_client_id = '23C59575B9CF467C9620760EB255B389'
           AND a.ad_org_id IN ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43')
           AND a.posted = 'N'
           AND a.processed = 'Y'
           AND (a.processing = 'N' OR a.processing IS NULL)
           and c_periodcontrol.c_period_id = c_period.c_period_id
             AND c_periodcontrol.docbasetype = 'APC'
             AND c_periodcontrol.periodstatus = 'O'
             AND ad_org_getperiodcontrolallow(a.ad_org_id) = c_periodcontrol.ad_org_id
             AND a.dateacct >= c_period.StartDate
             AND a.dateacct < c_period.EndDate + 1
        AND 1=1
)
(0062398)
hgbot   
2013-11-27 11:15   
Repository: erp/devel/pi
Changeset: 225fca243c8171ffc42b5989308a3f0f83e5c47f
Author: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
Date: Wed Nov 27 15:15:07 2013 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/225fca243c8171ffc42b5989308a3f0f83e5c47f [^]

Fixes Issue 24920: AcctServer.checkDocumentDates is very slow

---
M src/org/openbravo/erpCommon/ad_forms/AcctServer.java
M src/org/openbravo/erpCommon/ad_forms/AcctServer_data.xsql
---
(0062403)
eduardo_Argal   
2013-11-27 11:40   
Code reviewed and tested
(0062463)
hudsonbot   
2013-11-28 19:57   
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/ce703e5082ac [^]

Maturity status: Test