DWHPro Insights

Improving Teradata Load Performance: Choosing the Right Loading Method

By Roland Wenzlofsky ·

Improving Teradata Load Performance: Choosing the Right Loading Method

What influences the Teradata Load Performance?

Concurrent bulk-load limits are configuration- and utility-dependent; inspect the current workload and utility settings rather than assuming a fixed 10–99 range.

Further requests will be added to a workload queue once the utility limit has been reached, causing our loading run times to be unpredictable.

Selecting the appropriate loading method per table is crucial for achieving maximum Teradata load efficiency.

Bulk loads for huge tables

Depending on the number of rows and columns, transactional loading methods (such as BTEQ and TPump) may be preferable over bulk load utilities (such as Fastload, Multiload, and TPT Bulk Load) with varying data types in our tables.

Bulk load utilities are particularly advantageous when dealing with larger tables as they can load whole data blocks simultaneously rather than row by row. This advantage is even greater when used in conjunction with NOPI tables, as it eliminates the need for the sorting phase required in transactional loads.

Learn the proper utilization of NOPI tables through this article.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Transactional loads for small tables

For rollbacks, utilize the transient journal when executing transactional loads. I suggest this approach for small tables comprising only a few thousand rows. Opting for transactional loads instead of bulk loads is more desirable for small tables due to the increased stability of load times. Additionally, this approach frees up utility slots for larger tables that require them.

It’s important to consider that bulk load utilities run multiple parallel sessions. Logging during these sessions can significantly increase the time it takes to load data if the number of rows being loaded is small.

Teradata Load Performance

Finding incorrectly selected load methods

Start with a slow load that affects a real delivery deadline. Establish whether time is spent waiting to start, transferring rows, applying changes, or waiting for competing work. A single total duration does not identify the cause.

Build a reliable load timeline

  1. Identify one job and attempt. Record the utility, target tables, submitting user, scheduler run and restart attempt. Keep the utility output and the scheduler’s start and completion records.
  2. Separate waiting from execution. Compare submission, actual execution and completion times. Check utility limits, workload queues and blocking before concluding that the loading method is slow.
  3. Check the logging coverage. Ask your DBA which DBQL rules, thresholds and utility-specific logs apply. A missing request in a filtered query log does not prove that the utility phase did not run.
  4. Validate the correlation. Use the documented identifiers available for the installed utility and database release. Reconcile one complete load and one restart against the utility output before producing a history report. A session can contain multiple requests and jobs; SessionID alone is not a safe job-pairing key.
  5. Compare like-for-like loads. Record rows and bytes, table design, target state, utility settings and competing work. Repeat a candidate change under comparable conditions and reconcile the resulting data before treating it as an improvement.

Why the old SQL example was removed: it paired BEGIN/END records using only SessionID, which could match unrelated jobs. Its output was not a verified load history. No replacement correlation query is presented here as tested; the required identifiers and logging depend on your utility and release.

Use the Teradata load-utility comparison to choose candidates for the workload, and the DBQL guide to understand the available query evidence. For a running investigation, the Viewpoint walkthrough explains how to separate query states and check monitoring coverage.

Here is a link to the official Teradata documentation for NOPI tables:

Teradata Documentation: NOPI Tables

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

6 thoughts on “Improving Teradata Load Performance: Choosing the Right Loading Method”

  1. SELECT
    U.LOGDATE,
    U.USERNAME,
    U.UTILITYNAME,
    count(*) as Utility_Count
    FROM
    PDCRINFO.DBQLUtilityTbl_HST U
    WHERE LOGDATE > DATE-7
    AND USERNAME = ‘service_abc_xyz_Etl’
    and UTILITYNAME in
    (
    ‘FASTEXP’,
    ‘FASTLOAD’,
    ‘TPTLOAD’,
    ‘MULTLOAD’,
    ‘TPTUPD’,
    ‘TPTEXP’
    )
    GROUP BY 1,2,3
    ;

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam