DWHPro Insights

Efficiently Load Tables with Teradata Fast Path INSERT/SELECT: Requirements and Advantages

By Roland Wenzlofsky ·

Efficiently Load Tables with Teradata Fast Path INSERT/SELECT: Requirements and Advantages

Fast Path INSERT/SELECT efficiently loads a single table.

Requirements for Teradata Fast Path INSERT /SELECT

  • Fast-path empty-target eligibility and AMP-local identical-PI optimization are separate issues. Compatible PIs/maps can avoid row redistribution, but do not imply zero BYNET control traffic or guarantee every other optimization.
  • The target table is empty.
  • Only INSERT statements can be included in the request.

How does the Fast Path INSERT/SELECT work?

This reduces access to mass storage (such as hard disk or solid-state disk).

The process involves generating complete data blocks on every AMP and subsequently transferring them to mass storage using a single IO.

Fast-path empty-target eligibility and AMP-local identical-PI optimization are separate issues. Compatible PIs/maps can avoid row redistribution, but do not imply zero BYNET control traffic or guarantee every other optimization.

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

The rows of the target and source tables are sorted in ascending order on the same AMP and according to the same rowhash within the data blocks.

Each AMP can locally prepare data blocks and write them to mass storage using a single IO.

Teradata Fast Path
Data Blocks are assembled in parallel on each AMP and written to the target table

How does BTEQ handle the Fast Path INSERT/SELECT?

When using Fast Path to load multiple tables into an empty target table, you must strictly follow the specific syntax required by BTEQ.

The semicolon must be at the beginning (from the 2nd table) of the line (not at the end of the lines!):

INSERT INTO Customer SELECT * FROM Customer1
;INSERT INTO Customer SELECT * FROM Customer2
;INSERT INTO Customer SELECT * FROM Customer3
;INSERT INTO Customer SELECT * FROM Customer4;

Advantages of the Fast Path INSERT/SELECT

  • Fast-path can reduce journal and block-processing work under its documented restrictions. The current fixture loaded four rows with four INSERT SELECT statements; it did not measure journal records, physical I/O or rollback speed.
  • ROLLBACK is done immediately without delay
  • Fast-path can reduce journal and block-processing work under its documented restrictions. The current fixture loaded four rows with four INSERT SELECT statements; it did not measure journal records, physical I/O or rollback speed.
  • Fast-path empty-target eligibility and AMP-local identical-PI optimization are separate issues. Compatible PIs/maps can avoid row redistribution, but do not imply zero BYNET control traffic or guarantee every other optimization.

BTEQ multistatement requests require a final terminator after the last SELECT as well as the displayed request-grouping syntax. Preserve those boundaries when adapting to another client API.

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.

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