Scenarios Where Tpump and Mload should be used

Tools & Utilities
Not applicable

Scenarios Where Tpump and Mload should be used

Can anyone describe the diffrent situations where Tpump and Mload should be used

Re: Scenarios Where Tpump and Mload should be used

In general, the more you tend to accumulate your updates into large batches before applying them to your tables, the more likely it is that you'll want to use Mload. Mload is more efficient at applying a large number of updates. However, Mload has certain limitations like it can't update unique secondary indexes or join indexes, it can't fire triggers, and you can't use it on a table with referential integrity defined. Also, Mload will lock the entire table with a write lock when it's in the APPLY phase (when it's applying the updates).

Tpump, on the other hand, is best used if you are applying updates throughout the day in small batches (or using a queue). Tpump is not as fast, especially as the update volumes grow. It's advantages are that it doesn't lock the entire table for write, but only locks the specific row-hash values that are being updated, and it only locks them for the duration of the update. Also, since there is no special code inside the DBMS for Tpump, it supports all DBMS features (updates unique secondary indexes, join indexes, fires triggers, etc.).

If you are applying updates on a weekly or daily basis, I would tend to use Multiload. As you start to apply updates more frequently throughout the day, you may start to find that Tpump is the better option.