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?)

This message has been edited. Last edited by: FP Mod Chuck,

Max

I found this in the DM User Guide. It probably isn't 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.

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:
1. Using MODIFY with a SQL SET LOADONLY, was going to take about 4 hours;
2. Using SQL INSERT instead of MODIFY was going to take about 2 hours;
3. 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.

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 it's 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.