Force order query hint in SQL Server

The force order query hint in SQL server forces the query optimizer to join tables in the order they appear in the form clause, instead of letting SQL Server determine the best join. Default behavior of the SQL Server is re-arranging join order based on the estimates to optimize performance. With force order join happen exactly in the order specified in the query.

Use of force order

Ø  When SQL server optimizer picks a suboptimal join order for complex query.

Ø  When dealing with bad execution plan due to in correct statistics.

Ø  For trouble shooting performance issue and testing differ join order.

Avoid force order

Ø  The optimizer’s plan is already efficient.

Ø  Table frequently changes in size (static plan become outdated)

Ø  We are not sure about the best join order.

Syntex

Select * from tableA a Join TableB b On a.id=b.id Join TbaleC c On b.id=c.id Option (Force Order)

The optimizer must join TableAàTAbleBàTAbleC in this Order.

Example

Slow performance in multi join query.

A company has three large table

Ø  Customer(customerID,customerName) à 1 M records

Ø  Order(OrderID,CustomerID)à10 M records

Ø  OrderDetails(OrderDetailID,OrderID,productid,Qntity,price, TotalCost)à 50 M records.

SQL Server picks a bad join order. Query retrieves customer , orders and orderdetails. The optimizer join orders and orderdetails first, creating a huge intermediate result set. This makes the very slow.

Select c.customerName,o.orderID,od.productID ,od.Price From Customer c Join Order o on o.customerID=c.customerID Join OrderDetails od on od.orderid=o.orderid.

 SQL server may join orders and orderdetails first , producing a massive result set. This cause high memory usage and slow execution. 

Using this query hint we can solve this issue. It will join customer first, reducing the dataset early. Using the force order to prevent SQL server for changing the join order. 

Select c.customerName,o.orderID,od.productID ,od.Price From Customer c Join Order o on o.customerID=c.customerID Join OrderDetails od on od.orderid=o.orderid Option(force order)

This will insure the joining sequence Customerà orders àorderdetails

This will filter out the irrelevant customer first reducing data early. It will execute fast and use low memory.