DWHPro Insights

Teradata Queue Tables: Managing Live Events for Faster Business Responses

By Roland Wenzlofsky ·

Teradata Queue Tables: Managing Live Events for Faster Business Responses

Introduction

In modern business, prompt response to opportunities and issues is crucial.

Detect and collect business events for prompt processing, generating either an alert for a front-line user or an update message for an operational system.

Teradata parallel database triggers handle live events precisely.

A dashboard function checks the queue table for significant events every X minutes, filtering out unimportant ones and identifying a single serious shipper delay that could impact profitability. This is accomplished through the use of Teradata triggers, stored procedures, and queue tables that are straightforward to program and connect to production applications.

How does a particular Queue table work?

It resembles typical base tables but possesses the distinct characteristic of operating as an asynchronous FIFO queue.

The first queue-table column must have the documented queue insertion timestamp attributes, including TIMESTAMP and its CURRENT_TIMESTAMP default; QITS is the conventional name, not the requirement by itself.

The column values in the queue table indicate the insertion time of rows, unless the user provides alternative values.

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

car queue

To add data to a database table, utilize an INSERT statement that functions as a first-in, first-out push.

car queue2

The SELECT statement below operates similarly to a peek in a FIFO queue.

car queue3car queue4

You can then use a SELECT AND CONSUME statement, which operates as a FIFO pop:

Data is returned from the row with the oldest timestamp in the specified queue table.
The row is deleted from the queue table, guaranteeing that the row is processed only once.

car queue5car queue6

We perform a peek operation to review the previous pop. The outcome is accurate: the initial record was properly consumed.

car queue7

If no rows are available, the transaction enters a delay state until one of the following actions occur:
• A row is inserted into the queue table
• The transaction aborts, either as a result of direct user intervention, such as the ABORT statement, or indirect user intervention, such as a DROP TABLE statement on the queue table.

Queue Tables and Performance

We must remain vigilant about the Queue tables.

1. Each time the system performs a DELETE, MERGE, or UPDATE operation on a queue table, the FIFO cache for that table is spoiled. The next INSERT or SELECT AND CONSUME request performed on the table initiates a full‑table scan to rebuild the FIFO cache, which impacts performance. So, you should code DELETE, MERGE, and UPDATE operations only sparingly, and these should never be frequently performed operations.

2. Current Database Limits lists 2,211 FIFO runtime-cache row entries. The former fixed 100-entry claim is not current. SELECT AND CONSUME removes a row transactionally; external exactly-once processing still requires commit, retry and crash handling.

3. To optimize the distribution of your queue tables across the PEs, consider creating them all simultaneously.

Limitations on Queue Tables

Creation or modification may not:

have Permanent Journals
Remember that the purpose of a permanent journal is to maintain a sequential history of all changes made to the rows of one or more tables, and
protect user data when users commit, un-commit, or abort transactions.
See article: Teradata Permanent Journal
contain any Large Object(LOB) data
contain References or Foreign Keys

Questions?

If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.

The transcribed queue DDL/inserts executed, and SELECT AND CONSUME returned the oldest row (CAR_ID 1001), leaving two rows. This verifies the fixture only; external processing and crash recovery were not tested.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

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.

4 thoughts on “Teradata Queue Tables: Managing Live Events for Faster Business Responses”

  1. Hi Jiri, you’re welcome, thank you for the question!
    Teradata uses QITS column to maintain the FIFO ordering of rows in the queue table.
    The queue table FIFO cache row entry is in fact a pair of QITS and rowID values for each row to be consumed from the queue, sorted in QITS value order. So, yes, for instance when you execute the query “Select and consume top 1 from queue_table” the system is able to retrieve the queue table row at the top of the FIFO queue from a single AMP.

    Reply
  2. Hi Stelvio, thank you very much for your reply! I’ve got a follow-up question, though. In which way does the FIFO cache affect the query performance of queue tables? Does it contain, for example, statistic-like information on QTIS column so the Teradata could perform a single-AMP data retrieval?

    Reply
  3. Thank you very much indeed Jiri.
    Basically the Queue FIFO cache purpose is improve the performance for consuming rows from queue tables. The FIFO caches are created by the system during startup, and hold row information for a number of non‑consumed rows for each queue table. The Dipsatcher is responsible for maintaining this cache.

    Reply
  4. Hi Stelvio, great article! Just a question regarding the FIFO cache. What is its purpose and what does it contain? Thanks! 🙂

    Reply

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