Calculating time by event across multiple people in near real time

MSSQL 2k8
This works, but it is very slow. Takes 1:43 to run on just 1 event. There are up to 200 events.


TIMECLOCK holds all timing events including meetings, breaks and lunches
MAPS maps users to events and the last event date/time
REASONS hold all reason codes
REASONSALLOWED hold the codes to filter out time events (we don't count off clock or lunch)

The goal is to remove 'AND MAPS.eventID = 464' and have this run against all events and calculate the time accumulated since the 'lastEvent' on a per user/event basis.

I'd like this job to run every minute if possible, but in it's current form it'll take 5 hours to run once.

SELECT	TIMES.personID, MAPS.eventID,
		CONVERT(TIME,DATEADD(s,SUM(DATEDIFF(SECOND, TIMES.startTime, TIMES.endTime) ),0)) as duration
FROM	(
		SELECT	TIMES1.personID, TIMES1.eventtype, TIMES1.reasoncode, TIMES1.eventdatetime,
				TIMES1.eventdatetime as startTime, 
				ISNULL((SELECT MIN(TIMES2.eventdatetime) 
					FROM TIMECLOCK TIMES2 
					WHERE	TIMES2.eventdatetime > TIMES1.eventdatetime
							and TIMES2.personID = TIMES1.personID ),GETUTCDATE()) as endTime    
		FROM	TIMECLOCK TIMES1 
		) TIMES 
	JOIN MAPS on MAPS.personID = TIMES.personID AND MAPS.active = 1
	JOIN REASONS  on REASONS.eventtype = TIMES.eventtype AND REASONS.reasoncode = TIMES.reasoncode
WHERE	DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), TIMES.eventdatetime) > MAPS.lastEvent
		AND REASONS.reasonID in (SELECT reasoncode FROM REASONALLOWED)
		AND MAPS.eventID = 464
GROUP BY TIMES.personID,  MAPS.eventID
ORDER BY MAPS.eventID, duration DESC 

Open in new window

LVL 4
rlarianAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
One thing I see that immediately comes to mind is this:

WHERE      DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), TIMES.eventdatetime) > MAPS.lastEvent

Can you change it to the reverse ?

WHERE       MAPS.lastEvent  <= DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), TIMES.eventdatetime)

Whenever you use a function like that on the left side of the where my understanding is that it invalidates all indexes.

So if MAPS.lastevent is an indexable column, it won't be using that index
0
 
rlarianAuthor Commented:
ok, huge improvement. That and actually creating an index on the lastevent. down to 8 seconds. I'm going to try to run against the whole thing - crossing fingers.
0
 
rlarianAuthor Commented:
HUGE!! under 10 seconds including updates to other tables for tracking.
Can't thank you enough.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.