|View Issue Details|
|Type||Category||Severity||Reproducibility||Date Submitted||Last Update|
|backport||[Openbravo ERP] Y. DBSourceManager||minor||have not tried||2013-09-03 18:26||2013-09-10 18:07|
|Priority||normal||Resolution||fixed||Fixed in Version|
|Status||closed||Fix in branch||2.50||Fixed in SCM revision||416da0ef63d4|
|OS Version||Database version||Ant version|
|Product Version||SCM revision|
|Review Assigned To||shuehner|
|Regression introduced in release|
|Regression introduced by commit|
|Triggers an Emergency Pack||No|
0024727: Three different performance issues in dbsm which should be addressed
|Description||We'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 Reproduce||Try update.database in a database with lots of data. Notice that it can be really slow.|
|Tags||No tags attached.|
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Sep 10 11:38:37 2013 +0200
URL: http://code.openbravo.com/erp/stable/2.50/rev/416da0ef63d4fbdff5c51f6671909faeff1c1721 [^]
Fixed 24727. Commit latest dbsm-jar to speedup update.database
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
|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||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||http://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^] => http://code.openbravo.com/erp/stable/2.50/rev/416da0ef63d4fbdff5c51f6671909faeff1c1721 [^]|
|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|