Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0025682Openbravo ERPI. Performancepublic2014-02-07 07:462014-02-07 14:09
shuehner 
shuehner 
urgentmajorhave not tried
closedfixed 
5
3.0MP22.2 
 
dmiguelez
Core
No
0025682: Missing index for c_invoice table c_order_id column makes at least c_order_trg + c_order_trg2 slow
Both 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.

Observe 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
Add proposed trigger

create index c_invoice_order on c_invoice(c_order_id);
Performance
related to defect 0025683 closed dmiguelez c_order_trg is doing updates to c_invoice when not needed 
Issue History
2014-02-07 07:46shuehnerNew Issue
2014-02-07 07:46shuehnerAssigned To => AugustoMauch
2014-02-07 07:46shuehnerModules => Core
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