Hi. It has been my experience that these two load types have...

[Manoj Chaurasia]

Max

I found this in the DM User Guide. It probably isnt the detail you were hoping for

Insert records from memory. Speeds the loading of the data target by inserting a block

of rows at once. You can set the row interval to commit or write transactions and the

number of records to load in a block. This option is only available for relational

databases that support it, including Db2 on i V6R1 for CLI, Db2 on z, Informix, MS SQL

Server, MySQL, Oracle, Sybase ASE, Teradata 13, and UDB.

This option:

Requires clean input data. If any row in the block causes a data source constraint

violation, such as not null or unique index, the entire block is rejected.

Does not provide row counts (the number of records inserted or rejected) in the

detail log or statistics. NA (not available) will appear instead.

Does not provide record logging. For example, rejected records cannot be written to

a flat file for review.

Bulk load utility via a disk file and Extended Bulk Load Utility. Use database bulk

loaders instead of iWay to insert data into a target. DataMigrator automates bulk

loading for Hyperstage, Ingres, Informix, Microsoft SQL Server, IBM Db2, Teradata,

Nucleus, Oracle, Sybase Adaptive Server Enterprise, and Sybase Adaptive Server IQ.

You can set format version, maximum number of errors, first and last row to copy,

packet size, and row interval to commit or write transactions.

[Manoj Chaurasia]

About a year ago, I had to replace all the data in one of our oracle table (replacing about 8 million rows with 20 million rows). In testing, this is what I found:

 

Using MODIFY with a SQL SET LOADONLY, was going to take about 4 hours;

Using SQL INSERT instead of MODIFY was going to take about 2 hours;

Using the Oracle bulk loader, literally took about 4 seconds. It ran so fast I thought something had gone wrong. I spent 30 minutes verifying all the data loaded even though the log file from the loader told me everything was fine.

 

jgelona

[Manoj Chaurasia]

Insert Records from Memory and Bulk Load via Disk File do indeed have different performance characteristics and they depend on the database. In most circumstances and for most relational databases (and for all column stores) Bulk Load provides the best load time.

Your signature shows you are using WF 8.1.04 which corresponds to DataMigrator 7.7.06 which was GA in October 2014. Is that still correct If so I recommend using the current production release WF 8.2.06/7.7.10.

There have been many improvements to load performance (and other areas) in recent years.

A new Optimize Load capability for when the source and target tables are both in the same relational database generates a MERGE command for the databases that support which does all processing in the database for greatly improved performance.

A new Direct Bulk Load capability for when the source is a file and its already in the format required by the target database or column store reads directly from tat file without an extract step which improves performance in this specialized usage.

Clif Kranish

[Manoj Chaurasia]

Hi.

It has been my experience that these two load types have different behavior, i.e. one taking twice as long as the other to create a table, or the bulk loader being picker than insert from memory.

Does anyone know more about the specific conditions in which one is better than the other (Whether in theory or in practice)

Max Nevill