Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0020422Openbravo ERPA. Platformpublic2012-05-04 14:522013-05-02 16:31
Reporteralostale 
Assigned ToAugustoMauch 
PriorityhighSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0MP23 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0020422: Slow audit
DescriptionIn an instance with ~4M rows in ad_audit_trail table, inserting new rows in audited tables takes much more time than if they were not audited.

For example inserting a row in c_order takes around 200ms whereas not auditing it ~20ms.

The problem is caused by the primary key and indexes in ad_audit_trail, this makes each insertion on it a little bit slower.
Steps To ReproduceDo any insertion with and without audit enabled and verify it is faster without it.
Proposed Solution-Create a temporary table identical to ad_audit_trail but without primary nor indexes.
-Use this table to do the audit insertions.
-Create a background process to move data to temporary to actual table
-Modify audit queries to get info from both sides
Additional Information
TagsPerformance
Relationships
related to design defect 0020421 closed alostale Review needed indexes 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-05-04 14:52alostaleNew Issue
2012-05-04 14:52alostaleAssigned To => alostale
2012-05-04 14:52alostaleModules => Core
2012-05-04 14:53alostaleRelationship addedrelated to 0020421
2012-09-24 17:43AugustoMauchNote Added: 0052363
2012-09-24 17:43AugustoMauchPrioritynormal => high
2012-09-24 17:43AugustoMauchStatusnew => scheduled
2013-03-06 13:22egoitzIssue Monitored: egoitz
2013-03-06 16:56egoitzTriggers an Emergency Pack => No
2013-03-06 16:56egoitzNote Added: 0057104
2013-03-06 17:00egoitzNote Deleted: 0057104
2013-03-06 17:01egoitzNote Added: 0057105
2013-03-07 07:54alostaleNote Added: 0057118
2013-03-07 07:58alostaleTag Attached: Performance
2013-03-07 11:29alostaleNote Edited: 0057118bug_revision_view_page.php?bugnote_id=0057118#r4480
2013-04-23 11:12AugustoMauchAssigned Toalostale => AugustoMauch
2013-04-23 11:12AugustoMauchIssue Monitored: alostale
2013-04-23 11:12AugustoMauchReview Assigned To => alostale
2013-04-23 12:23hgbotCheckin
2013-04-23 12:23hgbotNote Added: 0058120
2013-04-23 12:23hgbotCheckin
2013-04-23 12:23hgbotNote Added: 0058121
2013-04-23 12:23hgbotCheckin
2013-04-23 12:23hgbotNote Added: 0058122
2013-04-23 12:23hgbotCheckin
2013-04-23 12:23hgbotNote Added: 0058123
2013-04-23 12:23hgbotStatusscheduled => resolved
2013-04-23 12:23hgbotResolutionopen => fixed
2013-04-23 12:23hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/c0c3d4e9feabb621a302624bdb27209da34a3770 [^]
2013-04-23 12:35AugustoMauchNote Added: 0058124
2013-04-23 12:35AugustoMauchNote Added: 0058125
2013-04-25 01:50hudsonbotCheckin
2013-04-25 01:50hudsonbotNote Added: 0058189
2013-04-25 01:50hudsonbotCheckin
2013-04-25 01:50hudsonbotNote Added: 0058190
2013-04-25 01:50hudsonbotCheckin
2013-04-25 01:50hudsonbotNote Added: 0058191
2013-04-25 01:50hudsonbotCheckin
2013-04-25 01:50hudsonbotNote Added: 0058192
2013-04-25 14:08hgbotCheckin
2013-04-25 14:08hgbotNote Added: 0058253
2013-04-25 14:08hgbotCheckin
2013-04-25 14:08hgbotNote Added: 0058254
2013-04-25 14:08hgbotCheckin
2013-04-25 14:08hgbotNote Added: 0058255
2013-04-25 15:30hgbotCheckin
2013-04-25 15:30hgbotNote Added: 0058260
2013-04-26 09:56alostaleNote Added: 0058271
2013-04-26 09:56alostaleStatusresolved => closed
2013-04-26 09:56alostaleFixed in Version => 3.0MP23
2013-04-26 23:54hudsonbotCheckin
2013-04-26 23:54hudsonbotNote Added: 0058287
2013-04-26 23:54hudsonbotCheckin
2013-04-26 23:54hudsonbotNote Added: 0058288
2013-04-26 23:54hudsonbotCheckin
2013-04-26 23:54hudsonbotNote Added: 0058289
2013-04-26 23:54hudsonbotCheckin
2013-04-26 23:54hudsonbotNote Added: 0058291
2013-05-02 16:31johnfandlIssue Monitored: johnfandl

Notes
(0052363)
AugustoMauch   
2012-09-24 17:43   
Effort: 10
Impact: high
Plan: short
(0057105)
egoitz   
2013-03-06 17:01   
A possible solution

1. Add AD_Table.AuditInserts as a Boolean
2. When AD_Table.IsFullyAudited is set to True, then AD_Table.AuditInserts should appear on the screen (with label of "Audit Insert Statements"),
defaulted to True (which matches "Fully Audited" expectation).
3. The user may set set AD_Table.AuditInserts to False to avoid adding the unnecessary insert trigger to the table in question
4. When "Update Audit Trail Infrastructure" is run, the added Trigger must NOT include the Insert operation if AD_Table.AuditInserts is False


For example, the normal audit trigger on the m_product table looks like:

CREATE TRIGGER au_m_product_trg
BEFORE INSERT OR UPDATE OR DELETE
ON m_product
FOR EACH ROW
EXECUTE PROCEDURE au_m_product_trg();

With this feature, when AD_Table.AuditInserts is False, the trigger should be as follows (same as before, except the INSERT operation is omitted):

CREATE TRIGGER au_m_product_trg
BEFORE UPDATE OR DELETE
ON m_product
FOR EACH ROW
EXECUTE PROCEDURE au_m_product_trg();

Note that there is no compelling reason to touch the logic which creates the trigger PROCEDURE itself--it doesn't matter that the Insert logic is still in
the procedure, since it will never be executed.
(0057118)
alostale   
2013-03-07 07:54   
(edited on: 2013-03-07 11:29)
The solution explained by egoitz is the one we are now thinking to implement.

The only problem with this solution is that it would not audit where the record was created from (from which window or process). To solve this, when inserting new records in audited tables, we could save a single row in audit table with the id of the new record + where it was created.
(0058120)
hgbot   
2013-04-23 12:23   
Repository: erp/devel/pi
Changeset: 0175279b4dbea5abe7e308d10a04fd86d649d625
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 10:54:40 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0175279b4dbea5abe7e308d10a04fd86d649d625 [^]

Related to issue 20422: Adds the new Insert Audits fields

This changeset contains all the changes related to the addition of the new Insert Audits field:
- Creation of the new column in the database
- Changes in the Application Dictionary (AD_COLUMN, AD_FIELD; AD_ELEMENT)
- It is a mandatory column, so it has been created in all AD_TABLES with its default value ('Y')

---
M modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.application/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.kernel/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.myob/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.datasource/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.google/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.openid/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.userinterface.selector/src-db/database/sourcedata/AD_TABLE.xml
M src-db/database/model/tables/AD_TABLE.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_ELEMENT.xml
M src-db/database/sourcedata/AD_FIELD.xml
M src-db/database/sourcedata/AD_TABLE.xml
---
(0058121)
hgbot   
2013-04-23 12:23   
Repository: erp/devel/pi
Changeset: d8c6a9c2b6acff52bc7d314b526d7d0d7bbe785c
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 11:03:37 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d8c6a9c2b6acff52bc7d314b526d7d0d7bbe785c [^]

Related to issue 20422: Updates the ad_create_audit_triggers SQL function

The ad_create_audit_triggers SQL function has been updated to use the new AD_TABLE.ISAUDITINSERTS column,

The behaviour of the triggers when a record is updated or deleted has not been changed. When a record is inserted, if the ISAUDITINSERTS column of the audit column is'Y' (default value), the behaviour will be the same as the previous one. If ISAUDITINSERTS is 'N', then only the key column of the audited table will be inserted in the audit table.

---
M src-db/database/model/postscript-Oracle.sql
M src-db/database/model/postscript-PostgreSql.sql
---
(0058122)
hgbot   
2013-04-23 12:23   
Repository: erp/devel/pi
Changeset: c33d0871c1d8c6ccef4075d90f9c99d79950fe6c
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 11:08:42 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/c33d0871c1d8c6ccef4075d90f9c99d79950fe6c [^]

Related to issue 20422: Updates AD_TABLE_MOD_TRG trigger

The AD_TABLE_MOD_TRG has been updated, so that the Updated column of the table is not changed if the only modified column is ISFULLYAUDITED or ISAUDITINSERTS.

---
M src-db/database/model/triggers/AD_TABLE_MOD_TRG.xml
---
(0058123)
hgbot   
2013-04-23 12:23   
Repository: erp/devel/pi
Changeset: c0c3d4e9feabb621a302624bdb27209da34a3770
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 11:12:29 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/c0c3d4e9feabb621a302624bdb27209da34a3770 [^]

Fixes issue 20422: Updates the AuditTrailPopup to consider show ID column

The AuditTrailPopup has been updated to show the ID column of the audit table. This is needed because now if an audited table is flagged as ISAUDITINSERTS='N', only the ID column of the table will be inserted in the audit table. Originally the popup did not show the records of the audit table belonging to its id column.

---
M src/org/openbravo/erpCommon/businessUtility/AuditTrailPopup.java
---
(0058124)
AugustoMauch   
2013-04-23 12:35   
Test plan:
As System Admin with an activated instance:
- Open Tables and Columns
- Select the C_UOM table.
- Open in form view, set the flag Fully Audited to true. Check that the flag Audit Inserts is shown and defaulted to 'Y'
- Run Update Audit Trail Infrastucture
- Open the Unit of Measure window
- Create a new record and save it. Change it and save again. The Audit Trail toolbar button should be shown (it is not shown until the record has been updated).
- Check that there is a record for each column of the C_UOM table in the INSERT section of the Audit Trail.
- Open again Tables and Columns, set the flag Audit Inserts to 'N', and run Update Audit Trail Infrastructure.
- Open Unit of Measure, create a new record and updated. Check that in the Audit Trail now the only column audited in the INSERT is the table key column
(0058125)
AugustoMauch   
2013-04-23 12:35   
Test it in Postgres and Oracle.
(0058189)
hudsonbot   
2013-04-25 01:50   
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/9fa1ecafd560 [^]

Maturity status: Test
(0058190)
hudsonbot   
2013-04-25 01:50   
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/9fa1ecafd560 [^]

Maturity status: Test
(0058191)
hudsonbot   
2013-04-25 01:50   
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/9fa1ecafd560 [^]

Maturity status: Test
(0058192)
hudsonbot   
2013-04-25 01:50   
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/9fa1ecafd560 [^]

Maturity status: Test
(0058253)
hgbot   
2013-04-25 14:08   
Repository: erp/devel/pi
Changeset: 478bd1f3ab24a447ccff0ae2d99329252d485f7c
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 14:03:29 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/478bd1f3ab24a447ccff0ae2d99329252d485f7c [^]

Related to issue 20422: Audit fields are placed in a new line

---
M src-db/database/sourcedata/AD_FIELD.xml
---
(0058254)
hgbot   
2013-04-25 14:08   
Repository: erp/devel/pi
Changeset: 334847ea1bcf6f0470a5678e497cafeaad2121b5
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 14:06:17 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/334847ea1bcf6f0470a5678e497cafeaad2121b5 [^]

Related to issue 20422: Updated callout to initialize isAuditInserts

The callout SL_TableAudit, which is called whenever the value of isFullyAudited is changed, has been updated to give a proper initial value to the isAuditInserts column:
- If at the moment there are no tables being audited with isAuditInserts='Y', it will be initialized to 'N'
- Otherwise, it will be initialized to 'Y'

---
M src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit.java
A src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit_data.xsql
---
(0058255)
hgbot   
2013-04-25 14:08   
Repository: erp/devel/pi
Changeset: 1cfc2ceb27c1be402f90a78e37c1ae6efc078f33
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 14:08:00 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/1cfc2ceb27c1be402f90a78e37c1ae6efc078f33 [^]

Related to issue 20422: isAuditInserts column should not be exported

The isAuditInserts column should not be exported, because it is an instance level configuration column

---
M modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.application/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.kernel/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.myob/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.datasource/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.google/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.openid/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.userinterface.selector/src-db/database/sourcedata/AD_TABLE.xml
M src-db/database/sourcedata/AD_DATASET_COLUMN.xml
M src-db/database/sourcedata/AD_TABLE.xml
---
(0058260)
hgbot   
2013-04-25 15:30   
Repository: erp/devel/pi
Changeset: 0136c51b6c5f0783497d7084805748f256fdce0f
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 15:28:34 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0136c51b6c5f0783497d7084805748f256fdce0f [^]

Related to issue 20422: xsql query replaced with DAL OBCriteria

---
M src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit.java
R src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit_data.xsql
---
(0058271)
alostale   
2013-04-26 09:56   
Code reviewed and tested on pi@0136c51b6c5f

Tested on Oracle and PosgreSQL

Improvement in a process doing massive insertions is from 7min to 30s
(0058287)
hudsonbot   
2013-04-26 23:54   
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/1db7e66bd5c5 [^]

Maturity status: Test
(0058288)
hudsonbot   
2013-04-26 23:54   
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/1db7e66bd5c5 [^]

Maturity status: Test
(0058289)
hudsonbot   
2013-04-26 23:54   
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/1db7e66bd5c5 [^]

Maturity status: Test
(0058291)
hudsonbot   
2013-04-26 23:54   
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/1db7e66bd5c5 [^]

Maturity status: Test