 |
 |
Archives of the TeradataForum
Message Posted: Wed, 27 Feb 2013 @ 16:49:50 GMT
| Subj: | | Re: SQL running longer with Between function |
| |
| From: | | Sirupa Ashok |
Dave,
Yes one is pulling one day data and another is one year data, when you see my query in where condition
A2K_WORKFLOW_SS.Create_User = 'ULR8'
If i change this user even with date between for one year again its very fast like 5 secs Only for this particular user it is taking
long.
Here is explain plan for between condition please suggest any performance improvements
Explain SELECT
A2K_WORKFLOW_SS.Policy_No,
A2K_WORKFLOW_SS.Work_Flow_Queue,
A2K_WORKFLOW_SS.Status,
A2K_CREATE_DT.calendar_date,
A2K_WORKFLOW_SS_PREVIOUS.Status,
A2K_WORKFLOW_SS_PREVIOUS.Work_Flow_Queue
FROM
PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS A2K_WORKFLOW_SS_PREVIOUS
RIGHT OUTER JOIN PROD_V.DS_VW_OPS_A2K_WF_SS_DELTA A2K_WF_SS_DELTA ON
(A2K_WF_SS_DELTA.Previous_WPS_Id=A2K_WORKFLOW_SS_PREVIOUS.WPS_Id)
RIGHT OUTER JOIN PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS A2K_WORKFLOW_SS ON
(A2K_WORKFLOW_SS.WPS_Id=A2K_WF_SS_DELTA.Current_WPS_Id)
LEFT OUTER JOIN PROD_V.DS_VW1_AVIVA_CALENDAR A2K_CREATE_DT ON
(A2K_CREATE_DT.calendar_date= cast(A2K_WORKFLOW_SS.Source_Create_Ts as date))
WHERE
(
A2K_CREATE_DT.calendar_date BETWEEN (date '2012-02-26') AND (date '2013-02-26') and
A2K_WORKFLOW_SS.Create_User = 'ULR8'
);
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock PROD_T.DS_OPS_A2K_WORKFLOW_SS in view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS for access, we lock PROD_T.DS_OPS_A2K_WF_SS_DELTA in
view PROD_V.DS_VW_OPS_A2K_WF_SS_DELTA for access, and we lock SYS_CALENDAR.CALDATES in view CALENDARTMP for access.
| |
| | 2) | Next, we do an all-AMPs RETRIEVE step from PROD_T.DS_OPS_A2K_WORKFLOW_SS in view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS by way of an all-rows
scan with a condition of ("PROD_T.DS_OPS_A2K_WORKFLOW_SS in view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS.Create_User = 'ULR8'") into Spool 2 (all_amps),
which is redistributed by the hash code of (CAST((PROD_T.DS_OPS_A2K_WORKFLOW_SS.Source_Create_Ts) AS DATE)) to ll AMPs. Then we do a SORT to
order Spool 2 by row hash. The ize of Spool 2 is estimated with high confidence to be 4,016 rows (301,200 bytes). The estimated time for this
step is 4.07 seconds.
| |
| | 3) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to SYS_CALENDAR.CALDATES in view
PROD_V.DS_VW1_AVIVA_CALENDAR by way of a RowHash match scan with a condition of ("(SYS_CALENDAR.CALDATES in view
PROD_V.DS_VW1_AVIVA_CALENDAR.cdate >= DATE '2012-02-26') AND (SYS_CALENDAR.CALDATES in view PROD_V.DS_VW1_AVIVA_CALENDAR.cdate <= DATE '2013-02-
26')"). Spool 2 and SYS_CALENDAR.CALDATES are joined using a merge join, with a join condition of ("SYS_CALENDAR.CALDATES.cdate =
(CAST(({LeftTable}.Source_Create_Ts) AS DATE))"). The result goes into Spool 3 (all_amps), which is redistributed by the hash code of
(PROD_T.DS_OPS_A2K_WORKFLOW_SS.WPS_Id, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Policy_No, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Work_Flow_Queue,
PROD_T.DS_OPS_A2K_WORKFLOW_SS.Status, SYS_CALENDAR.CALDATES.cdate) to all AMPs. Then we do a SORT to order Spool 3 by row hash. The size of
Spool 3 is estimated with index join confidence to be 21 rows (1,596 bytes). The estimated time for this step is 0.03 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows scan into Spool 4 (all_amps), which is duplicated on all AMPs. The size
of Spool 4 is estimated with index join confidence to be 2,016 rows (153,216 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to PROD_T.DS_OPS_A2K_WF_SS_DELTA in view
PROD_V.DS_VW_OPS_A2K_WF_SS_DELTA by way of an all-rows scan with no residual conditions. Spool 4 and PROD_T.DS_OPS_A2K_WF_SS_DELTA are joined
using a product join, with a join condition of ("WPS_Id = PROD_T.DS_OPS_A2K_WF_SS_DELTA.Current_WPS_Id"). The result goes into Spool 5
(all_amps), which is redistributed by the hash code of (SYS_CALENDAR.CALDATES.cdate, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Status,
PROD_T.DS_OPS_A2K_WORKFLOW_SS.Work_Flow_Queue, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Policy_No, PROD_T.DS_OPS_A2K_WORKFLOW_SS.WPS_Id) to all AMPs. Then
we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated with index join confidence to be 53 rows (2,385 bytes).
| |
| | 6) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 (Last Use) by way of a
RowHash match scan. Spool 3 and Spool 5 are left outer joined using a merge join, with a join condition of ("Field_1 = Field_1"). The result
goes into Spool 6 (all_amps), which is redistributed by the hash code of (PROD_T.DS_OPS_A2K_WF_SS_DELTA.Previous_WPS_Id) to all AMPs. Then we do
a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with index join confidence to be 53 rows (3,604 bytes). The estimated time
for this step is 3.17 seconds.
| |
| | 7) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of a RowHash match scan, which is joined to PROD_T.DS_OPS_A2K_WORKFLOW_SS in
view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS by way of a RowHash match scan with no residual conditions. Spool 6 and PROD_T.DS_OPS_A2K_WORKFLOW_SS are
left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT (Previous_WPS_Id IS NULL)"), with a join
condition of ("Previous_WPS_Id = PROD_T.DS_OPS_A2K_WORKFLOW_SS.WPS_Id"). The result goes into Spool 1 (group_amps), which is built locally on the
AMPs. The size of Spool 1 is estimated with index join confidence to be 53 rows (5,035 bytes). The estimated time for this step is 0.04 seconds.
| |
| | 8) | 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.
| |
Thank you.
| |
