Archives of the TeradataForum

Message Posted: Fri, 09 Jul 2004 @ 09:40:12 GMT

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

Hi all

I have a weekly querry which takes a long time give answer

Can anybody tell me how to optimize it for a response time under 10 sec

It takes around 45 sec now

The query give weekly aggregates! When given a date it gives the weekly aggregate fof the week containing the date

     SELECT  Sum( VCCART_LOADFCTR.TotalScheduledWorkHours) As
     TotalScheduledWorkHours,Sum(
     VCCART_LOADFCTR.AbsencePaidMedicalIncidental) As
     AbsencePaidMedicalIncidental,Sum(
     VCCART_LOADFCTR.AbsPaidMedicalIncidental_prct) As
     AbsPaidMedicalIncidental_prct,Sum(
     VCCART_LOADFCTR.AbsencePaidMedicalDisability) As
     AbsencePaidMedicalDisability,Sum(
     VCCART_LOADFCTR.AbsPaidMedicalDisability_prct) As
     AbsPaidMedicalDisability_prct,Sum( VCCART_LOADFCTR.AbsencePaidMedical)
     As AbsencePaidMedical,Sum( VCCART_LOADFCTR.AbsencePaidMedical_percent)
     As AbsencePaidMedical_percent,Sum( VCCART_LOADFCTR.AbsencePaidPersonal)
     As AbsencePaidPersonal,Sum( VCCART_LOADFCTR.AbsencePaidPersonal_percent)
     As AbsencePaidPersonal_percent,Sum( VCCART_LOADFCTR.AbsencePaidTotal) As
     AbsencePaidTotal,Sum( VCCART_LOADFCTR.AbsenteeismPaid_percent) As
     AbsenteeismPaid_percent,Sum(
     VCCART_LOADFCTR.AbsenceUnpaidMedicalIncidental) As
     AbsenceUnpaidMedicalIncidental,Sum(
     VCCART_LOADFCTR.AbUnpaidMedicalIncidental_prct) As
     AbUnpaidMedicalIncidental_prct,Sum(
     VCCART_LOADFCTR.AbsenceUnpaidMedicalDisability) As
     AbsenceUnpaidMedicalDisability,Sum(
     VCCART_LOADFCTR.AbUnpaidMedicalDisability_prct) As
     AbUnpaidMedicalDisability_prct,Sum(
     VCCART_LOADFCTR.AbsenceUnpaidMedical) As AbsenceUnpaidMedical,Sum(
     VCCART_LOADFCTR.AbsenceUnpaidMedical_percent) As
     AbsenceUnpaidMedical_percent,Sum( VCCART_LOADFCTR.AbsenceUnpaidPersonal)
     As AbsenceUnpaidPersonal,Sum(
     VCCART_LOADFCTR.AbsenceUnpaidPersonal_percent) As
     AbsenceUnpaidPersonal_percent,Sum( VCCART_LOADFCTR.AbsenceUnpaidTotal)
     As AbsenceUnpaidTotal,Sum( VCCART_LOADFCTR.AbsenteeismUnpaid_percent) As
     AbsenteeismUnpaid_percent,Sum( VCCART_LOADFCTR.AbsenceTotal) As
     AbsenceTotal,Sum( VCCART_LOADFCTR.AbsenteeismTotal_percent) As
     AbsenteeismTotal_percent,Sum( VCCART_LOADFCTR.PersonalDaysUnionPaid) As
     PersonalDaysUnionPaid,Sum(
     VCCART_LOADFCTR.PersonalDaysUnionPaid_percent) As
     PersonalDaysUnionPaid_percent,Sum(
     VCCART_LOADFCTR.PersonalDaysUnionUnpaid) As PersonalDaysUnionUnpaid,Sum(
     VCCART_LOADFCTR.PersonalDaysUnionUnpaid_prct) As
     PersonalDaysUnionUnpaid_prct,Sum(
     VCCART_LOADFCTR.PersonalDaysUnionTotal) As PersonalDaysUnionTotal,Sum(
     VCCART_LOADFCTR.AbsenteeismUnionNegotiate_prct) As
     AbsenteeismUnionNegotiate_prct
     FROM    VCCART_LOADFCTR
     WHERE   cast(VCCART_LOADFCTR.reporttime As date)  >=(
     Select  (cast('2004/07/01' As date) -(day_of_week-1)) -7
     From    sys_calendar.calendar
     Where   calendar_date = cast('2004/07/01' As date ))
             AND     cast(VCCART_LOADFCTR.reporttime As date) <=(
     Select  (cast('2004/07/01' As date) + (7- day_of_week)) -7
     From    sys_calendar.calendar
     Where   calendar_date = cast('2004/07/01' As date ))
             AND     (Extract(hour
     From    VCCART_LOADFCTR.ReportTime )>= 08
             Or      Extract( hour
     From    VCCART_LOADFCTR.ReportTime) <=16)

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