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

Question

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

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mac1986 SSCertifiable Points: 6216 More actions · Answer 1

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: 1004664 More actions · Answer 2

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: 101207 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".