Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in calculating the RUNTIME of my Job's ETL Expand / Collapse
Author
Message
Posted Saturday, December 22, 2012 10:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
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.
Post #1399731
Posted Sunday, December 23, 2012 3:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
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]

Post #1399769
Posted Sunday, December 23, 2012 5:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1399772
Posted Monday, December 24, 2012 9:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 2,044, Visits: 3,059

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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1399932
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse