Understanding the Execution Plan [ When Table JOIN occurs Part-III ]

Introduction

Continuing our journey here we see some others operators exist in our execution plan. Here is the query and Execution Plan that we are working from couple of weeks.

Example Query

SELECT e.JobTitle, a.City,
       p.LastName + ', ' + p.FirstName AS EmployeeName

FROM   HumanResources.Employee AS e

       INNER JOIN Person.BusinessEntityAddress AS bea

                       ON e.BusinessEntityID =bea.BusinessEntityID

       INNER JOIN Person.Address a ON bea.AddressID =a.AddressID

       INNER JOIN Person.Person AS p
             ON e.BusinessEntityID = p.BusinessEntityID;

Execution Plan

Compute Scalar

First of all it is not a join operation. As it is covered in our Execution Plan, so we must discuss about it. Here we see the properties of the Compute Scalar.

It is represent a operation named Scalar, generally used for calculation purpose. In our case the alias Employeename = ContactLastname + Conatct.FirstName with comma operators between them. If we look at the property, it is not a 0 cost operators (0.001997).

If we look at the property Expr1008 and click at the ellipsis on the right side of the property page this will open the expression.

Related Reference

Understanding the Execution Plan [ When Table JOIN occurs Part-II ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table_25.html
Understanding the Execution Plan [ When Table JOIN occurs Part-I ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html
Understanding of Execution Plan – III - C [ The OPERATORS]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-c.html
Understanding of Execution Plan – III - B [ The OPERATORS ]	http://sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-b.html
Understanding of Execution Plan [What happened When SQL statement Execute]	http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html
Understanding of Execution Plan – II [Reuse of the Execution Plan]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-ii.html
Understanding of Execution Plan – III - A [ The OPERATORS ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-the.html

Summary

In the next level we have to more discuss about our execution plan. So this series will be continued for some more articles. Please be with us.

Hope you like it.

Posted by: MR. JOYDEEP DAS

WINDOW Function

Introduction

The function named WINDOW belongs to SET Function and it applies to the set of rows. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012. Without going to deeper in WINDOW function we straight go to an Example to understand it in better way.

Step – 1 [ Create a Base Table ]

IF OBJECT_ID(N'dbo.tbl_EXAMPLETABLE', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EXAMPLETABLE];

   END

GO

CREATE TABLE [dbo].[tbl_EXAMPLETABLE]

     (

        IDNO      INT    NOT NULL,

        VALUE     INT    NOT NULL

     );

Step-2 [ Inserting Some Records in Base Table ]

INSERT INTO  [dbo].[tbl_EXAMPLETABLE]

         (IDNO, VALUE)

VALUES (1, 10),(1, 20),(1, 30),

       (2, 20),(2, 30),(2, 40);

SELECT IDNO, VALUE FROM [dbo].[tbl_EXAMPLETABLE];

IDNO        VALUE

----------- -----------

1           10

1           20

1           30

2           20

2           30

2           40

(6 row(s) affected)

Step-3 [ Now try to Group by ]

SELECT   IDNO,

         SUM(VALUE) AS [SUM VALUE],

         AVG(VALUE) AS [AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE]

GROUP BY IDNO;

IDNO        SUM VALUE   AVG VALUE

----------- ----------- -----------

1           60          20

2           90          30

(2 row(s) affected)

Step – 4 [ Now we want the Output Like this ]

IDNO       VALUE      SUM VALUE   AVG VALUE

------------------------------ -----------

1          10         60         20

1          20         60         20

1          30         60         20

2          20         90         30

2          30         90         30

2          40         90         30

Step – 5 [ Is it Possible Before SQL 2012 ]

Yes it is but takes some efforts.

SELECT   a.IDNO,

         a.VALUE,

         b.[SUM VALUE],

         b.[AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE] As a

         INNER JOIN

            (SELECT   IDNO,

                      SUM(VALUE) AS [SUM VALUE],

                   AVG(VALUE) AS [AVG VALUE]

             FROM     [dbo].[tbl_EXAMPLETABLE]

             GROUP BY IDNO)AS b ON a.IDNO = b.IDNO;           

IDNO        VALUE       SUM VALUE   AVG VALUE

----------- ----------- ----------- -----------

1           10          60          20

1           20          60          20

1           30          60          20

2           20          90          30

2           30          90          30

2           40          90          30

(6 row(s) affected)

Step – 6 [  What’s makes Easy in WINDOW function in SQL 2012 ]

We Just use the OVER() Clause

SELECT   IDNO,

         VALUE,

         SUM(VALUE) OVER() AS [SUM VALUE],

         AVG(VALUE) OVER() AS [AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE];

IDNO        VALUE       SUM VALUE   AVG VALUE

----------- ----------- ----------- -----------

1           10          150         25

1           20          150         25

1           30          150         25

2           20          150         25

2           30          150         25

2           40          150         25

(6 row(s) affected)

Step-7 [ We can use Partition By clause within OVER() ]

SELECT   IDNO,

         VALUE,

         SUM(VALUE) OVER(PARTITION BY IDNO) AS [SUM VALUE],

         AVG(VALUE) OVER(PARTITION BY IDNO) AS [AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE];

IDNO        VALUE       SUM VALUE   AVG VALUE

----------- ----------- ----------- -----------

1           10          60          20

1           20          60          20

1           30          60          20

2           20          90          30

2           30          90          30

2           40          90          30

(6 row(s) affected)

Hope you like it.

Posted by: MR. JOYDEEP DAS

Understanding the Execution Plan [ When Table JOIN occurs Part-II ]

Introduction

Continuing my previous article Understanding Execution Plan [ When Table JOIN occurs Part – I ] hare we are describing the others process of Execution Plan.

If have you not read my previous article, please go through the following link.

http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html

For reference we again have a look at the Execution plan of the flowing Query.

SELECT e.JobTitle, a.City, 
       p.LastName + ', ' + p.FirstName AS EmployeeName

FROM   HumanResources.Employee AS e

       INNER JOIN Person.BusinessEntityAddress AS bea

                       ON e.BusinessEntityID =bea.BusinessEntityID

       INNER JOIN Person.Address a ON bea.AddressID =a.AddressID

       INNER JOIN Person.Person AS p 
             ON e.BusinessEntityID = p.BusinessEntityID;

The Nested Loop Join

If we look at our Execution Plan carefully we find that the second operation from the top right is a Clustered Index Seek operation (BusinessEntityAddress Table). This is relatively less expensive (only 9%). The Seek is a part of Join Operation and we can see the different search criteria with it. For this we have to look at the Seek Predicates section at the Bottom of the tool tip property.

A Nested Loops join functions by taking a set of data, referred to as the outer set, and comparing it, one row at a time to another set of data, called the inner set. It just likes a cursor, and effectively, it is one but, with the appropriate data set, it can be a very efficient operation.

The data Scan at Employee Table and the Seek against the BusinessEntityAddress table is join at Nested Loop Join operation. To understand it properly we have to look at the tooltip or property of the Nested Loop Join.

We can call Nested Loop as Nested Iteration as the operation takes input from two data sets and join them by scanning from outer data set (Here in our Execution Plan it is the Bottom operator) once for each row in the inner set. If the number of rows is in two data set is small, the Nested Loop operation is much more efficient.  As long as the inner data set is small and the outer data set, small or not, is indexed, then this is an extremely efficient join mechanism. Except in cases of very large data sets, this is the best type of join to see in an execution plan.

Related Reference

Understanding the Execution Plan [ When Table JOIN occurs Part-I ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html
Understanding of Execution Plan – III - C [ The OPERATORS]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-c.html
Understanding of Execution Plan – III - B [ The OPERATORS ]	http://sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-b.html
Understanding of Execution Plan [What happened When SQL statement Execute]	http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html
Understanding of Execution Plan – II [Reuse of the Execution Plan]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-ii.html
Understanding of Execution Plan – III - A [ The OPERATORS ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-the.html

Summary

In the next level we have to more discuss about our execution plan. So this series will be continued for some more articles. Please be with us.

Hope you like it.

Posted by: MR. JOYDEEP DAS