Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 27 Feb 2013 @ 16:49:50 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


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.



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023