Archives of the TeradataForum

Message Posted: Tue, 12 Feb 2002 @ 14:12:20 GMT


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

Subj:		Re: Insert data into table

From:		Dieter N�th

Well, my problem is the follow:

I had 300.000.000 of rows in a table and I load 300000 every day on it. This table is the source for another ten tables. Our load process taked 6 hours.

That's rather slow

How many rows/value avg/max?

How do you load:

Inserts using MultiLoad?

FastLoad + Insert/Select?

We had a problem with the data and we needed rebuild a month, near 9000000 of rows. The strategy was create another table, correct the data in that table, copy the original correct data on a second table and copy data of the first table to second table, rename the second table how the original table.

Did you copy in a multi-statement?

In BTEQ the position of the semicolon is important:

insert into target sel * from source1 where...
;insert into target sel * from source2;

Both inserts will be processed as a single statement, so there's only an entry in the transient journal indicating the table was empty, no need to journal the 9000000 records. If there's an error rollback will be easy, just empty the table again.

But if you submitted

insert into target sel * from source1 where...;
insert into target sel * from source2;

The first insert is without journal, the second one is a new transaction inserting in a table with date, so all 9000000 records are journalled. Rollback will be very lengthy...

(In Queryman it's F9 vs. F5)

We did this with all our tables (11), the consecuence was that our load process taked 18 hours. For this situation we decided delete 150.000.000 of rows, but our load process take 8 hours.

Looks like NO multi-statement or a lot of duplicate row checks.

I don't know what is the problem, i have recollect statistics and i can't reduce the hours. I think that the problems is produced in INSERT/SELECT instructions. The programs are the same in all the cases.

What can I do? I need to reduce the hours, six is my fist goal, less is better.

If you provide more info about your system, DDL and data demographics, you'll probably get help here ;-)

Dieter


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

Attachments

Library

Quick Reference