How to Measure SQL Query Performance in Teradata ?

Before we actually start with SQL Performance Tuning we should know how to measure performance of SQL Query. The most common answer we get is the Query which takes most time is the worst and we should optimize it.

This should not hold good for all the cases. There can be various reasons possible for long running queries which you can read in this post. Now coming back to the question if it is not the real time in seconds, minutes or hours then what should be  the deciding criteria to determine query eligible for SQL optimization ?

The first thing you should check is AMPCPUTIME.  This is time taken by AMPs used by query in CPU seconds. This is different than your real life time units. Higher the AMPCPUTIME means AMPs have worked more on processing the query and used more CPU seconds.

The other parameters you can check for is TotalIOCount. This is total I/O used by the query. If you are reading data from disk a lot then this will be high. One possible reason could be if you are not retrieving data efficiently from the disk. Now we know the parameters which can help in determining suitable candidates for SQL Query Optimization.

Next question is where to find these parameters ? For this you have to query DBQLogTbl & DBQLSQLTBL tables in DBC. You can use below query to get the result:

SELECT
TB1.AMPCPUTime,TB1.SpoolUsage,TB1.TotalIOCount,
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=(select session);

The query may take sometime to show the results. Also it is advisable to check with DBA team to understand the cache flushing mechanism used in Teradata. This means it may take up to 10 mins for your query to reflect in Query Log. Generally it is kept @ 10 minutes.