Query Rule Off Join Commute & FORCE ORDER

The estimated reading time for this post is 2 minutes

There are few steps for a query to be processed before execution, one of the process is called ‘Optimizer’. In this process SQL Server tries to create a good enough execution plan from given query. In the execution plan, SQL Server identifies every single operation to retrieve and project data, SQL Server Optimizer Engine re-orders JOINs to reduce the cost of the execution.

SQL Server automatically orders the joins, it means base on the join cost, the sources will be join and it makes sure the final result is accurate with the minimal cost and effort.

For example, There are three tables called A,B and C. The given query is as following:-

SELECT * FROM TableA A 
	INNER Join TableC C ON C.ID = A.ID 
	INNER Join TableB B ON B.ID = C.ID

 

SQL Server Optimizer decides to choose one of the following possibilities:-

  • (A JOIN B) JOIN C
  • (A JOIN C) JOIN B
  • (B JOIN C) JOIN A
  • (B JOIN A) JOIN C

As you see there are four possibilities, most of database developers think that SQL Server will execute the query and joins in same order that is. There is an option to force SQL Server to avoid above decision and follows the join order provided by given query.

Lets kick an example to learn more about it.

The following scripts create three tables called A,B and C on tempdb database and also populate them straightaway.

USE tempdb;
go
CREATE TABLE TableA (ID INT Not Null, Content VARCHAR(10));
Go
CREATE TABLE TableB (ID INT Not Null, Content VARCHAR(10));
Go
CREATE TABLE TableC (ID INT Not Null, Content VARCHAR(10));
Go
 
INSERT INTO TableA (ID,Content) VALUES (1,'A'),(2,'B'),(3,'C');
Go
INSERT INTO TableB (ID,Content) VALUES (1,'A'),(2,'B'),(3,'C');
Go
INSERT INTO TableC (ID,Content) VALUES (1,'A'),(2,'B'),(3,'C');
Go
 
ALTER TABLE TableA
	ADD CONSTRAINT PK_A PRIMARY KEY CLUSTERED (ID);
Go
ALTER TABLE TableB
	ADD CONSTRAINT PK_B PRIMARY KEY CLUSTERED (ID);
Go
ALTER TABLE TableC
	ADD CONSTRAINT PK_C PRIMARY KEY CLUSTERED (ID);
Go

 

Next, execute the following query and take a look at the actual execution plan.

SELECT * FROM TableA A 
	INNER Join TableC C ON C.ID = A.ID 
	INNER Join TableB B ON B.ID = C.ID

 

Index Order 1

Above execution plan shows that SQL Server Optimizer chose (A,B)+C join order. Join order is the default behavior of SQL Server Optimizer, but there is a way to disable this behavior for the given query.

The following query uses OPTION (FORCE ORDER) in order to force SQL Server Optimizer to use the join order as in the given query.

SELECT * FROM TableA A 
	INNER Join TableC C ON C.ID = A.ID 
	INNER Join TableB B ON B.ID = C.ID
	OPTION (Force ORDER)

 

Or you can use OPTION (QUERYRULEOFF JOINCOMMUTE) in order to disable the auto join order.

SELECT * FROM TableA A 
	INNER Join TableC C ON C.ID = A.ID 
	INNER Join TableB B ON B.ID = C.ID
	OPTION (QueryRuleOff JoinCommute);

 

Index Order 2

The above execution plan illustrates that SQL Server Optimizer uses the (A JOIN C)  JOIN B which is same join order as the given query.

It is recommended to let SQL Server Optimizer Engine decides the join order to provide the good enough execution plan with the minimal cost.

Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

2 Comments on "Query Rule Off Join Commute & FORCE ORDER"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
Moin Ul Haque Africawala
Guest
Moin Ul Haque Africawala

Hey Hamid

Excellent post on to improve query performance. Can you please help me with of how to embed the OPTION ( QUERYRULEOFF JOINCOMMUTE) in either the view or a stored procedure.
Your help will be greatly appreciated.

Thanks
Regards
Moin.H

wpDiscuz