Archives of the TeradataForum

Message Posted: Tue, 16 Oct 2001 @ 10:58:38 GMT

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

That looks fine to me. I've not seen the thread on this one so I'll assume the following:

1) The input table dwkb_aux.Current_Account_Transaction is a lot smaller than the target table dwkb.Current_Account_Transaction.

2) Both tables have an IDENTICAL layout (column sequence, data types, primary index). Even if this is the case I always explicitly code the column list in the SELECT statement. It's safer that way and is self-documenting.

3) Both have NOT NULL coded on the primary index column(s).

4) Target table has even distribution.

5) Target table has no secondary indexes.

6) No join indexes exist based on the target table.

7) Both are physical tables, not views.

I can't see how stats would help or hinder in this case. The input table has to be scanned in full and all rows have to be inserted into the target table. There is no decision making to be done.

Poor performance is very often not really what it seems. Locking conflicts, causing your job to be queued behind another job, need to be excluded from the possible causes.

You might try the following:

LOCKING dwkb.Current_Account_Transaction FOR WRITE NOWAIT
LOCKING dwkb_aux.Current_Account_Transaction FOR ACCESS NOWAIT
INSERT INTO dwkb.Current_Account_Transaction
SELECT * FROM dwkb_aux.Current_Account_Transaction;

This guarantees that you get a 'dirty read' lock on the input table (let's hope no-one else updates it at the same time) and a write lock on the target table. If neither request can be satisfied the job fails before the input table is scanned. The use of views with locking modifiers is widely used, and recommended, to avoid problems such as this.

Hope this helps,

Paul Johnson.

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