468,117 Members | 1,571 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,117 developers. It's quick & easy.

load from cursor vs delete/insert

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

Oct 3 '06 #1
1 4618
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/
Oct 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Diego | last post: by
7 posts views Thread by jane | last post: by
2 posts views Thread by nier | last post: by
3 posts views Thread by db2udbgirl | last post: by
13 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.