Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculating time by event across multiple people in near real time

Posted on 2014-02-22
3
Medium Priority
?
241 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 23

Accepted Solution

by:
Steve Wales earned 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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