performance on sql server joins

dc stein

15y1.9k

I have the following performance question on joins in sql server 2008 r2.
Is there a performance problem when I join 5 tables using inner joins and 4 tables are joined using left outer joins? Is there a performance problem with this type of join? If so, then should I join all the tables using left outer joins? I can not use all inner joins since all rows I want selected would not be picked.
(Note: This issue has occurred since my company is changing their production database. Basically there was one table that contained about eveything we needed. Now the new database is breaking up the one major table into nine different tables. The production application are the same, but they need to work with the new database.)
Thus basically in several stored procedures I need to join all nine of the tables. For a couple tables I need to do left outer joins, so at least data from from the 5 major tables will appear.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Zoran Horvat15y ago
I've remembered what is the problem with outer joins. Consider the query with inner join:

SELECT * FROM A INNER JOIN B ON A.AIB=B.AIB;

If AID column is indexed both in A and in B table, then joining them can be implemented by the database engine by first joining indices and only when matching pairs are found in the two indices, only those matching rows would be fetched from table A and table B.

Now consider the same query with left outer join:

SELECT * FROM A LEFT JOIN B ON A.AIB=B.AIB;

In this case we have a set of B.AIB values which could be extracted from the index. But when speaking of the A table, output should include both matching rows and rows that don't have the matching B.AIB value. Hence, database engine must perform full table scan on A and index scan on B, which can be much slower than inner join.

Zoran
Zoran Horvat15y ago
There is performance penalty with left joins and those should be avoided if possible. If not possible, then move them to the right as far as you can in the join. For instance:

SELECT * FROM A LEFT JOIN B ON A.AID=B.AID INNER JOIN C ON B.BID=C.BID

This should be transformed (if applicable) into:

SELECT * FROM B INNER JOIN C ON B.BID=C.BID LEFT JOIN A ON B.AID=A.AID

This query returns a bit different result, so be careful with such transformations.

General rule of thumb is - don't use outer joins if you don't have to.

Zoran