0025705: c_bpartner_trg can be optimized for performance

Description

This trigger is called on every update to c_bpartner which is among other things in c_invoice_post (which updates credit used).

sourcecode of that trigger (for update case) shows
a.) one conditional block if pricelist changes
b.) one unconditional block of updating three bpartner related acocunting tables
C_BP_CUSTOMER_ACCT
C_BP_VENDOR_ACCT
C_BP_EMPLOYEE_ACCT

For those tables ad_org_id changes from c_bpartner are synchronized into them.

However if the ad_org_id of the c_bpartner did not change (which it will nearly never in normal operation) then those updates could probably be skipped.

Steps To Reproduce

Post an invoice
check pg_stat_user_functions (after activating it and notice runtime of around 100ms in c_bpartner_trg)
Note: that runtime is obversed with having 0.25million rows in each of those tables.

Proposed Solution

Add
if (old.ad_org_id <> new.ad_org_id)

around the three update statements.

Testing this locally reduces trigger runtime for case of c_invoice_post to 0.

Tags

Performance

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0064058) hgbot (developer) 2014-02-11 17:08	Repository: erp/devel/pi Changeset: d704451d1d2e56dd204127024fadb3aa2f2fdd7d Author: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com> Date: Tue Feb 11 17:01:36 2014 +0100 URL: http://code.openbravo.com/erp/devel/pi/rev/d704451d1d2e56dd204127024fadb3aa2f2fdd7d [^] Fixes bug 25705: c_bpartner_trg can be optimized for performance --- M src-db/database/model/triggers/C_BPARTNER_TRG.xml ---

(0064062) dmiguelez (developer) 2014-02-11 19:34	Code Review + Testing Ok

(0064186) hudsonbot (developer) 2014-02-12 18:30	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/d1a5bb862230 [^] Maturity status: Test

Issue History
Date Modified	Username	Field	Change
2014-02-11 15:52	shuehner	New Issue
2014-02-11 15:52	shuehner	Assigned To	=> AugustoMauch
2014-02-11 15:52	shuehner	Modules	=> Core
2014-02-11 15:52	shuehner	Triggers an Emergency Pack	=> No
2014-02-11 16:00	shuehner	Assigned To	AugustoMauch => dmiguelez
2014-02-11 16:00	shuehner	Tag Attached: Performance
2014-02-11 17:08	hgbot	Checkin
2014-02-11 17:08	hgbot	Note Added: 0064058
2014-02-11 17:08	hgbot	Status	new => resolved
2014-02-11 17:08	hgbot	Resolution	open => fixed
2014-02-11 17:08	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/d704451d1d2e56dd204127024fadb3aa2f2fdd7d [^]
2014-02-11 19:34	dmiguelez	Review Assigned To	=> dmiguelez
2014-02-11 19:34	dmiguelez	Note Added: 0064062
2014-02-11 19:34	dmiguelez	Status	resolved => closed
2014-02-12 18:30	hudsonbot	Checkin
2014-02-12 18:30	hudsonbot	Note Added: 0064186