15.00 - Volatile and Global Temporary Tables, Teradata Session Mode, and DML Performance - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Volatile and Global Temporary Tables, Teradata Session Mode, and DML Performance

When a session uses Teradata session semantics, you need to be aware of certain critical performance issues with the LOG/NO LOG and DELETE/PRESERVE ON COMMIT options for global temporary and volatile tables. The issues relate to whether the DML statements DELETE, INSERT, and UPDATE are grouped within explicit transaction boundaries or not. Recall that in Teradata session mode, each SQL request is treated as a transaction unless you explicitly define transaction boundaries with the BEGIN TRANSACTION and END TRANSACTION statements.

Suppose you define the following four volatile tables so you can examine 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;

If inserts are done 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);

If inserts are done 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.

Now consider the following data for deletes, inserts, and updates to the previously defined volatile tables.

 

Table Name

 

Transient Journal Step Performed?

Deletion Step Performed?

Number of All-AMPs Operations per Transaction

vt_ld

Y

Y

1

vt_nld

Y

Y

1

vt_lp

N

N

0

vt_nlp

Y

N

1

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.

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

For example, consider the following comparison test of 200 individual Teradata session mode inserts made to.

  • A permanent table.
  • A volatile table with ON COMMIT DELETE and 200 implicit transactions.
  • A volatile table with ON COMMIT DELETE and a single explicit transaction defined to encapsulate all 200 individual inserts.
  •  

    Table Type

    Transaction Boundary Semantics

    Sum(CPUTime)

    (seconds)

    Sum(DiskI/O)

    Permanent

    Implicit

                  13.49

                5,761

    Volatile

    Implicit

                  48.50

              31,889

    Explicit

                    6.03

                4,702

    These numbers are presented for comparison purposes only. Actual values depend on the configuration of your system, but the ratios between values are comparable across configurations and hold for both 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 both CPU time and disk I/O measures where other factors are held constant.