Solved

Calculating time by event across multiple people in near real time

Posted on 2014-02-22
3
236 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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