Does Sql JOIN order affect performance?

I was just tidying up some sql when I came across this query:

SELECT 
        jm.IMEI ,
        jm.MaxSpeedKM ,
        jm.MaxAccel ,
        jm.MaxDeccel ,
        jm.JourneyMaxLeft ,
        jm.JourneyMaxRight ,
        jm.DistanceKM ,
        jm.IdleTimeSeconds ,
        jm.WebUserJourneyId ,
        jm.lifetime_odo_metres ,
        jm.[Descriptor]
FROM    dbo.Reporting_WebUsers AS wu WITH (NOLOCK)
        INNER JOIN dbo.Reporting_JourneyMaster90 AS jm WITH (NOLOCK) ON wu.WebUsersId = jm.WebUsersId
        INNER JOIN dbo.Reporting_Journeys AS j WITH (NOLOCK) ON jm.WebUserJourneyId = j.WebUserJourneyId
WHERE   ( wu.isActive = 1 )
        AND ( j.JourneyDuration > 2 )
        AND ( j.JourneyDuration < 1000 )
        AND ( j.JourneyDistance > 0 )

My question is does it make any performance difference the order of the joins as for the above query I would have done

FROM dbo.Reporting_JourneyMaster90 AS jm

and then joined the other 2 tables to that one

Solution 1:

Join order in SQL2008R2 server does unquestionably affect query performance, particularly in queries where there are a large number of table joins with where clauses applied against multiple tables.

Although the join order is changed in optimisation, the optimiser does't try all possible join orders. It stops when it finds what it considers a workable solution as the very act of optimisation uses precious resources.

We have seen queries that were performing like dogs (1min + execution time) come down to sub second performance just by changing the order of the join expressions. Please note however that these are queries with 12 to 20 joins and where clauses on several of the tables.

The trick is to set your order to help the query optimiser figure out what makes sense. You can use Force Order but that can be too rigid. Try to make sure that your join order starts with the tables where the will reduce data most through where clauses.

Solution 2:

No, the JOIN by order is changed during optimization.

The only caveat is the Option FORCE ORDER which will force joins to happen in the exact order you have them specified.

Solution 3:

I have a clear example of inner join affecting performance. It is a simple join between two tables. One had 50+ million records, the other has 2,000. If I select from the smaller table and join the larger it takes 5+ minutes.

If I select from the larger table and join the smaller it takes 2 min 30 seconds.

This is with SQL Server 2012.

To me this is counter intuitive since I am using the largest dataset for the initial query.

Solution 4:

Usually not. I'm not 100% this applies verbatim to Sql-Server, but in Postgres the query planner reserves the right to reorder the inner joins as it sees fit. The exception is when you reach a threshold beyond which it's too expensive to investigate changing their order.