Anonymous | Login
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] Y. DBSourceManagerminorhave not tried2013-09-03 18:262013-09-10 18:07
ReportermarvintmView Statuspublic 
Assigned Tomarvintm 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branch2.50Fixed in SCM revision416da0ef63d4
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toshuehner
Web browser
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo

0024727: 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 ]
blocks defect 0024682 closedmarvintm Three different performance issues in dbsm which should be addressed 

-  Notes
hgbot (developer)
2013-09-10 17:58

Repository: erp/stable/2.50
Changeset: 416da0ef63d4fbdff5c51f6671909faeff1c1721
Author: Stefan Hühner <stefan.huehner <at>>
Date: Tue Sep 10 11:38:37 2013 +0200
URL: [^]

Fixed 24727. Commit latest dbsm-jar to speedup update.database

M src-db/database/lib/dbsourcemanager.jar
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

- Issue History
Date Modified Username Field Change
2013-09-10 11:37 shuehner Type defect => backport
2013-09-10 11:37 shuehner fix_in_branch => 2.50
2013-09-10 17:58 hgbot Checkin
2013-09-10 17:58 hgbot Note Added: 0061061
2013-09-10 17:58 hgbot Status scheduled => resolved
2013-09-10 17:58 hgbot Resolution open => fixed
2013-09-10 17:58 hgbot Fixed in SCM revision [^] => [^]
2013-09-10 18:07 shuehner Review Assigned To => shuehner
2013-09-10 18:07 shuehner Note Added: 0061065
2013-09-10 18:07 shuehner Status resolved => closed

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker