Inner Join kills query performance

Question

There are 2 queries below that are identical except for the JOIN to mjnEmployeeDepartment. The first query uses LEFT OUTER JOIN and the second uses INNER JOIN. They both return the same data but the INNER JOIN takes 4.5 minutes to execute whereas the LEFT OUTER JOIN takes 4 seconds. Can anyone suggest what might be happening here?

I am running the code in SMS 2017 against a SQL 2012 database.

Select
Count(*) --23878 00:00:00

From
       dbo.mjnEmployee as e
              Inner Join dbo.mjnEmployeeStatus as s
                     on e.EmployeeID = s.EmployeeID
              Inner Join dbo.mjnEmployeeEmploymentInfo as RankNo
                     On e.EmployeeId = RankNo.EmployeeID
              Inner Join dbo.mjnEmployeeOfficeAssociation as Office
                     On e.EmployeeId = Office.EmployeeID
              Inner Join dbo.mjnEmployeeEmploymentInfo as TrackNo
                     On e.EmployeeId = TrackNo.EmployeeID
              Inner Join dbo.mjnEmployeeUnit as Unit
                     on e.EmployeeID = Unit.EmployeeID
                     And Unit.Iteration = 1

              Left Outer Join dbo.mjnEmployeeDepartment as Department
                     on e.EmployeeID = Department.EmployeeID

              Left Outer Join dbo.mjnEmployeeAssociation as Supervisor
                     On  e.EmployeeId = Supervisor.ObjectEmployeeId
                     and Supervisor.EmployeeAssociationType = 2
              Left Outer Join dbo.mjnEmployeeAssociation as Manager
                     On Manager.ObjectEmployeeId = e.EmployeeId
                     and Manager.EmployeeAssociationType = 1
              Left Outer Join dbo.mjnEmployeeAssociation as Assistant
                     On  e.EmployeeId = Assistant.ObjectEmployeeId
                     and Assistant.EmployeeAssociationType = 3
              Left Outer Join dbo.mjnEmployeeAssociation as Advisor
                     On e.EmployeeId = Advisor.ObjectEmployeeId
                     and Advisor.EmployeeAssociationType = 4

Select
Count(*) --23878 00:04:37

From
       dbo.mjnEmployee as e
              Inner Join dbo.mjnEmployeeStatus as s
                     on e.EmployeeID = s.EmployeeID
              Inner Join dbo.mjnEmployeeEmploymentInfo as RankNo
                     On e.EmployeeId = RankNo.EmployeeID
              Inner Join dbo.mjnEmployeeOfficeAssociation as Office
                     On e.EmployeeId = Office.EmployeeID
              Inner Join dbo.mjnEmployeeEmploymentInfo as TrackNo
                     On e.EmployeeId = TrackNo.EmployeeID
              Inner Join dbo.mjnEmployeeUnit as Unit
                     on e.EmployeeID = Unit.EmployeeID
                     And Unit.Iteration = 1

              Inner Join dbo.mjnEmployeeDepartment as Department
                     on e.EmployeeID = Department.EmployeeID

              Left Outer Join dbo.mjnEmployeeAssociation as Supervisor
                     On  e.EmployeeId = Supervisor.ObjectEmployeeId
                     and Supervisor.EmployeeAssociationType = 2
              Left Outer Join dbo.mjnEmployeeAssociation as Manager
                     On Manager.ObjectEmployeeId = e.EmployeeId
                     and Manager.EmployeeAssociationType = 1
              Left Outer Join dbo.mjnEmployeeAssociation as Assistant
                     On  e.EmployeeId = Assistant.ObjectEmployeeId
                     and Assistant.EmployeeAssociationType = 3
              Left Outer Join dbo.mjnEmployeeAssociation as Advisor
                     On e.EmployeeId = Advisor.ObjectEmployeeId
                     and Advisor.EmployeeAssociationType = 4

Me too Duffy but wouldn't a missing index effect a left join as well as an inner join? Also, when I look at the execution plans SMS does not suggest any new index. — Matthew Nelson, Sep 25 '17 at 19:41
At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also [try to read it yourself](https://stackoverflow.com/a/759097/1260204), maybe you can figure out the performance issue(s) with your query. Finally include the [schema DDL](https://en.wikipedia.org/wiki/Data_definition_language) along with the query you are executing. — Igor, Sep 25 '17 at 19:41
Look into indexing, and look into re-ordering the joins, if possible. Sometimes just addressing those two things will correct slowness. — user2366842, Sep 25 '17 at 19:41
I don't know what you look for in the execution plan to spot when an index is needed. If I were looking at a MySQL or SQL Server database I'd look for TABLE SCAN. — duffymo, Sep 25 '17 at 20:07
I will try to post the execution plans. They are rather lengthy. In SMS when you look at the execution plan there will be a suggestion to add an index if the optimizer thinks you need one. — Matthew Nelson, Sep 25 '17 at 20:23
Look in the catalog-tables or simply use UPDATE STATISTICS on all tables to be sure — Turo, Sep 25 '17 at 20:34
Fast Plan https://www.brentozar.com/pastetheplan/?id=r1LX1gwsZ Slow Plan https://www.brentozar.com/pastetheplan/?id=HJl0elwj- — Matthew Nelson, Sep 25 '17 at 21:12

score 0 · Answer 1 · answered Sep 25 '17 at 23:57

0

It seems like the, below table used in the join missing on clause which leads to multiply all rows with the earlier set of results,

Inner Join dbo.mjnEmployeeDepartment as Department
                     on e.EmployeeID = Department.EmployeeID

Is there any other table having this column ? If yes then that needs to be used.

answered Sep 25 '17 at 23:57

Vikrant More

5,182
23
58
90

The rows aren't being duplicated. Both versions of the query return the exact same data. – Matthew Nelson Sep 26 '17 at 14:48
its not abt the same data, in first query its left outer join and in second query its inner join on this table – Vikrant More Oct 02 '17 at 10:14
It is the same data. In this case, the data in both tables is a 1-1 exact match so the left join versus the inner join returns the same exact data set. The difference is in the execution plan selected by the optimizer. – Matthew Nelson Oct 03 '17 at 12:12

score 0 · Answer 2 · edited Sep 26 '17 at 15:26

0

We ended up using a combination of reordering the tables and using FORCESEEK optimizer hint. We moved the conditional join table to be after all the inner join tables and added WITH(FORCESEEK) after the department table. Running like a champ now.

edited Sep 26 '17 at 15:26

TT.

15,774
6
47
88

answered Sep 26 '17 at 14:51

Matthew Nelson

105
1
8

score -1 · Accepted Answer · answered Sep 25 '17 at 19:52

-1

You need to look at the difference between an inner and outer join. In essence an inner join will give the rows that are common to both tables where an outer join will give all rows in table A and any rows in table B that match.

This likely explains why your inner join is faster (although it's difficult to say without knowing the data).

The differences are illustrated really well in another question here:

What is the difference between "INNER JOIN" and "OUTER JOIN"?

answered Sep 25 '17 at 19:52

Tony Roe

64
3

The INNER JOIN is the slow one. That's why I am confused. They both return the same data but itakes the inner join almost 100 times longer to do it. – Matthew Nelson Sep 25 '17 at 20:15
Your question states They both return the same data but the INNER JOIN takes 4 minutes to execute whereas the LEFT OUTER JOIN takes 4.5 minutes hence my answer – Tony Roe Sep 25 '17 at 20:30
BAH! I meant to type 4 SECONDS. My bad. – Matthew Nelson Sep 25 '17 at 20:32

Inner Join kills query performance

3 Answers3