DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • An Overview of SQL Server Joins
  • MongoDB to Couchbase: An Introduction to Developers and Experts
  • How to Decide Between JOIN and JOIN FETCH
  • PySpark Join Explained

Trending

  • Best Practices for Writing Clean Java Code
  • Automated Testing: The Missing Piece of Your CI/CD Puzzle
  • LTS JDK 21 Features
  • Agile Estimation: Techniques and Tips for Success
  1. DZone
  2. Data Engineering
  3. Databases
  4. How JOIN Order Can Increase Performance in SQL Queries

How JOIN Order Can Increase Performance in SQL Queries

Joydeep Das user avatar by
Joydeep Das
·
Apr. 14, 14 · Interview
Like (0)
Save
Tweet
Share
25.55K Views

Join the DZone community and get the full member experience.

Join For Free

Introduction

All developers are very much concerned about  performance. If someone say that this increase performance, all the developer are running behind it. It is not a bad practice at all. Rather as per my point of view we must span all our effort related improve the performance of query.
“One common question that we find that, if we change the ordering of table join in case of inner join will effect or increase performance”
To understand it lets take a simple example of Inner join. There is two tables named Table-A and Table-B. We can us the Inner Join on both the table.
Like this
 FROM [Table-A]  AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO
OR
 FROM [Table-B]  AS a INNER JOIN [Table-A] AS b ON  a.IDNO = b.IDNO

Which one is best for performance?

To answer this question we all know that whenever a SQL Query is executed the MS SQL server create several query plans with different join Order and choose the best one.
That means the Join order that we are writing in the query may not be executed by execution plan. May be different join order is used by the execution plan. In the above case the execution plan decide which Join order he will chose depends on best possible costing of execution.
Here [Table-A] JOIN [Table-B] or [Table-B] JOIN [Table-A], MS SQL Server knows it well that both are same.

To understand it Details Lets take an Example

Step-1 [ Create Base Table and Insert Some Records ]

 -- Item Master

IF OBJECT_ID(N'dbo.tbl_ITEMDTLS', N'U')IS NOT NULL
  BEGIN
  DROP TABLE [dbo].[tbl_ITEMDTLS];
  END
GO
CREATE TABLE [dbo].[tbl_ITEMDTLS]
  (
  ITEMCD  INT  NOT NULL IDENTITY PRIMARY KEY,
  ITEMNAME  VARCHAR(50) NOT NULL
  ) 
GO

-- Inserting Records

INSERT INTO [dbo].[tbl_ITEMDTLS]
  (ITEMNAME)
VALUES ('ITEM-1'),('ITEM-2'),('ITEM-3');
 
-- Item UOM Master

IF OBJECT_ID(N'dbo.tbl_UOMDTLS', N'U')IS NOT NULL
  BEGIN
  DROP TABLE [dbo].[tbl_UOMDTLS];
  END
GO
CREATE TABLE [dbo].[tbl_UOMDTLS]
  (
  UOMCD  INT  NOT NULL IDENTITY PRIMARY KEY,
  UOMNAME  VARCHAR(50) NOT NULL
  ) 
GO

-- Inserting Records

INSERT INTO  [dbo].[tbl_UOMDTLS]
  (UOMNAME)
VALUES ('KG'),('LTR'),('GRM');
GO 

-- Transaction Table 

IF OBJECT_ID(N'dbo.tbl_SBILL', N'U')IS NOT NULL
  BEGIN
  DROP TABLE [dbo].[tbl_SBILL];
  END
GO
CREATE TABLE [dbo].[tbl_SBILL]
  (
  TRID  INT  NOT NULL IDENTITY PRIMARY KEY,
  ITEMCD  INT  NOT NULL,
  UOMCD  INT  NOT NULL,
  QTY  DECIMAL(18,3) NOT NULL,
  RATE  DECIMAL(18,2) NOT NULL,
  AMOUNT  AS QTY * RATE
  );
GO

-- Foreign Key Constraint

ALTER TABLE  [dbo].[tbl_SBILL]
ADD CONSTRAINT  FK_ITEM_tbl_SBILL FOREIGN KEY(ITEMCD) REFERENCES [dbo].[tbl_ITEMDTLS](ITEMCD);
GO
ALTER TABLE  [dbo].[tbl_SBILL]
ADD CONSTRAINT  FK_UOMCD_tbl_SBILL FOREIGN KEY(UOMCD) REFERENCES [dbo].[tbl_UOMDTLS](UOMCD);

-- Insert Records

INSERT INTO [dbo].[tbl_SBILL]
  (ITEMCD, UOMCD, QTY, RATE)
VALUES (1, 1, 20, 2000),(2, 3, 23, 1400);  

Step-2 [ Now Make Some JOIN  ]

 SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
  c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM  [dbo].[tbl_ITEMDTLS] AS a
  INNER JOIN  [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
  INNER JOIN  [dbo].[tbl_UOMDTLS]  AS c ON b.UOMCD  = c.UOMCD;
Here  [tbl_ITEMDETAILS] JOIN [tbl_SALES] JOIN [tbl_UOMDETAILS]
If we look at the Execution Plan
We find that
[tbl_SALES] JOIN [tbl_ITEMDETAILS] JOIN [tbl_UOMDETAILS]
Step-2 [ Now we need to Force Order Hint to maintain Join Order ]
 SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
  c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM  [dbo].[tbl_ITEMDTLS] AS a
  INNER JOIN  [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
  INNER JOIN  [dbo].[tbl_UOMDTLS]  AS c ON b.UOMCD  = c.UOMCD
OPTION ( QUERYRULEOFF JoinCommute);
For this we need the FORCE ORDER Hint.
The query optimizer uses different rules to evaluate different plan and one of the rules is called JoinCommute. We can turn it off using the undocumented query hint QUERYRULEOFF.
Hope you like it.
Joins (concurrency library) Database sql

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • An Overview of SQL Server Joins
  • MongoDB to Couchbase: An Introduction to Developers and Experts
  • How to Decide Between JOIN and JOIN FETCH
  • PySpark Join Explained

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: