Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0024116Openbravo ERPY. DBSourceManagerpublic2013-06-18 11:222013-06-25 13:26
Reportershuehner 
Assigned Toshuehner 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0MP25Fixed in Version3.0MP25 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0024116: Data loading in create.database could be much faster
DescriptionThe data loading part (sourcedata,referenceddata) of create.database could be much faster by doing 3 changes:

- using batched inserts of data (helps on pgsql + oracle)
- not creating,dropping,recreating all foreign keys (helps on oracle)
- not creating,dropping,recreating not null constraints (helps on oracle)
Steps To Reproduce-
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2013-06-18 11:22shuehnerNew Issue
2013-06-18 11:22shuehnerAssigned To => shuehner
2013-06-18 11:22shuehnerModules => Core
2013-06-18 11:22shuehnerOBNetwork customer => No
2013-06-18 11:22shuehnerTriggers an Emergency Pack => No
2013-06-18 11:23shuehnerReview Assigned To => marvintm
2013-06-18 11:23shuehnerTarget Version => 3.0MP25
2013-06-18 11:31shuehnerNote Added: 0059479
2013-06-19 16:52hgbotCheckin
2013-06-19 16:52hgbotNote Added: 0059532
2013-06-19 16:52hgbotStatusnew => resolved
2013-06-19 16:52hgbotResolutionopen => fixed
2013-06-19 16:52hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/2d1d5234a73335d80a4f9fbb54f1c2216d26090b [^]
2013-06-19 17:14hgbotCheckin
2013-06-19 17:14hgbotNote Added: 0059533
2013-06-19 17:14hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/2d1d5234a73335d80a4f9fbb54f1c2216d26090b [^] => http://code.openbravo.com/erp/devel/pi/rev/649cce2ca1eb7c16886e230ed5e1e6cafd7d158d [^]
2013-06-20 02:08hudsonbotCheckin
2013-06-20 02:08hudsonbotNote Added: 0059596
2013-06-25 13:26marvintmStatusresolved => closed
2013-06-25 13:26marvintmFixed in Version => 3.0MP25

Notes
(0059479)
shuehner   
2013-06-18 11:31   
Some performance numbers for running 'create.database' on some example machine (real pc, no vm)
before-pgsql: 10min
after-pgsql: 5min
before-oracle: 22min
after-oracle: 10min

Observed speedup with database on EBS (ec2) volumes is much less but still noticable in the few minute range.
(0059532)
hgbot   
2013-06-19 16:52   
Repository: erp/devel/dbsm-main
Changeset: 2d1d5234a73335d80a4f9fbb54f1c2216d26090b
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Wed Jun 19 13:46:37 2013 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/2d1d5234a73335d80a4f9fbb54f1c2216d26090b [^]

Fixed 24116: speedup create.database
- Do not create + then drop again not null constraints for all tables
  Instead do not create them.
  Implemented by optionally marking columns at nullable in the part of the
  code creating the SQL for creating a table + activating that code from the
  SqlBuilder.createTables function. Secondly in that same function remove
  the deletion of the not null constraints as they are not created anymore.
- Do not create all foreign keys and then disable them before data loading to
  enable them afterwards again.
  Instead to not create any foreign keys from the SqlBuilder.createTables
  functions. Only after data loading create all foreign keys using a new
  helper function Platform.createAllFKs
- Enable Batched data insertion when loading data. The infrastructure for this
  was already present but needed a few fixes to work properly.
  - Add code for handling (updated,updatedby,created,createdby) columns in same
    way as that code was already present for the non-batched insertion.
  - Move calling end() method to after data loading but before doing any action
    on the loaded data as only end() will do the last inserts when using batching.
  - Move out trigger/fk handling from the start()/end() methods. Instead let
    the caller (create.database) handle those actions explicitely to make them
    more visible and to have exact control over when running them.

---
M src/org/apache/ddlutils/Platform.java
M src/org/apache/ddlutils/io/DataToDatabaseSink.java
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/openbravo/ddlutils/task/CreateDatabase.java
---
(0059533)
hgbot   
2013-06-19 17:14   
Repository: erp/devel/pi
Changeset: 649cce2ca1eb7c16886e230ed5e1e6cafd7d158d
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Wed Jun 19 13:49:28 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/649cce2ca1eb7c16886e230ed5e1e6cafd7d158d [^]

Fixed 24116: speedup create.database
- Do not create + then drop again not null constraints for all tables
- Do not create all foreign keys and then disable them before data loading to
  enable them afterwards again.
- Enable Batched data insertion when loading data.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0059596)
hudsonbot   
2013-06-20 02:08   
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/5caf871044ad [^]

Maturity status: Test