Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0025682Openbravo ERPI. Performancepublic2014-02-07 07:462014-02-07 14:09
Reportershuehner 
Assigned Toshuehner 
PriorityurgentSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version3.0MP22.2 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0025682: Missing index for c_invoice table c_order_id column makes at least c_order_trg + c_order_trg2 slow
DescriptionBoth those triggers do an update to c_invoice which is looked up via c_invoice.c_order_id to synchronize a few fields from c_order to c_invoice.

with 700k rows in c_invoice just this one update is taking 230ms each time.

Steps To ReproduceObserve trigger runtime in database with many invoices.

i.e. set track_functions = 'all' in pg config
Then after running code executing the update
check trigger runtime with select * from pg_stat_user_functions order by total_time desc
Proposed SolutionAdd proposed trigger

create index c_invoice_order on c_invoice(c_order_id);
Additional Information
TagsPerformance
Relationships
related to defect 0025683 closed dmiguelez c_order_trg is doing updates to c_invoice when not needed 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-02-07 07:46shuehnerNew Issue
2014-02-07 07:46shuehnerAssigned To => AugustoMauch
2014-02-07 07:46shuehnerModules => Core
2014-02-07 07:46shuehnerOBNetwork customer => Yes
2014-02-07 07:46shuehnerTriggers an Emergency Pack => No
2014-02-07 07:49shuehnerRelationship addedrelated to 0025683
2014-02-07 07:52shuehnerNote Added: 0064009
2014-02-07 08:03alostaleTag Attached: Performance
2014-02-07 08:03alostaleAssigned ToAugustoMauch => dmiguelez
2014-02-07 12:33priyamIssue Monitored: priyam
2014-02-07 14:01hgbotCheckin
2014-02-07 14:01hgbotNote Added: 0064016
2014-02-07 14:01hgbotStatusnew => resolved
2014-02-07 14:01hgbotResolutionopen => fixed
2014-02-07 14:01hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/c73830693d6559bcdcc997321ad3d93f47436f6b [^]
2014-02-07 14:01shuehnerAssigned Todmiguelez => shuehner
2014-02-07 14:09dmiguelezReview Assigned To => dmiguelez
2014-02-07 14:09dmiguelezNote Added: 0064017
2014-02-07 14:09dmiguelezStatusresolved => closed

Notes
(0064009)
shuehner   
2014-02-07 07:52   
I think this fix should be included in the next MP30.x emergency mp as i see no regression risk and it is a useful performance fix to get out to every user.
(0064016)
hgbot   
2014-02-07 14:01   
Repository: erp/devel/pi
Changeset: c73830693d6559bcdcc997321ad3d93f47436f6b
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Fri Feb 07 12:56:56 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/c73830693d6559bcdcc997321ad3d93f47436f6b [^]

Fixed 25682: Add index on c_invoice.c_order_id to speedup triggers
c_order_trg & c_order_trg2

---
M src-db/database/model/tables/C_INVOICE.xml
---
(0064017)
dmiguelez   
2014-02-07 14:09   
Code Review + Testing Ok