Query Rule Off Join Commute & FORCE ORDER

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:-

[codesyntax lang=”tsql”]

Select * From TableA A 
	Inner Join TableC C On C.ID = A.ID 
	Inner Join TableB B on B.ID = C.ID

[/codesyntax]

 

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

 

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

[codesyntax lang=”tsql”]

Select * From TableA A 
	Inner Join TableC C On C.ID = A.ID 
	Inner Join TableB B on B.ID = C.ID

[/codesyntax]

 

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.

[codesyntax lang=”tsql”]

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)

[/codesyntax]

 

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

[codesyntax lang=”tsql”]

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);

[/codesyntax]

 

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

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan