CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2005t-sql
user822150
user822150

Reputation: 71

SQL inner vs left join question

I have a very badly performing sql query. I tracked it down to the INNER JOIN performed on the table. Changing this to LEFT join significantly increases performance (from 6 min to 20 sec) - now i know the 2 are not equiv, but... here is what i am asking

SELECT * 
  FROM SomeTable ST
  JOIN BigTable BT ON BT.SomeID = ST.SomeID 
                  AND BT.Something = ST.Something 
                  AND BT.AnotherValue = '123'

Since the join has additional criteria (and something=something) -- is changing this to a left join producing the same results - but MUCH faster?

The results returned are the same using LEFT/INNER with left being significantly faster...

Upvotes: 7

Views: 1073

Answers (2)

N. Warfield
N. Warfield

Reputation: 346

While you're getting the same results from both joins its important to understand that a left join is different from an inner join.

A left join will take all rows from the left table even if there are no matches in the right table.

SQL LEFT JOIN vs. SQL INNER JOIN

So your data just happens to be produced in a manner that is identical given the two different joins. 

SELECT * 
  FROM SomeTable ST
  JOIN BigTable BT ON BT.SomeID = ST.SomeID 
                  AND BT.Something = ST.Something 
                  AND BT.AnotherValue = '123'

How about this:

SELECT
   *
FROM
   SomeTable
      INNER JOIN
   BigTable
      ON SomeTable.PreferedPrimaryKey = BigTable.PreferAForeignKey
      AND SomeTable.SomethingThatIsIndexedAndPreferableNumeric = BigTable.SomethingThatIsIndexedAndPreferableNumeric 
WHERE
   BigTable.AnotherValue = '123'

Check your indexes and make sure your criteria for the second part of the join isn't a non-indexed character string.

Upvotes: 0

shinkou
shinkou

Reputation: 5154

It looks like doing the inner join the other way around would give a better performance...

SELECT
    *
FROM
    BigTable AS BT
INNER JOIN
    SomeTable AS ST
ON
    BT.AnotherValue = '123'
AND
    BT.SomeID = ST.SomeID 
AND
    BT.Something = ST.Something

or with subquery

SELECT
    *
FROM
    (SELECT * FROM BigTable WHERE AnotherValue = '123') AS BT
INNER JOIN
    SomeTable AS ST
AND
    BT.SomeID = ST.SomeID 
AND
    BT.Something = ST.Something

Also, make sure the BigTable.AnotherValue is properly indexed.

Upvotes: 2

Related Questions