I am trying to join a total of 4 tables together. When I only join 3 of the tables together I get a result set of about 220,000 records in about 2 seconds, but as soon as I throw the 4th table in, the script will run for an extremely long time (over 20 minutes during one test) and will return million of rows.
I can't figure out why adding this last table causes this performance hit and all these extra rows to show up. As far as I can tell none of the rows are duplicates, but I have only spot checked, and never been able to run the script to completion.
SELECT e.ID AS EmployeeID,
NULL AS TimesheetHeaderID,
ts.StartDateTime AS StartDateTime,
ts.EndDateTime AS EndDateTime,
pc.ID AS ProjectID,
tc.ID AS TaskID,
ts.[Description] AS WorkDescription,
ts.UserID AS Name,
ts.WeekEnding AS WeekEnding
FROM JobsDB.dbo.Timesheets AS ts
INNER JOIN dbo.Employee AS e
ON e.ADUserName = ts.UserId
INNER JOIN dbo.ProjectCode AS pc
ON pc.Code = ts.JobCode
INNER JOIN dbo.TaskCode AS tc
ON tc.Code = ts.JobSubTypeId
WHERE ts.WeekEnding >= '2014-01-01';
It is only when I include TaskCode
table that I have the problem.
There could be a task with code "Admin" a dozen times because a dozen projects have an "Admin" task.
I have no idea why Timesheets
is a heap. It is the old database I am migrating info from, and was created in the manner of "just make it work and don't care about doing it right".
I have triple confirmed the join between Code
and JobSubTypeId
. Looking at the schema for Timesheets the Id
column is not a PK, so I could possibly make it one.
I noticed something that might work and generated this code:
SELECT e.ID AS EmployeeID,
NULL AS TimesheetHeaderID,
ts.StartDateTime AS StartDateTime,
ts.EndDateTime AS EndDateTime,
pc.ID AS ProjectID,
tc.ID AS TaskID,
ts.[Description] AS WorkDescription,
ts.UserID AS Name,
ts.WeekEnding AS WeekEnding
FROM JobsDB.dbo.Timesheets AS ts
INNER JOIN dbo.Employee AS e
ON e.ADUserName = ts.UserId
INNER JOIN dbo.ProjectCode AS pc
ON pc.Code = ts.JobCode
INNER JOIN dbo.TaskCode AS tc
ON tc.Code = ts.JobSubTypeId
AND tc.ProjectID = pc.ID --Works but not all rows return
WHERE ts.WeekEnding >= '2014-01-01';
This code works, mostly. With it the query runs in seconds and returns 163,222 rows of data. Not as much as the 3 table join so I need to dig though the data to see if this is really working and I am just missing some task codes or if this is a false positive.