How JOIN Order Can Increase Performance in SQL Queries
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. See the original article here.
Opinions expressed by DZone contributors are their own.