Dump/Load Millions of records - OpenEdge Deployment - Forum

All Replies

Posted by LegacyUser on 23-Aug-2001 08:42

Hi Oscar,

It really depends on a number of factors;

"Oscar " wrote:

>Hi,

I need dump and load around 15,000,000 records and i

>want to know what is the way fastest.

>I have progress 8.3c on unix

>Thanks

Posted by LegacyUser on 23-Aug-2001 08:42

Hi Oscar,

It really depends on a number of factors;

"Oscar " wrote:

>Hi,

I need dump and load around 15,000,000 records and i

>want to know what is the way fastest.

>I have progress 8.3c on unix

>Thanks

Posted by LegacyUser on 23-Aug-2001 08:45

Hi, i'll get it all typed this time !

It really depends on a number of factors;

how many tables, how many processors, how much data, how much free disk space, etc.

When we dump & load 4 gig db we use 16 scripts to dump the data and then use a bulkloader followed by index rebuild.

"Oscar " wrote:

>Hi,

I need dump and load around 15,000,000 records and i

>want to know what is the way fastest.

>I have progress 8.3c on unix

>Thanks

Posted by LegacyUser on 29-Aug-2001 17:31

Thanks Keith,

I did the dump/load similary like you said, but now my problem is the "idxbuild" the problem is not the space, the problem is the time. I have one table with around 50 millions of records and the reindex has around 40 hours and is not finish yet.

Thanks.

"Keith" wrote:

>Hi, i'll get it all typed this time !

>It really depends on a number of factors;

>how many tables, how many processors, how much data, how

>much free disk space, etc.

>When we dump & load 4 gig db we use 16 scripts to dump

>the data and then use a bulkloader followed by index

>rebuild.

>"Oscar " wrote:

>>Hi,

>> I need dump and load around 15,000,000 records and i

>>want to know what is the way fastest.

>>I have progress 8.3c on unix

>>Thanks

Posted by LegacyUser on 12-Nov-2001 19:21

Hi Oscar,

In search of a faster way to dump and reload a

Progress database, I used binary dump/load on a

869Mb test db to verify/benchmark if it's really

faster than the regular dictionay dump/load.

Based on the result (Please see the tally below)

Binary dump/load process is 300% faster than the

dictionary dump/load. However the problem is the

size gets bigger.

Original database size: 869Mb

Reg. Dump: 39 mins

Bin. Dump: 15 mins

Reg. Load: 4 hrs

Bin. Load: 12 mins

Reg. Indexing: 1 hr 5 mins

Bin. Indexing: same

After Reg dump & load, DB size: 800MB

After Bin dump & load, DB size: 910MB

Regards,

Ompong Paguirigan

Posted by LegacyUser on 19-Nov-2001 07:14

Question re the speed - how many dump / load processes did you run concurrently ?

Is the increase in size caused by the sequence of tables being loaded increasing unused parts of blocks ?

"Ompong Paguirigan" wrote:

>Hi Oscar,

>In search of a faster way to dump and reload a

>Progress database, I used binary dump/load on a

>869Mb test db to verify/benchmark if it's really

>faster than the regular dictionay dump/load.

>Based on the result (Please see the tally below)

>Binary dump/load process is 300% faster than the

>dictionary dump/load. However the problem is the

>size gets bigger.

>Original database size: 869Mb

>Reg. Dump: 39 mins

>Bin. Dump: 15 mins

>Reg. Load: 4 hrs

>Bin. Load: 12 mins

>Reg. Indexing: 1 hr 5 mins

>Bin. Indexing: same

>After Reg dump & load, DB size: 800MB

>After Bin dump & load, DB size: 910MB

>Regards,

>Ompong Paguirigan

Posted by Admin on 13-Dec-2006 00:08

Hi Oscar,

I have similar issue. I have dumped and loaded a table with 7 milion records and now trying to do index rebuild but it is taking more 24 hours and still not finished.

How did you went with index rebuild? Could you help us?

Regards

Priti.

Posted by Admin on 13-Dec-2006 02:17

Have you tried the suggestions from the knowledge base?

- http://progress.atgnow.com/esprogress/resultDisplay.do?gotoLink=70146&docType=1000&contextId=15355%3A70146.70242%2C64209.64230%2C69978.70020&clusterName=CombinedCluster&contentId=1761a78c-0aa8-4557-8a23-8224b48fde06&responseId=4f2e6f6f8c3365a4%3Ab60b93%3A10f7969caf8%3A726&groupId=1&answerGroup=4&score=909&page=http%3A%2F%2Fprogress.atgnow.com%2Fesprogress%2Fdocs%2FProgress%2Fesupport.progress.com%2FEDocHTML%2FOpenEdge%2F10.0B%2Fdmadm%2Fdmadm-13-1.html&result=7&excerpt=MaximizingindexrebuildperformanceTospeedupindexrebuildoperations%2Cdothefollowing%3A&resultType=5000#Goto70146

- http://progress.atgnow.com/esprogress/resultDisplay.do?gotoLink=135&docType=1006&contextId=21826%3A135.184&clusterName=CombinedCluster&contentId=788bb29b-23c5-4994-949e-82d15b8f568f&responseId=4f2e6f6f8c3365a4%3Ab60b93%3A10f7969caf8%3A5d8&groupId=3&answerGroup=19&score=727&page=http%3A%2F%2Fprogress.atgnow.com%2Fesprogress%2Fdocs%2FSolutions%2FProgress%2FESERVER_P72607.xhtml&result=18&excerpt=Whatisthequickestwaytorunanindex+rebuild.&resultType=5002#Goto135

Posted by ChUIMonster on 13-Dec-2006 13:04

There are a lot of variables involved in optimizing a dump & load. Quite a lot depends on the hardware resources that you have available, the version of Progress being used and the characteristics of the data.

I did a presentation, DB-8 Highly Parallel Dump and Load, at Exchange 2006 that goes into some of the possibilities. It's somewhere in the PSDN library or you can go straight to the horse's mouth:

http://www.greenfieldtech.com/downloads/files/exch06db8.ppt

In a nutshell -- parallelization is your friend, IO is your enemy and avoiding the index rebuild can save a lot of time. And sometimes a 4GL based dump & load is better than binary (especially if you can easily carve the big tables into pieces and you don't care too much about scatter).

This thread is closed