Monday, 24 March 2014

JOIN ORDER cans Increase Performance


Introduction


All the developer is very much concern related to 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.



Posted By: MR. JOYDEEP DAS

No comments:

Post a Comment