Need help in calculating the RUNTIME of my Job's ETL

• Mac1986

  SSCertifiable

  Points: 6216

  More actions

  December 22, 2012 at 10:21 pm

  #286787

  Hi All,

  I have 58 sequentially run jobs as part of our Database ETL. So, I took the start time of the 1st job and EndTime of 58th Job to calculate the totla Run time of the ETL.

  I'm using the following TSQL logic to get the time difference between the jobs but if the ETL is > than 24 hours, I'm not getting the correct difference in hours. Please suggest.

  Need to calculate the difference between 2012-12-16 20:57:01.293 and 2012-12-18 01:26:57.573

  When I use: DATEDIFF(HH,Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate) RunTimeInHours

  I get 20 Hrs

  When I use convert(varchar,(Finish.WorkQueueEndWorkDate - Start.WorkQueueStartWorkDate),108) RunTime

  I get 04:29:56

  Please help me getting the correct difference as Runtime.

• Mac1986

  SSCertifiable

  Points: 6216

  More actions

  December 23, 2012 at 3:43 pm

  #1571195

  Never mind I figure out a way to get the right Datetime...

  convert(varchar(5),DateDiff(s, Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate)/3600)+':'+convert(varchar(5),DateDiff(s, Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate)%60)) as [hh:mm:ss]

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  December 23, 2012 at 5:08 pm

  #1571198

  Mac1986 (12/22/2012)

  ---

  I'm using the following TSQL logic to get the time difference between the jobs but if the ETL is > than 24 hours...

  ... then I'd have to say that you might have a bit of a performance problem.

  If it were me, I'd add another timer to measure the duration of each of the 58 jobs and the fix the job that's taking the longest and maybe continue from there.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  December 24, 2012 at 9:57 am

  #1571315

  SELECT

  CAST(DATEDIFF(SECOND, Start.WorkQueueStartWorkDate, Finish.WorkQueueEndWorkDate) / 3600 AS varchar(3)) +

  RIGHT(CONVERT(char(8), Finish.WorkQueueEndWorkDate - Start.WorkQueueStartWorkDate, 8), 6)

  from (

  select CAST('20121216 20:57:01.293' AS datetime) AS WorkQueueStartWorkDate

  ) AS start

  cross join (

  select CAST('20121218 01:26:57.573' AS datetime) AS WorkQueueEndWorkDate

  ) as finish

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.