Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0024682
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] Y. DBSourceManagerminorhave not tried2013-09-03 18:262013-09-17 19:02
ReportermarvintmView Statuspublic 
Assigned Tomarvintm 
PrioritynormalResolutionfixedFixed in Version3.0MP28
StatusclosedFix in branchpiFixed in SCM revision7eada4720c21
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
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
Summary

0024682: 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.
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 0024727 closedmarvintm Three different performance issues in dbsm which should be addressed 

-  Notes
(0060901)
hgbot (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (administrator)
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 (viewer)
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

- Issue History
Date Modified Username Field Change
2013-09-03 18:26 marvintm New Issue
2013-09-03 18:26 marvintm Assigned To => marvintm
2013-09-03 18:26 marvintm Modules => Core
2013-09-03 18:26 marvintm OBNetwork customer => No
2013-09-03 18:26 marvintm Triggers an Emergency Pack => No
2013-09-03 18:29 hgbot Checkin
2013-09-03 18:29 hgbot Note Added: 0060901
2013-09-03 18:29 hgbot Status new => resolved
2013-09-03 18:29 hgbot Resolution open => fixed
2013-09-03 18:29 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^]
2013-09-04 10:03 hgbot Checkin
2013-09-04 10:03 hgbot Note Added: 0060905
2013-09-04 17:26 hgbot Checkin
2013-09-04 17:26 hgbot Note Added: 0060922
2013-09-05 16:03 hgbot Checkin
2013-09-05 16:03 hgbot Note Added: 0060949
2013-09-06 11:31 hgbot Checkin
2013-09-06 11:31 hgbot Note Added: 0060959
2013-09-10 08:26 hgbot Checkin
2013-09-10 08:26 hgbot Note Added: 0060987
2013-09-10 11:33 hgbot Checkin
2013-09-10 11:33 hgbot Note Added: 0061050
2013-09-10 11:36 shuehner Status resolved => new
2013-09-10 11:36 shuehner Resolution fixed => open
2013-09-10 11:37 shuehner Status new => scheduled
2013-09-10 11:37 shuehner fix_in_branch => pi
2013-09-10 17:55 hgbot Checkin
2013-09-10 17:55 hgbot Note Added: 0061059
2013-09-10 17:55 hgbot Status scheduled => resolved
2013-09-10 17:55 hgbot Resolution open => fixed
2013-09-10 17:55 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^] => http://code.openbravo.com/erp/devel/pi/rev/7eada4720c21a0c4c25bba169f3ee225c815f447 [^]
2013-09-10 18:07 shuehner Review Assigned To => shuehner
2013-09-10 18:07 shuehner Note Added: 0061064
2013-09-10 18:07 shuehner Status resolved => closed
2013-09-10 18:07 shuehner Fixed in Version => 3.0MP28
2013-09-17 19:02 hudsonbot Checkin
2013-09-17 19:02 hudsonbot Note Added: 0061233

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker