How can the Teradata Queryband be used to measure Resource Usage?
Assume you have just created an alternative version of an SQL statement that seems to be performing better. Right as you show it to a co-worker, it is not so fast anymore regarding calendar time passed. So are you wrong about your improvement?
Here is an easy way to document the performance before and after your work.
You can wrap your alternative query versions in a query band in Teradata to generate clearly distinguishable tracks of the resource usage. The figures can be read out and used for documentation and presentation purposes quickly.
Follow these steps:
1. Ensure that your Teradata SQL Assistant session does not do any other tasks once you set the query band.
2. Configure the query band for the first version of your SQL, run the SQL and only the SQL immediately after the query band opening and end the query band immediately after the SQL returns a result.
SET QUERY_BAND = ‘QUERY1=VS1;’ FOR SESSION ;
SET QUERY_BAND=NONE FOR SESSION;
3. Perform step 2 for your second SQL version, using a different query band name, e.g. ‘QUERY1=VS2;’
4. Display and compare the resource usage results using the following summary:
sel queryband, NumResultRows, NumSteps, TotalIOCount, AMPCPUTime, ParserCPUTime, NumOfActiveAMPs, MaxCPUAmpNumber, MinAmpIO,MAxAmPIO, MaxIOAmpNumber, SpoolUsage
where trim(queryband) LIKE ‘%QUERY1=%’
and queryText LIKE ‘%SELECT%’
It might take a few minutes until Teradata writes your activity from the cache to the dbc table. So do not worry if an immediate execution of step 4 returns no rows. Plan your result presentation around this delay in order not to make your target audience impatient.
Depending on where the focus and expectations of your tuning efforts lie, one or the other measure is more important to you.
You have a hard proof of your success at hand now.