load from cursor vs delete/insert

[peteh]

Hi All;
I have a 2.5mm row table (very wide columns) on an UDB v8.2.3 (DPF -
6x10) AIX box. We were doing some performance testing to determine the
pros and cons of delete/insert vs load from cursor operations in our
ETL architecture (data warehouse extract/transform/load).

We expected load from cursor to be substantially faster, but found that
the delete/insert took 2 minutes and the load from cursor took 5
minutes.

The delete portion of the delete/insert test deletes all rows and the
insert performs the same SELECT as the load from cursor which does a
load/replace.

Can anyone help me understand why this might be? Is the load from
cursor passing everything through the coordinating node so I lose
parallel processing? Thanks for any insight.

Pete H

[Serge Rielau]

peteh wrote:

Hi All;
I have a 2.5mm row table (very wide columns) on an UDB v8.2.3 (DPF -
6x10) AIX box. We were doing some performance testing to determine the
pros and cons of delete/insert vs load from cursor operations in our
ETL architecture (data warehouse extract/transform/load).

We expected load from cursor to be substantially faster, but found that
the delete/insert took 2 minutes and the load from cursor took 5
minutes.

The delete portion of the delete/insert test deletes all rows and the
insert performs the same SELECT as the load from cursor which does a
load/replace.

Can anyone help me understand why this might be? Is the load from
cursor passing everything through the coordinating node so I lose
parallel processing? Thanks for any insight.

You are correct. LOAD FROM CURSOR indeed serialized at the coordinator.
You could connect to each datanode and do a partial LOAD. That woudl
give you both parallelism and LOAD speed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/