By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,564 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,564 IT Pros & Developers. It's quick & easy.

load from cursor vs delete/insert

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.