Adding 4th table to join jumps processing time from seconds to over 20 minutes

Question

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.

• 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 >= '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.

Answer 1

I looked at the table structures and as Aaron had previously pointed out TimeSheets does not have any indexes and the other tables do not have any nonclustered indexes.

I created the two databases and their tables and generated the estimated execution plan and I get the same execution plan that you are currently getting. I created a clustered index on TimeSheets and some nonclustered indexes on the tables and columns that are being joined and ran the query again and am now getting index seeks, which would improve the performance.

These indexes would help this query only so depending on what other queries are done against these databases they may not be the "best choice". Also, as I don't know if these values are unique so only created non-unique nonclustered indexes.

Here are the SQL Statements I used to create the indexes

USE jobsdb;
GO
ALTER TABLE TimeSheets
ADD CONSTRAINT PK_TimeSheets_ID PRIMARY KEY CLUSTERED(Id);
CREATE NONCLUSTERED INDEX I_TimeSheets_UserId ON TimeSheets(userId);
CREATE NONCLUSTERED INDEX I_TimeSheets_JobCode ON TimeSheets(JobCode);
CREATE NONCLUSTERED INDEX I_TimeSheets_JobSubTypeId ON  TimeSheets(JobSubTypeId);
CREATE NONCLUSTERED INDEX I_TimeSheets_WeekEnd ON TimeSheets(WeekEnding);

USE JobSightDev;
GO
CREATE NONCLUSTERED INDEX I_Employee_ADUserName ON employee(ADUserName);
CREATE NONCLUSTERED INDEX I_ProjectCode_Code ON ProjectCode(Code);
CREATE NONCLUSTERED INDEX I_TaskCode_Code ON TaskCode(Code);

As with any changes this should be done in a test environment.

I also noticed that the relationships are using the actual value rather than the PRIMARY KEY from the foreign table. These are all non-integer columns so the joins become more expensive when doing the comparison. Not that you can change it but it would have been more effective to join using the Id value rather than the text value. One additional option to further improve performance is the change the clustering key from Id to ProjectCode.Code and TaskCode.Code but this should also be tested. But again this all needs to be tested

Answer 2

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 >= '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 >= '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