Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0024682Openbravo ERPY. DBSourceManagerpublic2013-09-03 18:262013-09-17 19:02
Reportermarvintm 
Assigned Tomarvintm 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0MP28 
Merge Request Status
Review Assigned Toshuehner
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0024682: Three different performance issues in dbsm which should be addressed
DescriptionWe've found three different performance issues in dbsm which should be addressed:

- Currently, the oncreatedefault expressions are being tested in a dummy select to verify whether the oncreatedefault can be safely executed or not. This select is not limited, so if the oncreatedefault affects a big table, then it's very slow.

- Currently, update.database executes statements to ensure that "on delete cascade" foreign keys can be activated successfully. These statements were designed to prevent the problem from having tables from having records which point to AD records which no longer exist. However, currently these statements are executed for all tables, even for those which have nothing to do with AD tables.

- In update.database, all foreign keys are dropped and recreated. This is really not needed, because this was needed only to update the Application Dictionary tables. This could be improved by disabling just the AD tables, and the foreign keys of non-AD tables which point to the AD.
Steps To ReproduceTry update.database in a database with lots of data. Notice that it can be really slow.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on backport 0024727 closed marvintm Three different performance issues in dbsm which should be addressed 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2013-09-03 18:26marvintmNew Issue
2013-09-03 18:26marvintmAssigned To => marvintm
2013-09-03 18:26marvintmModules => Core
2013-09-03 18:26marvintmOBNetwork customer => No
2013-09-03 18:26marvintmTriggers an Emergency Pack => No
2013-09-03 18:29hgbotCheckin
2013-09-03 18:29hgbotNote Added: 0060901
2013-09-03 18:29hgbotStatusnew => resolved
2013-09-03 18:29hgbotResolutionopen => fixed
2013-09-03 18:29hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^]
2013-09-04 10:03hgbotCheckin
2013-09-04 10:03hgbotNote Added: 0060905
2013-09-04 17:26hgbotCheckin
2013-09-04 17:26hgbotNote Added: 0060922
2013-09-05 16:03hgbotCheckin
2013-09-05 16:03hgbotNote Added: 0060949
2013-09-06 11:31hgbotCheckin
2013-09-06 11:31hgbotNote Added: 0060959
2013-09-10 08:26hgbotCheckin
2013-09-10 08:26hgbotNote Added: 0060987
2013-09-10 11:33hgbotCheckin
2013-09-10 11:33hgbotNote Added: 0061050
2013-09-10 11:36shuehnerStatusresolved => new
2013-09-10 11:36shuehnerResolutionfixed => open
2013-09-10 11:37shuehnerStatusnew => scheduled
2013-09-10 11:37shuehnerfix_in_branch => pi
2013-09-10 17:55hgbotCheckin
2013-09-10 17:55hgbotNote Added: 0061059
2013-09-10 17:55hgbotStatusscheduled => resolved
2013-09-10 17:55hgbotResolutionopen => fixed
2013-09-10 17:55hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^] => http://code.openbravo.com/erp/devel/pi/rev/7eada4720c21a0c4c25bba169f3ee225c815f447 [^]
2013-09-10 18:07shuehnerReview Assigned To => shuehner
2013-09-10 18:07shuehnerNote Added: 0061064
2013-09-10 18:07shuehnerStatusresolved => closed
2013-09-10 18:07shuehnerFixed in Version => 3.0MP28
2013-09-17 19:02hudsonbotCheckin
2013-09-17 19:02hudsonbotNote Added: 0061233

Notes
(0060901)
hgbot   
2013-09-03 18:29   
Repository: erp/devel/dbsm-main
Changeset: 912c4ac086e00a8cc8f8b93e105e138848ac2388
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Tue Sep 03 18:29:22 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^]

Fixed issue 24682. Three performance improvements have been added:
- Now oncreatedefault testing expressions will be executed with a LIMIT statement, to improve performance.
- Now 'DELETE FROM' statements executed for 'on delete cascade' foreign keys will be issued only for those foreign keys which are either part of an AD table, or point to an AD table.
- Now only foreign keys for AD tables, or which point to an AD table, will be dropped and recreated.

---
M src/org/apache/ddlutils/Platform.java
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
M src/org/openbravo/ddlutils/task/AlterDatabaseDataAll.java
---
(0060905)
hgbot   
2013-09-04 10:03   
Repository: erp/devel/dbsm-main
Changeset: 1ee907d016ed101cdda96d64c258cfd4d696e96f
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Wed Sep 04 10:03:01 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/1ee907d016ed101cdda96d64c258cfd4d696e96f [^]

Related to issue 24682. Fixed compilation problems

---
M src/org/apache/ddlutils/platform/maxdb/MaxDbBuilder.java
M src/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java
M src/org/apache/ddlutils/platform/mysql/MySqlBuilder.java
M src/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java
M src/org/apache/ddlutils/platform/sybase/SybaseBuilder.java
M src/org/openbravo/ddlutils/task/AlterXML2SQL.java
---
(0060922)
hgbot   
2013-09-04 17:26   
Repository: erp/devel/dbsm-main
Changeset: bb0a0fbcfe5a23939efabac53ac783d5befdcf7d
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Wed Sep 04 17:26:02 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/bb0a0fbcfe5a23939efabac53ac783d5befdcf7d [^]

Related to issue 24682. Cleanup of some unneeded methods. Added Operation exception to unsupported feature.

---
M src/org/apache/ddlutils/Platform.java
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/openbravo/ddlutils/task/AlterDatabaseDataMod.java
---
(0060949)
hgbot   
2013-09-05 16:03   
Repository: erp/devel/dbsm-main
Changeset: 253902e2aa5a4d15c005d13a60d886b62bc8a16a
Author: Antonio Moreno <antonio.moreno <at> openbravo.com>
Date: Thu Sep 05 16:03:48 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/253902e2aa5a4d15c005d13a60d886b62bc8a16a [^]

Related to issue 24682. The FK performance fix will now also work in Oracle.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
---
(0060959)
hgbot   
2013-09-06 11:31   
Repository: erp/devel/dbsm-main
Changeset: 64ee18562b3af655e0ab7041e51ae109addf41df
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Fri Sep 06 11:30:25 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/64ee18562b3af655e0ab7041e51ae109addf41df [^]

Issue 24682: Simplifiy implemenation.
The code for disabling/enabling the datasetFK's was nearly identical
on postgres & oracle. Make it completely identical and then pull up
the implementation into the PlatformImplBase superclass removing the
duplicate code.
As a nice side-effect the changes done earlier to make some functions from
SqlBuilder (and all its subclasses) are not longer needed and have been
reverted.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/maxdb/MaxDbBuilder.java
M src/org/apache/ddlutils/platform/mssql/MSSqlBuilder.java
M src/org/apache/ddlutils/platform/mysql/MySqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
M src/org/apache/ddlutils/platform/sapdb/SapDbBuilder.java
M src/org/apache/ddlutils/platform/sybase/SybaseBuilder.java
---
(0060987)
hgbot   
2013-09-10 08:26   
Repository: erp/devel/dbsm-main
Changeset: 21dd58e127a8b6dbbcdcd6bfc5d1f60a9c2c985f
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Sep 10 08:26:32 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/21dd58e127a8b6dbbcdcd6bfc5d1f60a9c2c985f [^]

Issue 24682: Fix limit clause construction

---
M src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
---
(0061050)
hgbot   
2013-09-10 11:33   
Repository: erp/devel/dbsm-main
Changeset: c21df20ed7b11b77b81d53a90a7067e0e1dc3153
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Sep 10 11:33:22 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/c21df20ed7b11b77b81d53a90a7067e0e1dc3153 [^]

Issue 24682: Skip validation of onCreateDefault sql's for the most common cases
which are known to be fine statically without executing them.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
---
(0061059)
hgbot   
2013-09-10 17:55   
Repository: erp/devel/pi
Changeset: 7eada4720c21a0c4c25bba169f3ee225c815f447
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Sep 10 11:36:05 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/7eada4720c21a0c4c25bba169f3ee225c815f447 [^]

Fixed 24682. Fixed 24713. Speed up install.source & update.database
Commit jar containing the fixed for those two issues

---
M src-db/database/lib/dbsourcemanager.jar
---
(0061064)
shuehner   
2013-09-10 18:07   
Reviewed and tested on pi rev: 7eada4720c21.
Main changes are 3:
- speed up validation of sql for oncreatedefaults, final version does skip check completely in a few (but very common) cases. When the validation is still ran as a select statement add 'limit 1' or equivalent on oracle to not scan all the sourcetable which is very slow + can easily run out of memory.
- The next two changes are related, During one part of update.database the AD-data in the tables is updated. as only ad tables are touched the only foreign key's for ad-tables are needed to be deactivated, not the fk's for all existing tables in the database. Thus also only fk's for those ad-tables need to be recreated.
- the third changes is the code which ensures that all fk's of type ondelete:cascade can be activated by deleting non-conforming data. As now only fk's for ad-tables are dropped/re-created that code that also be restricted to ad-tables now.

Review did not show code ran in that code-block which touches non-ad tables.

Testing done: to ecxercise the modified code-path.
- start with 2.50-pg (mp45) and update to latest 2.50-tip and run db-consistency test afterwards
- start with 2.50-pg (mp45) and run update.database to update to pi-tip (latest 3.0) and run db-consistency test afterwards.
- (empty) update.database without any special changes on postgres and oracle against pi-tip and run consistency test
- start with latest 2.50 tip on oracle and update again to latest pi-tip and run consistency test
(0061233)
hudsonbot   
2013-09-17 19:02   
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/1c53d622fc50 [^]

Maturity status: Test