Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 28 Feb 2013 @ 19:52:07 GMT


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


Subj:   Re: SQL running longer with Between function
 
From:   Sirupa Ashok

Dave,

Here is explain plan for single date please suggest any performance improvements..

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 execute the following steps in parallel.  
   
  1) 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 built locally on the AMPs. Then we do a SORT to order Spool 2 by the hash code of (CAST((PROD_T.DS_OPS_A2K_WORKFLOW_SS.Source_Create_Ts) AS DATE)). The size of Spool 2 is estimated with high confidence to be 4,022 rows (301,650 bytes). The estimated time for this step is 4.08 seconds.
 
   
  2) We do a single-AMP RETRIEVE step from SYS_CALENDAR.CALDATES in view PROD_V.DS_VW1_AVIVA_CALENDAR by way of the unique primary index "SYS_CALENDAR.CALDATES in view PROD_V.DS_VW1_AVIVA_CALENDAR.cdate = DATE '2013-02-22'" with no residual conditions into Spool 3 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 3 by the hash code of (SYS_CALENDAR.CALDATES.cdate). The size of Spool 3 is estimated with high confidence to be 96 rows (1,632 bytes). The estimated time for this step is 0.00 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 Spool 3 (Last Use) by way of a RowHash match scan. Spool 2 and Spool 3 are joined using a merge join, with a join condition of ("cdate = (CAST(({LeftTable}.Source_Create_Ts) AS DATE))"). The result goes into Spool 4 (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 4 by row hash. The size of Spool 4 is estimated with index join confidence to be 1 row (76 bytes). The estimated time for this step is 0.02 seconds.  
  4)We do an all-AMPs RETRIEVE step from Spool 4 by way of an all-rows scan into Spool 5 (all_amps), which is duplicated on all AMPs. The size of Spool 5 is estimated with index join confidence to be 96 rows (7,296 bytes). The estimated time for this step is 0.01 seconds.  
  5)We do an all-AMPs JOIN step from Spool 5 (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 5 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 6 (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 6 by row hash. The size of Spool 6 is estimated with index join confidence to be 3 rows (135 bytes).  
  6)We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 6 (Last Use) by way of a RowHash match scan. Spool 4 and Spool 6 are left outer joined using a merge join, with a join condition of ("Field_1 = Field_1"). The result goes into Spool 7 (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 7 by row hash. The size of Spool 7 is estimated with index join confidence to be 3 rows (204 bytes). The estimated time for this step is 2.34 seconds.  
  7)We do an all-AMPs JOIN step from Spool 7 (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 7 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 3 rows (285 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