Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update Field with Count result extreamly slow

Author  Topic 

PeetKoekemoer
Starting Member

12 Posts
Posted - 2012-04-23 : 13:19:38
I have 2 tables.
PerformanceManagement and attendance
in the performancemanagement table I have one record per employee for a given period. In the other table do I have the employee's clocking details (containig over 17 million records). I want to update the performamcemanagement table's fields with the grouped shifts worked for the period. but this query takes for ever to execute.

Update PerformanceManagement set
UGShifts = (SELECT Count(Workdate) as UGShifts FROM Attendance a where a.ActualAttendance IN ('U')
and a.WorkDate BETWEEN PerformanceManagement.BeginDate and PerformanceManagement.EndDate
and a.IndustryNo = PerformanceManagement.IndustryNo),
SurfaceShifts = (SELECT Count(Workdate) as UGShifts FROM Attendance a where a.ActualAttendance IN ('W')
and a.WorkDate BETWEEN PerformanceManagement.BeginDate and PerformanceManagement.EndDate
and a.IndustryNo = PerformanceManagement.IndustryNo),
LeaveShifts = (SELECT Count(Workdate) as UGShifts FROM Attendance a where a.ActualAttendance IN (
'LA', 'LAC', 'LAP', 'LAU', 'LCP', 'LOC', 'LOT', 'LU','S33', 'S50', 'SE', 'SL', 'SLU')
and a.WorkDate BETWEEN PerformanceManagement.BeginDate and PerformanceManagement.EndDate
and a.IndustryNo = PerformanceManagement.IndustryNo)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-23 : 13:45:38
What indexes do you have on the two tables? Specifically do you have indexes on IndustryNo column and on the Workdate column? Also, while not ANSI standard, an extension that T-SQL allows may make it go faster - see below. I looked at your code and rewrote it with absolutely no testing at all, so please review it and convince yourself if that makes sense. If you do choose to use it, you may want to do a select rather than an update to verify that the counts you are getting are in fact correct.
UPDATE p SET 
	UGShifts = COUNT(CASE WHEN a.ActualAttendance IN ('U') THEN Workdate END),
	SurfaceShifts = COUNT(CASE WHEN a.ActualAttendance IN ('W') THEN Workdate END),
	LeaveShifts = COUNT(CASE WHEN a.ActualAttendance IN  ('LA', 'LAC', 'LAP', 'LAU', 'LCP', 'LOC', 
                                    'LOT', 'LU', 'S33', 'S50', 'SE', 'SL', 
                                    'SLU') THEN Workdate END)
FROM
	Attendance a
	INNER JOIN 	PerformanceManagement p ON
		a.IndustryNo = p.IndustryNo
WHERE
	a.WorkDate BETWEEN p.BeginDate AND p.EndDate
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2012-04-23 : 14:12:57
just to piggy back on sunitabeck's code, call me OCD


UPDATE p 
   SET UGShifts = COUNT(CASE WHEN a.ActualAttendance = N'U'  THEN Workdate END),
	   SurfaceShifts = COUNT(CASE WHEN a.ActualAttendance = N'W' THEN Workdate END),
	   LeaveShifts = COUNT(CASE WHEN a.ActualAttendance IN  ('LA', 'LAC', 'LAP', 'LAU', 'LCP', 'LOC', 
                                    'LOT', 'LU', 'S33', 'S50', 'SE', 'SL', 
                                    'SLU') THEN Workdate END)
 FROM dbo.Attendance a
INNER JOIN dbo.PerformanceManagement p 
   ON a.IndustryNo = p.IndustryNo
WHERE a.WorkDate BETWEEN p.BeginDate AND p.EndDate


<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-23 : 19:17:40
quote:
Originally posted by yosiasz

just to piggy back on sunitabeck's code, call me OCD
Heh! OCD away all you like!! That is what keeps me up during the day and lets me sleep peacefully at night
Go to Top of Page

PeetKoekemoer
Starting Member

12 Posts
Posted - 2012-04-24 : 01:31:17
Sorry no luck

Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2012-04-24 : 09:07:47
Do it as a select instead, include the primary key. Use it as a derived table, join to it, and update from there.









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

PeetKoekemoer
Starting Member

12 Posts
Posted - 2012-04-24 : 12:39:18
ok how do I do this?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-24 : 14:19:08
What Don meant is something like this:
UPDATE p SET
	p.UGShifts = c.UGShifts,
	p.SurfaceShifts = c.SurfaceShifts,
	p.LeaveShifts = c.LeaveShifts
FROM
	PerformanceManagement p
	INNER JOIN
	(
		SELECT
			a.IndustryNo,
			UGShifts = COUNT(CASE WHEN a.ActualAttendance = N'U'  THEN Workdate END),
			SurfaceShifts = COUNT(CASE WHEN a.ActualAttendance = N'W' THEN Workdate END),
			LeaveShifts = COUNT(CASE WHEN a.ActualAttendance IN  ('LA', 'LAC', 'LAP', 'LAU', 'LCP', 'LOC', 
										'LOT', 'LU', 'S33', 'S50', 'SE', 'SL', 
										'SLU') THEN Workdate END)
		FROM dbo.Attendance a
		INNER JOIN dbo.PerformanceManagement p 
				ON a.IndustryNo = p.IndustryNo
		WHERE a.WorkDate BETWEEN p.BeginDate AND p.EndDate
	) c	 ON c.IndustryNo = p.IndustryNo;
Please do test to see if the results are as expected before you use it, because I have not.
Go to Top of Page

PeetKoekemoer
Starting Member

12 Posts
Posted - 2012-04-25 : 00:28:32
thanx it worked and I checked the data (100%), I just had to add a group by a.Industryno

UPDATE p SET
p.UGShifts = c.UGShifts,
p.SurfaceShifts = c.SurfaceShifts,
p.LeaveShifts = c.LeaveShifts
FROM
PerformanceManagement p
INNER JOIN
(
SELECT
a.IndustryNo,
UGShifts = COUNT(CASE WHEN a.ActualAttendance = N'U' THEN Workdate END),
SurfaceShifts = COUNT(CASE WHEN a.ActualAttendance = N'W' THEN Workdate END),
LeaveShifts = COUNT(CASE WHEN a.ActualAttendance IN ('LA', 'LAC', 'LAP', 'LAU', 'LCP', 'LOC',
'LOT', 'LU', 'S33', 'S50', 'SE', 'SL',
'SLU') THEN Workdate END)
FROM dbo.Attendance a
INNER JOIN dbo.PerformanceManagement p
ON a.IndustryNo = p.IndustryNo
WHERE a.WorkDate BETWEEN p.BeginDate AND p.EndDate group by a.IndustryNo
) c ON c.IndustryNo = p.IndustryNo
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2012-04-25 : 10:00:39
gotta love a column named UGShift









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-04-25 : 10:08:04
quote:
Originally posted by DonAtWork

gotta love a column named UGShift


How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

You are sooooooooooo BAD!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site