To understand SQL query performance in Teradata, various parameters come into play. I believe AMPCPUTime, TotalIOCount, and SpoolUsage are the primary indicators to assess SQL query performance.
When executing multiple queries sequentially in Teradata, it is not always accurate to assume that the query taking the longest time is the worst. However, utilizing three specific parameters makes it possible to determine the worst query in most cases. This information can be obtained from two tables in DBC: DBQLOGTBL and DBQLSQLTBL. To obtain SQL Query Stats, use the following query:
SEL
TB1.queryband,TB1.NumResultRows,TB1.NumSteps,TB1.TotalIOCount,
TB1.AMPCPUTime,TB1.ParserCPUTime,TB1.NumOfActiveAMPs,TB1.MaxCPUAmpNumber,
TB1.MinAmpIO,TB1.MAxAmPIO,TB1.MaxIOAmpNumber,TB1.SpoolUsage,
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
FROM
DBC.DBQLOGTBL TB1
INNER JOIN
DBC.DBQLSQLTBL TB2
ON
TB1.QueryID = TB2.QueryID
AND
TB1.ProcID = TB2.ProcID
AND
TB1.SessionID=12345;
The columns can be adjusted according to your needs. However, the ones listed above are crucial in determining query performance in Teradata. If the AMPCPUTIME is high, it is necessary to fine-tune your query for optimal performance.
Consider these three factors when executing the query above:
SQL query results may not be immediately visible due to a brief delay in transferring query information to DBQL tables.
The query above may take a significant amount of time to produce results due to inadequate indexing on both tables. Upon inspecting both tables’ primary index columns, we note that the ProcID and CollectTimeStamp are identical. However, the CollectTimeStamp value may differ for the same query in either table, making joining by the second column unwise. Consequently, leveraging the primary index is not possible, resulting in an extended query time.
To obtain the SessionID, execute the SEL SESSION command within the session where you execute your queries.
Avoid labeling the query with the longest execution time as the worst. Inspect the query DBQL statistics to personally identify the query with the poorest performance.
Related Services
⚡ Need Help Optimizing Your Data Platform?
We cut data platform costs by 30–60% without hardware changes. 25+ years of hands-on tuning experience.
Explore Our Services →📋 Considering a Move From Teradata?
Get a personalized migration roadmap in 2 minutes. We have migrated billions of rows from Teradata to Snowflake, Databricks, and more.
Free Migration Assessment →
Query logging has indeed to be turned on, but as far as I know, the cache is automatically flushed regularly, or am I wrong on this?
FROM The docs:
”
Before Teradata Database writes DBQL data to Data Dictionary tables, it holds the data in cache until either:
1.The cache is full.
2.You end query logging.
3.The number of seconds has elapsed that you define in the DBS Control utility field DBQLFlushRate.
Note: You can select a rate from 1 to 3,600 seconds.However,Teradata recommends a flush rate of at least 10 minutes (600 seconds), which is the default. Less than 10 minutes can impact performance.
4.You flush the DBQL cache manually.
“
You will need to start query logging then end it to flush the cache, or you will get nothing using your query