 |
 |
Archives of the TeradataForum
Message Posted: Sat, 03 Apr 2010 @ 20:34:30 GMT
| Subj: | | Performance Tunning and Explain |
| |
| From: | | Ahmad, Syed |
Hello Guys,
Have any one done any performance tunning. Please let me know your views for good performance. The below query is taking much time in showing
the result but once we are joining the below four derived tables it takes much more time. I am not sure what to do.
explain SELECT D_SET_CURR_BEG.SET_YR,
MIN (D_SET_CURR_BEG.SET_BEGIN_YR_DT) SET_CURRENT_BEG_DT
FROM ABC.D_DT D_SET_CURR_BEG
WHERE D_SET_CURR_BEG.SET_BEGIN_YR_DT(FORMAT 'YYYY')
(CHAR(4))= D_SET_CURR_BEG.SET_YR
GROUP BY D_SET_CURR_BEG.SET_YR;
Explanation
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct ABC."pseudo table" for read on a RowHash to prevent global deadlock for ABC.D_SET_CURR_BEG.
| |
| | 2) | Next, we lock ABC.D_SET_CURR_BEG for read.
| |
| | 3) | We do an all-AMPs SUM step to aggregate from ABC.D_SET_CURR_BEG by way of an all-rows scan with a condition of
("(ABC.D_SET_CURR_BEG.SET_BEGIN_YR_DT (CHAR(4), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY'))=ABC.D_SET_CURR_BEG.SET_YR") , grouping by
field1 (ABC.D_SET_CURR_BEG.SET_YR). Aggregate Intermediate Results are computed globally, then placed in Spool 3. The size of Spool 3 is
estimated with no confidence to be 12 rows (300 bytes). The estimated time for this step is 0.06 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the
AMPs. The size of Spool 1 is estimated with no confidence to be 12 rows (348 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.06 seconds.
| |
| |
