Solved

Calculating time by event across multiple people in near real time

Posted on 2014-02-22
3
237 Views
Last Modified: 2014-02-22
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

0
Comment
Question by:rlarian
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39880139
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
 
LVL 4

Author Comment

by:rlarian
ID: 39880203
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
 
LVL 4

Author Closing Comment

by:rlarian
ID: 39880226
HUGE!! under 10 seconds including updates to other tables for tracking.
Can't thank you enough.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question