Adding 4th table to join jumps processing time from seconds to over 20 minutes 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 &gt;= '2014-01-01';


It is only when I include TaskCode table that I have the problem.


Link to execution plan
Table structure and indexes


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 &gt;= '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.
SQL is not making up rows.
If that join is producing more rows than you expect then figure it out.
Are you sure ts.JobSubTypeId is a task code?
That name does not sound like a task code. 

Try this - it will show you were the volume is coming from
In your query each count is a row  

  -- this is the raw count from Timesheets
  SELECT ts.JobSubTypeId, count(*) as countRaw
  FROM JobsDB.dbo.Timesheets AS ts
  WHERE ts.WeekEnding &gt;= '2014-01-01' 
  GROUP BY ts.JobSubTypeId
  ORDER BY count(*) desc;

  -- this is the count brought in by the join to TaskCode
  SELECT ts.JobSubTypeId, count(*) as countJoin
  FROM JobsDB.dbo.Timesheets AS ts
  LEFT JOIN dbo.TaskCode AS tc
         ON tc.Code = ts.JobSubTypeId
  WHERE ts.WeekEnding &gt;= '2014-01-01' 
  GROUP BY ts.JobSubTypeId
  ORDER BY count(*) desc;

  -- this is the raw counts in TaskCode 
  SELECT tc.Code, count(*) as countCode
  FROM dbo.TaskCode AS tc
  GROUP BY tc.Code
  ORDER BY count(*) desc;


The fact that when you finally ran the second query and it ran fast is a very strong indication you have a volume and not an index issue.  That count(*) had to touch those rows.

Even if JobSubTypeId is Code that is clearly not a sufficient join   

ON tc.Code = ts.JobSubTypeId


TaskCode has a number of very detailed columns
Your are clearly bring in massive data from other projects