Volatile and Global Temporary Tables, Teradata Session Mode, and DML Performance - Advanced SQL Engine - Teradata Database

For sessions that use Teradata session semantics, be aware of certain critical performance issues with the LOG/NO LOG and DELETE/PRESERVE ON COMMIT options for global temporary and volatile tables. In Teradata session mode, each SQL request, such as DELETE, INSERT, and UPDATE, is treated as a transaction unless you explicitly define transaction boundaries with the BEGIN TRANSACTION and END TRANSACTION statements.

Because the implicit transaction semantics of Teradata session mode treat each SQL request as an individual transaction, a large number of all-AMPs operations can impact on overall CPU and Disk I/O performance unless you explicitly group DML requests into explicit transactions.

The following four volatile tables provide an example the performance issues associated with logging and committing various table update operations.

CREATE VOLATILE TABLE vt_ld LOG (
  column_1 INTEGER
  column_2 INTEGER)
ON COMMIT DELETE ROWS;

CREATE VOLATILE TABLE vt_lp LOG (
  column_1 INTEGER
  column_2 INTEGER)
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE vt_nld NO LOG (
  column_1 INTEGER
  column_2 INTEGER)
ON COMMIT DELETE ROWS;

CREATE VOLATILE TABLE vt_nlp NO LOG (
  column_1 INTEGER
  column_2 INTEGER)
ON COMMIT PRESERVE ROWS;

For tables defined with NO LOG or ON COMMIT DELETE ROWS, each journaling (LOG) step makes an entry to empty the contents of its table in case of an abort. This is an all-AMPs operation.

Each deletion step deletes the contents of its table when a transaction ends. This is an all-AMPs operation that occurs for ON COMMIT DELETE ROWS tables.

Transient journaling steps for volatile and global temporary tables are generally generated once per table per transaction containing a DELETE, INSERT, or UPDATE operation.

Deletion steps for volatile and global temporary tables are generated once per each transaction that contains a DELETE, INSERT, or UPDATE operation.

For example, consider the following volatile table definitions.

    CREATE VOLATILE TABLE vt1 (
      f1 INTEGER,
      f2 INTEGER)
    ON COMMIT DELETE ROWS;

    CREATE VOLATILE TABLE vt2 (
      f1 INTEGER,
      f2 INTEGER)
    ON COMMIT DELETE ROWS;

    CREATE VOLATILE TABLE vt3 (
      f1 INTEGER,
      f2 INTEGER)
    ON COMMIT DELETE ROWS;

For inserts to these tables using a multistatement request in Teradata session mode like the following, the overhead is only 1 transient journal step and 1 delete step.

    INSERT vt1 (1,1)
    ;INSERT vt2 (1,1)
    ;INSERT vt1 (2,2)
    ;INSERT vt3 (1,1)
    ;INSERT vt2 (2,2);


For inserts to these tables using a single statement request in an explicit transaction like the following, the overhead is 1 journal step for each table and 1 delete step for all of them.

    BT;
     INSERT vt1 (1,1);  ← 1 transient journal step for vt1
     INSERT vt2 (1,1);  ← 1 transient journal step for vt2
     INSERT vt1 (2,2);
     INSERT vt3 (1,1);  ← 1 transient journal step for vt3
     INSERT vt2 (2,2);
    ET;                 ← 1 delete step for  all  NO LOG or ON COMMIT
                            DELETE ROWS volatile tables
                           referenced in the transaction.

The deletes, inserts, and updates to the volatile tables result in following activity:

If the column labeled Transient Journal Step Performed? is yes, the operation is performed once per table per transaction.

If the column labeled Deletion Step Performed? is yes, the operation is performed once per transaction.

The following comparison of 200 individual Teradata session mode inserts are made to a permanent table and a volatile table. The volatile table was updated to add 200 inserts with ON COMMIT DELETE for:

• 200 implicit transactions.
• A single explicit transaction defined to encapsulate all 200 individual inserts.

These numbers are presented for comparison purposes only. Actual values depend on the configuration of your system. The ratios between values are comparable across configurations for volatile and global temporary tables. Note that the performance increment for the explicit transaction boundary case over implicit transactions is roughly an order of magnitude for CPU time and disk I/O measures where other factors are constant.