Archives of the TeradataForum

Message Posted: Tue, 04 Apr 2006 @ 12:35:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here are my findings from a year ago:

Here are my general findings concerning TPUMP, results may vary. I am currently using V2R3.

PACK SIZE: The bigger the PACK size the better. With PACK = 1, I had fewer BLOCKED sessions, but the job completion time increased. When I tried PACK size 100 I got an "insufficient memory for plastic steps" error, and reduced it to 75 than 50 to overcome it. Newer versions of TD may allow larger PACK size.

SESSION SIZE: More sessions better. Caused increase in Blocked (conflicting) Sessions, but job run time decreased. I was inserting 22,000 rows and there was a run time decrease when going from 10-30 sessions, less noticeable from 30-60 sessions.

CHECKPOINT: It appears checkpoints default to 15 minutes. Since this is a <20 minute job I removed checkpointing. May want included in longer running jobs.

SYSTEM LOAD: System load seemed to have the biggest effect on run time. Even though there was no conflict on table being updated, time required to process BLOCKED sessions appeared longer during high CPU load.

BLOCKED SESSIONS: As suggested by other users, this seems to be caused by ROW Hash collisions; 80 AMPS and 22,000 UPI updates, 50 update statements per PACK. It appears that subsequent statement packages will not be requested until all statements in pack are processed.

TARGET TABLE SIZE: Processing time did not appear to be a function of target table size. Only a function of number of Updates.

ONE LAST NEW OBSERVATION: We used TPUMP to update 6 million rows on a 350 m row table. Although this may seem extreme, since TPUMP can be stopped or crashed with a roll back of only one record, we could just start it and let it run, or stop and restart it if load required. This is much safer then a Multiload update into an existing table, although not as fast as a create-select-insert-rename into a new table.

Bill H

	<Prev	Next>		<<First	<Prev	Next>	Last>>