In this article, we learn about operators of joins- Merge Join, Hash Join, And Nested Join.
If we use an efficient join operator according to the data tables, it can increase the performance query else it can decrease the performance.
EXECUTION PLAN
- An execution plan is a visual representation of the operations. It is an interface to check how a query was executed.
- The correct way to read the execution plan is to start from the right side of the plan to the left side.
Three ways to display the execution plan:
Display Estimated Execution Plan:-
- The Display Estimated Execution Plan will provide you how your query will execute on the server without actually running it.
Include Actual Execution Plan:-
- The Include Actual Execution Plan shows what happened when the server executed the query.
Live Execution Plan:-
- In the live execution plan, we see an actual plan of the query. It allows you to directly take a look at how to query executing.
QUERY COST
- The cost is based on how many CPU and I/O resources.
- The cost of executing a query is 100% and this is divided across each part.
OPERATORS OF JOINS
- A join is used to combines two or more tables, it depends on the relationship between tables.
- In the execution plan, there are many joins for different operations like Nested Loop Join, Hash Match, and Merge Join.
- The query optimizer is a cost-based optimizer. Whenever we join the table the query optimizer selects join iterator based on the cost-based decision.
NESTED JOIN:-
- The nested loops join, also called nested iteration. A nested loop is a loop inside a loop. Usually, it is used when the table is less number of records.
- If one table is small and the other is large also it has indexed on join columns, then the nested loop join is the fastest join operation.
- Nested loop supports almost all types of join except right and full outer join.
- An index nested loops perform better than a merge join or hash join if a less number of records are involved.
MERGE JOIN:-
- The Merge Join provides an output that is generated by joining two sorted data sets using a full, left, or inner join.
- If the tables are large then merge join is the fastest join operation. It requires a sorted input.
- The Merge Join simultaneously reads a row from each input and compares them using the join key.
HASH JOIN:-
- The Hash Match physical operator builds a hash table by computing a hash value for each row from its build input.
- Hash joins can efficiently process large, unsorted, non-indexed inputs.
- This requires less IO, but needs more CPU and requires a lot of memory.
- Hash join use two important terms Hashing Function and Hash table.
Hashing Function:- It takes one or more values and converts them to a single symbolic value usually in numeric.
Hash Table:- The hash table is a data structure that divides all rows into equal-sized buckets, where each bucket is represented by a hash value.
If you are new to database learning — SQL Server recommended is the following must-watch the video:-
