Sunday, 26 October 2014

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.JobTitlea.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