Archives of the TeradataForum

Message Posted: Fri, 20 Aug 2004 @ 13:21:51 GMT

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

Prasanna

Multiload is expecting an import file to load the data. There are reasons why you may find that a Multiload works better than an Insert/Select. There are reasons to use one or the other of course...

Your Insert/Select statement will insert rows into your table in parallel and is one of the fastest ways to load data from one table to another on the same system. This can also be one of the slowest ways to load data into a table because the Insert/Select process must use the Transient Journal for Rollback purposes. If the table that you are loading is a SET table and there are millions of rows in the table this process can be quite long. If you decide to abort the job, Rollback can take 1 1/2 to 2 times longer to recover.

This is why you might find a FastExport and a Multiload to be a better bet for large tables. Multiload does not use the transient journal, it has its own WT, ET and UV tables to work with. (you define the tables .) This means no Rollback issues. Multiload can perform and UPSERT so you can insert or update as well as delete using Multiload.

The ETL process for any warehouse must constantly be reevaluated to determine if the method for loading the data into the warehouse is still the proper method. For small tables and transformation an insert/select may be the fastest way to complete the process. As time goes by and the data rows accumulate, a change to the load process may be in order.

If the Insert/Select process is taking longer than expected, determine the row counts for the target table and how much data is being loaded and that may assist you in determining the best method for loads.

You may also want to ask the question to your EDW team whether or not the entire dataset in the target table is necessary. Sometimes you can archive months of data that are no longer required as part of the daily queries. This may speed up your data loads.

I currently have a 357 million row table that grows 1.3 million a day and an insert/select is not a good candidate for loading this table. We use Multiload and it takes about 20 minutes...

Looking forward to comments from the A-Team.

Best Regards

Chris Coffing
AFKS O&M Lead Teradata DBA
Certified Teradata Master

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