Archives of the TeradataForum

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

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

<-- 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>>