Archives of the TeradataForum

Message Posted: Wed, 08 Mar 2006 @ 13:12:11 GMT

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

This is my understanding of the different types of checkpointing taking place and the utility differences...

During Phase I of the Fastload, the rows are de-blocked and redistributed to their proper AMP, but they are not sorted in hashing order. In phase II of the Fastload, the rows are then sorted and and merged into the the actual table. There is some kind of internal checkpointing going on in this phase to keep track of which data blocks have been merged in and which have not. This is different than the checkpointing that takes place in Phase I which keeps track of which rows have been loaded from the host file. At the end of the phase II of Fastload, the fallback copy of the table is created (if fallback is specified).

In Mload, during the acquisition phase, if you have fallback on the table, two copies of the row are created and sent to the AMPs (one to the primary and one to the fallback AMP). This is one difference between Mload and Fastload. Also, during the acquisition phase, as rows are being de-blocked and sent to the appropriate AMP, they are put into the work table in hashing order just like they exist in the target table. So, at the end of the acquisition phase, there is no need for a sort (another difference between Mload and Fastload).

Once all of the rows have been put into the work table, the application phase then begins and the rows are merged from the work table into the target table. There is also checkpointing that takes place in the application phase to keep track of which rows (or data blocks) from the work table have been applied to the target table and which have not. This is different than the checkpointing that takes place in the acquisition phase, which again, keeps track of which rows have been loaded from the file.

So, the differences between the two utilities when loading into an empty table would be:

1) Fastload sends one copy of each row regardless of fallback or non-fallback and then creates the fallback copy in Phase II. Mload creates a second copy of the row if the target table is defined as fallback. (Of course, you can always alter the table to add fallback at the end of the Mload or Fastload, which would make this difference moot).

2) Fastload does a sort in Phase II to get the rows in hashing order. Mload puts the rows in hashing order in the work table as it goes along. So, there is no sort that takes place in Mload prior to merging the rows into the target table.

If you are going into a non-fallback table, I would think that #2 would be the primary advantage of using Fastload. Rather than going through the overhead of keeping the rows in the work table in order as it goes along (which will slow things down), Fastload simply gets the rows to the right AMP (in phase I) and then sorts them at the end of that process (during phase II).

Thanks,

Barry

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