In my previous article we are just using a single table as per our example is concern, Now in this article we are going to see the different table join each other’s and make a single data set by using JOIN. When a JOIN command is issued in T-SQL, it will be resolved through a Join operator. Hope it will be interesting.
SELECTe.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;
2. Hash Match join operation between the Person.Person table and the output from the
first Hash Match (21%).
3. Other Hash Match join operator between the Person.Address table and the output from
the Nested Loop operator (20%).
Here the Estimated Operation Cost means the cost to the Query Optimizer for Executing the specific operation. If Estimated Operation Cost is lower is more efficient the Operation. That does not mean that only seeing the Estimated Operation Cost we decide that it is most expensive operation. We have to calculate other factors also.
What is Has Match Join
In this execution plan we also find the Hash Match Join Operators. First we have to understand what it does.
Hash Match just put two data set into a Temporary Table called Hash Table and use this structure to compare data arrive at the matching set.
Here in our Execution Plan we find two Hash Match join operators. First we find just before SELECT operators (Must read from Right to Left). It just JOIN the output of INDEX SCAN with output of the rest of the operators in the query. This the second most expensive operation in our execution plans. Now we see the tooltip property of this Hash Match Join.
It is a programmatic technique where data is converted into symbolic forms for much more efficient searching of data. , SQL Server programmatically converts a row of data in a table into a unique value that represents the contents of the row. We can say it like encryption, a hash value converted to the original data.
It is a data structure and allows quick access to the element. The SQL server take a row from table and hash it into a hash value and store the hash value into a hash table in temp db.
Note: for more information please search on google.
Now move it our main flow, the Hash Match Join operators when SQL server has to join two large datasets. It decides to do so by Hashing the row from the smaller of the two data sets and inserting them into hash table. It then processes the larger data set, one row at a time, against the hash table, looking for matches, indicating the rows to be joined.
If the hash table is relatively small this can be quick in process. If the both table are very large, the hash Match join can be very ineffective in compare to the other type of join. As all the data is stored in the Temp DB, so excessive use of Hash Join in our query provide heavier load of Temp DB.
2. Missing Where clause
3. A Where clause with Calculation or Conversion