Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 Feb 2013 @ 21:46:00 GMT


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


Subj:   SQL running longer with Between function
 
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, February 26, 2013 16:09 -->

Hi Experts,

The query below running longer when I use Between date function there lot of difference like 5 sec and 8 min

Our RDBMS is 13.10 and stats are up to date.

Appreciate your help thank you.

Below took 8 min to get results (Used Between date function in where condition)

     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'
       );

Here below one taking just 5 seconds with out between function

     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  =  (date '2013-02-22')
        and
        A2K_WORKFLOW_SS.Create_User  =  'ULR8'
       );

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: 27 Dec 2016