1
select * from AdventureWorks.Sales.Customer c
inner loop join AdventureWorks.Sales.SalesOrderHeader o on o.CustomerID = c.CustomerID

select * from AdventureWorks.Sales.SalesOrderHeader o
inner loop join AdventureWorks.Sales.Customer c on c.CustomerID = o.CustomerID

In MS Sql Server: the above two statements can makes different execution plans. If we assume table Customer and table SalesOrderHeader have very different order of magnitude of records. Which one has better performance? WHY?

bummi
  • 27,123
  • 14
  • 62
  • 101
user1033098
  • 205
  • 2
  • 7
  • possible duplicate of [Does INNER JOIN performance depends on order of tables?](http://stackoverflow.com/questions/19612208/does-inner-join-performance-depends-on-order-of-tables) – bummi Nov 15 '14 at 17:59

1 Answers1

1

Using join hints forces join order. Look into the messages tab: there is a message saying that.

This is a very unfortunate side-effect of using join-hints. It makes them very awkward to use.

Which one has better performance?

Look at the query execution time and plan cost estimation to answer that.

usr
  • 168,620
  • 35
  • 240
  • 369
  • If assuming that Customer has 100 records, SalesOrderHeader has 1,000,000 records, can we determine which one has better performance? Is it deterministic? – user1033098 Feb 14 '13 at 16:16
  • 2
    @user1033098, yes. "A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large." from http://msdn.microsoft.com/en-us/library/ms191318%28v=sql.90%29.aspx – iruvar Feb 14 '13 at 16:20
  • Thanks, cravoori! Getting into the bottom of the question.... WHY "A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large."? – user1033098 Feb 14 '13 at 16:28
  • 1
    Because the cost of the join is the number of outer rows times the cost to execute the inner input. Twice the rows on the outside => twice the cost. If inner input is not indexed a scan needs to occur. If it is indexed a cheap seek can be done. – usr Feb 14 '13 at 16:58