Several parameters can help us in understanding SQL Query Performance in Teradata.
I consider AMPCPUTime, TotalIOCount, and SpoolUsage as three main parameters to determine SQL Query performance.
Say you are executing multiple queries in Teradata sequentially. You might think the query which took the most time is weak, but this may not be true for all the cases. However, if you refer to above mentioned three parameters to decide the worst query, you will be correct for most cases.
Two tables in DBC give us this required information: DBQLOGTBL and DBQLSQLTBL.
To get SQL Query Stats, you can use the below-mentioned query:
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
TB1.QueryID = TB2.QueryID
TB1.ProcID = TB2.ProcID
You can add or remove columns per your requirement. However, the highlighted ones are essential for determining any Query Performance in Teradata.
If the AMPCPUTIME is high, you must tune your query to ensure it performs well.
Three points to consider while running the query mentioned above:
a) You may not see results immediately after running your SQL queries. There are a few minute delays when query information comes to DBQL tables.
b) The query mentioned above may take some time to give output. The reason is the ‘NOT SO PROPER’ Index columns for these two tables. When we check the PRIMARY INDEX columns for both tables, we observe that the PI is the same. Both the tables have ProcID, CollectTimeStamp as PI. However, the value for CollectTimeStamp can differ for the same query in both tables. Hence joining by the second column is not advisable. Therefore, you cannot ultimately leverage PI; thus, the query may need a long time to give results.
c) To get the SessionID, just run SEL SESSION; command in the same session you are running your queries.
So, never say that query that took the maximum time is the worst. Fetch the Query DBQL stats and check the worst query yourself.