| Author |
 Topic  |
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
 Posted - 04/23/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/23/2012 : 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
|
 |
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 04/23/2012 : 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 |
Edited by - yosiasz on 04/23/2012 14:13:09 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/23/2012 : 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  |
Edited by - sunitabeck on 04/23/2012 19:18:06 |
|
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
Posted - 04/24/2012 : 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.
|
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
Posted - 04/24/2012 : 12:39:18
|
ok how do I do this? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/24/2012 : 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. |
|
|
|
PeetKoekemoer
Starting Member
South Africa
11 Posts |
Posted - 04/25/2012 : 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 |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
| |
Topic |
|
|
|
Update Field with Count result extreamly slow
