Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0024727Openbravo ERPY. DBSourceManagerpublic2013-09-03 18:262013-09-10 18:07
Reportermarvintm 
Assigned Tomarvintm 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Toshuehner
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0024727: 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
blocks defect 0024682 closed marvintm Three different performance issues in dbsm which should be addressed 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2013-09-10 11:37shuehnerTypedefect => backport
2013-09-10 11:37shuehnerfix_in_branch => 2.50
2013-09-10 17:58hgbotCheckin
2013-09-10 17:58hgbotNote Added: 0061061
2013-09-10 17:58hgbotStatusscheduled => resolved
2013-09-10 17:58hgbotResolutionopen => fixed
2013-09-10 17:58hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/912c4ac086e00a8cc8f8b93e105e138848ac2388 [^] => http://code.openbravo.com/erp/stable/2.50/rev/416da0ef63d4fbdff5c51f6671909faeff1c1721 [^]
2013-09-10 18:07shuehnerReview Assigned To => shuehner
2013-09-10 18:07shuehnerNote Added: 0061065
2013-09-10 18:07shuehnerStatusresolved => closed

Notes
(0061061)
hgbot   
2013-09-10 17:58   
Repository: erp/stable/2.50
Changeset: 416da0ef63d4fbdff5c51f6671909faeff1c1721
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

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