0024116: Data loading in create.database could be much faster

Notes
(0059479) shuehner (administrator) 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 (developer) 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 (developer) 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 (developer) 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

Issue History
Date Modified	Username	Field	Change
2013-06-18 11:22	shuehner	New Issue
2013-06-18 11:22	shuehner	Assigned To	=> shuehner
2013-06-18 11:22	shuehner	Modules	=> Core
2013-06-18 11:22	shuehner	Triggers an Emergency Pack	=> No
2013-06-18 11:23	shuehner	Review Assigned To	=> marvintm
2013-06-18 11:23	shuehner	Target Version	=> 3.0MP25
2013-06-18 11:31	shuehner	Note Added: 0059479
2013-06-19 16:52	hgbot	Checkin
2013-06-19 16:52	hgbot	Note Added: 0059532
2013-06-19 16:52	hgbot	Status	new => resolved
2013-06-19 16:52	hgbot	Resolution	open => fixed
2013-06-19 16:52	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/dbsm-main/rev/2d1d5234a73335d80a4f9fbb54f1c2216d26090b [^]
2013-06-19 17:14	hgbot	Checkin
2013-06-19 17:14	hgbot	Note Added: 0059533
2013-06-19 17:14	hgbot	Fixed in SCM revision	http://code.openbravo.com/erp/devel/dbsm-main/rev/2d1d5234a73335d80a4f9fbb54f1c2216d26090b [^] => http://code.openbravo.com/erp/devel/pi/rev/649cce2ca1eb7c16886e230ed5e1e6cafd7d158d [^]
2013-06-20 02:08	hudsonbot	Checkin
2013-06-20 02:08	hudsonbot	Note Added: 0059596
2013-06-25 13:26	marvintm	Status	resolved => closed
2013-06-25 13:26	marvintm	Fixed in Version	=> 3.0MP25

Relationships [ Relation Graph ] [ Dependency Graph ]