Focal Point Banner

As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at and provide your corporate email address, company, and name.

Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Bulk Loader vs Insert From Memory?

Read-Only Read-Only Topic
[SOLVED] Bulk Loader vs Insert From Memory?
Gold member

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,

WebFocus 8.104
Windows 7 Entreprise, SP1
Posts: 82 | Location: Abbotsford BC | Registered: March 15, 2010Report This Post
posted Hide Post

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.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005Report This Post
posted Hide Post
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.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
posted Hide Post
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.

Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] Bulk Loader vs Insert From Memory?

Copyright © 1996-2020 Information Builders