sql server - Does Sql JOIN order affect performance?

Question

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

Answer 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.