Solved

Calculating time by event across multiple people in near real time

Posted on 2014-02-22
3
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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