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

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.

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.

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.

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.

JOIN order doesn't matter, the query engine will reorganize their order based on statistics for indexes and other stuff.

For test do the following:

• select show actual execution plan and run first query
• change JOIN order and now run the query again
• compare execution plans

They should be identical as the query engine will reorganize them according to other factors.

As commented on other asnwer, you could use OPTION (FORCE ORDER) to use exactly the order you want but maybe it would not be the most efficient one.

AS a general rule of thumb, JOIN order should be with table of least records on top, and most records last, as some DBMS engines the order can make a difference, as well as if the FORCE ORDER command was used to help limit the results.

Wrong. SQL Server 2005 it definitely matters since you are limiting the dataset from the beginning of the FROM clause. If you start with 2000 records instead of 2 million it makes your query faster.