16.20 - Comparing Update and Stream Operators - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
uah1527114222342.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval

Both the Update operator and the Stream operator can be used to update data in the Teradata Database, however:

  • The Update operator locks the target tables that are being updated so that interactive data reads and writes cannot be performed concurrently.
  • The Stream operator does not lock the target tables that are being updated so that interactive read and write activities can be performed concurrently.

    This feature of the Stream operator enables it to perform update operations during periods of heavy table access by other users. Like the other Teradata PT operators, the Stream operator can use multiple sessions and multiple operator instances to process data from several data sources concurrently.

    Unlike the Load and Update operators, Stream operator does not use its own protocol to access Teradata. Rather it uses Teradata SQL protocol.

Comparing Update and Stream Operators 
Parameter Update Operator Stream Operator
Volume Performs high-volume updates against a large number of rows. Works better for low-volume real-time updates.
Performance Performance improves as the volume of updates increases. Performance improved with multi-statement requests.
Lock Granularity Bulk updates at block level. Must lock all tables, which prevents access until complete.

Rows are not available until the load job is complete.

Does not fully lock target tables during updates. Instead, uses standard SQL locking protocols to lock individual rows as updates are applied, which permits concurrent read and write access to target tables by other users.

Rows are immediately available for access once the transaction is complete.

Number of Tables No more than 5. Up to 127.
Timing Batches transactions and applies them at a higher volume, but usually at a rate that is much slower than real-time. Loads changes in near real-time.
Concurrent
Operations Requires one Teradata Database load slot against the database connected to by each Export, Load, or Update operator specified in the APPLY-SELECT statement. Does not require a Teradata Database load slot.
Instances Multiple parallel instances improve update performance. Multiple parallel instances might or might not improve performance.
Sequencing Data is processed in sequence all the time (but not in real-time). Robust mode must be used if sequencing is needed.
DML Statements Uses actual DML statements. Uses macros to modify tables rather than actual DML commands.
Work Tables Requires one work table per target table. Work tables not required.