Teradata slows down at end of load ??!! 2

[RanD]

We are restating a moderately large table (250 M rows).
Plan has been optimized.
Insert/Selecting into empty table, matching index(es).
We were loading about 10M rows every 5 minutes up to the last 45M rows, then the system started slowing down remarkably. Last count showed only 2M rows loaded in the last 10 minutes.

(Yes, am using locking for access to do the select).

Given an optimized plan, can anyone suggest other things we could do to ensure that the system doesn't slow down like this? We see this a lot on larger table inserts.

 

[Patten]

Just a thought (can't figure out if it is true: not enough input: DDL table etc..)

But if your table has a non unique primary index and it is a set table (which I don't know), you can have this phenomenon. Supposing there are rows with the same primary index, and you have a set table which does not allow for duplicate rows. Teradata checks for every row if it is a duplicate row or not: if not it is inserted, if it is a duplicate row it is discarded. This check happens extremely fast for unique primary indexes. If your table is defined with a non unique primary index this can take longer. The bigger the amount of rows the longer the check is: it has to check with all the rows already inserted. Typically your inserts slow down after a while.

Solutions:
1. Off course: define a unique primary index.
2. If 1 is not possible: make your table multiset which allows for duplicate rows. This way the check won't happen.
After the loading you can write a check on duplicate rows if you want to avoid duplicate rows. Something like this:

CREATE MULTISET TABLE yourtable;
INSERT INTO yourtable SELECT * FROM othertable;
SELECT primaryindexfield, COUNT(*)
FROM yourtable
HAVING COUNT(*) > 1;

If the having count select finds rows, you have duplicate rows.

 

[RanD]

Thanks, that was not the problem. . .

We determined that the problem was the indexing strategy.
Knew it was dicey, did the best we could, that wasn't good enough apparently.