There are mainly three type of join hints
1. Nested Loop Join
2. Merge Join
3. Hash Match Join
A join hint provides a means to force SQL Server to use one of the three join methods that we've encountered previously, in a given part of a query. To recap, these join methods are:
Nested Loops join: compares each row from one table ("outer table") to each row in another table ("inner table") and returns rows that satisfy the join predicate. Cost is proportional to the product of the rows in the two tables. Very efficient for smaller data sets.
Merge Join: compares two sorted inputs, one row at a time. Cost is proportional to the sum of the total number of rows. Requires an equi-join condition. Efficient for larger data sets.
Hash Match join: reads rows from one input, hashes the rows, based on the equi-join condition, into an in-memory hash table. Does the same for the second input and then returns matching rows. Most useful for very large data sets (especially data warehouses).
By incuding one of the join hints in your T-SQL, you will potentially override the optimizer's choice of the most efficent join method. In general, this is not a good idea, and if you're not careful you could seriously impede performance.1
Application of the join hint applies to any query (SELECT, INSERT, or DELETE) where joins can be applied. Join hints are specified between two tables.
1. Nested Loop Join
2. Merge Join
3. Hash Match Join
A join hint provides a means to force SQL Server to use one of the three join methods that we've encountered previously, in a given part of a query. To recap, these join methods are:
Nested Loops join: compares each row from one table ("outer table") to each row in another table ("inner table") and returns rows that satisfy the join predicate. Cost is proportional to the product of the rows in the two tables. Very efficient for smaller data sets.
Merge Join: compares two sorted inputs, one row at a time. Cost is proportional to the sum of the total number of rows. Requires an equi-join condition. Efficient for larger data sets.
Hash Match join: reads rows from one input, hashes the rows, based on the equi-join condition, into an in-memory hash table. Does the same for the second input and then returns matching rows. Most useful for very large data sets (especially data warehouses).
By incuding one of the join hints in your T-SQL, you will potentially override the optimizer's choice of the most efficent join method. In general, this is not a good idea, and if you're not careful you could seriously impede performance.1
Application of the join hint applies to any query (SELECT, INSERT, or DELETE) where joins can be applied. Join hints are specified between two tables.
No comments :
Post a Comment