The Teradata Query Band – Measure your Success

1
1785

 

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 ;

Select

;

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

from dbc.dbqlogtbl

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.

Our Reader Score
[Total: 4    Average: 4/5]
The Teradata Query Band – Measure your Success written by Paul Timar on April 4, 2014 average rating 4/5 - 4 user ratings

1 COMMENT

  1. –Rather than using the LIKE in the predicate, you could use the built-in query band functions for a more flexible and extensible solution, here’s a slightly more involved example to illustrate:
    –e.g.
    — Intialise the QB for business date 2014-11-01
    SET QUERY_BAND = ‘QUERY1=VS1;Bus_Date=2014-11-01;’ FOR SESSION ;

    — Run the before SQL for business date 2014-11-01.
    SELECT blah, blah…WHERE my_bus_date = DATE’2014-11-01′;

    — Update the QB for another bus_date
    SET QUERY_BAND = ‘QUERY1=VS1;Bus_Date=2014-11-02;’ UPDATE FOR SESSION ;

    — Run the before SQL for business date 2014-11-02.
    SELECT blah, blah…WHERE my_bus_date = DATE’2014-11-02′

    — Update the QB for the tuned (after) queries
    SET QUERY_BAND = ‘QUERY1=VS2;Bus_Date=2014-11-01;’ UPDATE FOR SESSION ;

    — Run the before SQL for business date 2014-11-01.
    SELECT blah, blah…WHERE my_bus_date = DATE’2014-11-01′;

    — Update the QB for another bus-date
    SET QUERY_BAND = ‘QUERY1=VS1;Bus_Date=2014-11-02;’ UPDATE FOR SESSION ;

    — Run the after SQL for business date 2014-11-02.
    SELECT blah, blah…WHERE my_bus_date = DATE’2014-11-02′

    –Reset the QB
    SET QUERY_BAND = NONE;

    — Now go get the results – the point of the example:
    SELECT GetQueryBandValue(t1.queryband,0,’QUERY1′)
    ,GetQueryBandValue(t1.queryband,0,’Bus_Date’)
    ,t1.*
    FROM dbc.dbqlogtbl as t1
    WHERE CAST(GetQueryBandValue(t1.queryband,0,’Bus_Date’) AS DATE)
    BETWEEN date ‘2014-11-01’
    AND date ‘2014-11-03’
    AND t1.QueryBand IS NOT NULL;

    Now you have many more options than what the LIKE clauses can enable.
    Also check out:
    SYSLIB.GetQueryBandSP
    SYSLIB.GetQueryBandValueSP
    SYSLIB.QueryBandReservedName
    SYSLIB.GetQueryBand
    SYSLIB.GetQueryBandPair
    in addition to SYSLIB.GetQueryBandValue

LEAVE A REPLY

Please enter your comment!
Please enter your name here