Global Temp Tables with Tactical Applications?

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

I know of a Teradata site that chose to use global temporary tables (GTTs) with their tactical queries in an attempt to get good performance against their fact table. However, there are some known limitations in doing that which I want to share with you. Read this posting to find out why mixing GTTs and tactical queries should not be your first choice.

What is a Global Temporary Table?

A GTT is a structure that is defined once in the data dictionary.  Any number of sessions can insert into and reference their own local copy of the global table structure. Volatile temp tables (VTT), by way of contrast, are created and used only within the context of a single session, and are never written to the dictionary.

Be careful of using GTTs with tactical queries because the query plans that they produce will not be able to be re-used by queries from other sessions.  In other words, you’ll never get cached plans.  This is true even when the SQL that references them is in a macro and you have parameterized the SQL correctly. 

Cached plans are a key performance enabler for tactical applications, as they allow short, time-sensitive requests to side-step most parsing engine activity. Parsing and optimizing CPU for very short queries is sometimes larger than the AMP CPU to execute the request.  Tactical queries perform more consistently when their plans have been cached.

Why GTT Plans Are Not Cached

You won’t get cached plans when using GTTs because each session will have a different table ID for the GTT, and that table ID is not known until the query is parsed. One prerequisite of getting a re-usable, cacheable query plan, is that all table IDs in the query must be consistent across all executions.  This will never be true of tables that reference global temporary tables.

In addition, there is slightly more overhead when GTTs execute than would be the case with VTTs or regular permanent tables. The DBC.Next table must be processed by the first query in a session that uses a GTT, in order to get the table ID that that session will utilize.  Since DBC.Next has only one row, access from multiple sessions will be single-threaded.  VTTs, however, have no need to go to DBC.Next table to get a table ID, as they use Spool ID instead, and Spool ID is available locally.

2 Comments
Enthusiast
Carrie, We have tactical applications that each need their own copy of a work table to accumulate and return customer specific data. There can be 20+ instances of the process running at an given time - each for a different customer. We use GTTs to accumulate the data. After reading your blog, I am concerned, but don't know what a good alternative would be. Blocking would occur on a physical table. Would you suggest a volatile table? Other options?

Thanks for your insight and suggestions.
Teradata Employee
There are a couple of considerations here.

First, if NOT having cached plans (you won't get cached plans using GTTs) is NOT causing you performance issues, then there is probably no need to change your approach. However, if the parsing engine times for these queries (derived from DBQL) is contributing to the total tactical query elapsed time exceeding expectations often, then it might be time for a change.

In terms of coming up with a way to use multiple copies of physical tables instead of GTTs, if I were you I'd post a question on the Teradata Forum or the Masters Forum if you are a Teradata Master, and see what suggestions might be out there for how to do that in an efficient way. If you came up with a way to re-use different physical copies of the same work table there would be no blocking conflict.

In terms of the potential for blocking on the DBC.Next table with GTTs, that's a pretty slight overhead, and unless all the queries are started at the same moment in time, it is unlikely to be an issue for you. I would assume that your 20+ concurrent queries have their start times spread out, reducing any contention on DBC.Next if you continue on with GTTs.

Using VTTs won't offer much benefit in terms of caching plans, as VTTs will use their own local-to-the-session spool ID as the table identifier. Because this can vary query by query, re-usable plans will not be produced.

Thanks, -Carrie