15.10 - Comparing Update and Stream Operators - Parallel Transporter

Teradata Parallel Transporter User Guide

prodname
Parallel Transporter
vrm_release
15.10
category
User Guide
featnum
B035-2445-035K

Comparing Update and Stream Operators

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.

     

    Table 6: 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.