2

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.

  • 4
    Last time I saw something like this it was because the join was incomplete or wrong. Double check that ON tc.Code = ts.JobSubTypeId is correct and sufficient to join the two tables. – Kenneth Fisher Jun 6 '16 at 21:54
2

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.

Execution Plan

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

  • See my answer. OP finally tested it and the second aggregation ran just fine. This a volume problem. count(*) had to evaluate those rows. – paparazzo Jun 9 '16 at 19:01
  • I think there are two parts to this problem. The first is the number of records being returned as part of the join, which you addressed in your answer. Unfortunately, this cannot be controlled. The second part is the efficiency of how the query is executed, which was OP's primary concern, which I attempted to address through my response. It certainly isn't efficient to have to do a table or clustered index scan. I don't think one answer is more correct than the other they are just different ways of looking at the problem, which IMO is a good thing. – Adam Jun 9 '16 at 19:25
  • Cool, not arguing, but I don't agree with "this cannot be controlled". And I don't think your answer is incorrect. You answer adds value. Very strong indication the query is invalid and returning false rows. You fix the return count problem and you fix the problem. Maybe count is not the OPs primary concern but it should be. First return the proper rows and if that proper query is slow then fix that. Again second aggregation in my answer is fast - there is very strong indication this is not an index problem. – paparazzo Jun 9 '16 at 19:33
  • Not meaning to pick on you but look at taskcode - it has taskID, WorkDescription, UserID, and WeekEnding fields. It is is very detailed table. Is it not a simple id to description lookup. ON tc.Code = ts.JobSubTypeId is clearly an overly broad relationship. OP added AND tc.ProjectID = pc.ID and got performance but wrong answer. The correct join is very likely to have good response. – paparazzo Jun 9 '16 at 19:45
  • I'm not following your logic around your comments for TaskCode. For the join ON tc.Code = ts.JobSubTypeId all I was saying is that that these are both varchar columns and joining on strings is less efficient than on integers. Also, it doesn't make a whole log of sense to have the value of Code stored in JobSubTypeId. Rather it would make more sense for JobSubTypeId to be the value of TaskCode.Id. However, as these are separate databases a foreign key cannot be enforced. As for your previous comments I do agree that the number of records being returned needs to be validated. – Adam Jun 9 '16 at 20:07
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.