The query optimizer supports JOIN operations, while SQLServer mainly has the following three types of JOIN algorithms: NestedLoop, Sort-Merge, and HashJoin. Although each algorithm is not very complex, considering performance optimization, product-level optimizers often use improved variant algorithms. For example, SQLServer supports blocknest
The query optimizer supports JOIN operations, while SQL Server mainly has the following three types of JOIN algorithms: Nested Loop, Sort-Merge, and Hash Join. Although each algorithm is not very complex, considering performance optimization, product-level optimizers often use improved variant algorithms. For example, SQL Server supports block nest.
Query OptimizerAll are supported
JOINOperation, and
SQL ServerThere are three main JOIN algorithms: Nested Loop, Sort-Merge, and Hash Join. Although each algorithm is not very complex, considering performance optimization, product-level optimizers often use improved variant algorithms. For example
SQL ServerSupports block nested loops, index nexted loops, sort-merge, hash join, and hash team. Here we will give a brief introduction to the prototype of the above three basic algorithms.
[Hypothesis] two tables, R and S, occupy M pages in total, and S occupies N pages in total. R and s represent tuples, And I and j represent the I or j fields, respectively, that is, the JOIN fields mentioned later.
1. Nested Loop Join (Nested Loop Join)
Algorithm:
The idea is quite simple and straightforward: For each tuples in relation R, r compares them with every tuples in relation S in the JOIN condition field and filters out the qualified tuples. The pseudocode is:
Foreach tuple r into R do
Foreach tuple s Î S do
If ri = sj then add to result |
Cost:
The order of the inner or outer layers of joined tables has a significant impact on disk I/O overhead. However, the CPU overhead is relatively low, mainly because the overhead of in-memory after the tuples are read into the memory is O (n * m)
For I/O overhead, according to the prerequisites of page-at-a-time, I/O cost = M + M * N, translation is the I/O overhead = the I/O overhead for reading M pages + the I/O overhead for reading N pages M times.
Usage summary:
• It is applicable to situations where one set is large and the other set is small (using a small set as an external loop), and I/O performance is good.
• I/O performance is quite good when the External Loop input is quite small, the internal loop is very large, and indexes are built on the JOIN field.
• When only one of the two sets creates an index on the JOIN field, the set must be used as an inner loop.
• For a one-to-one matching relationship (two links with unique constraint fields ), you can skip the remainder of the loop after finding the matching tuples (similar to the continue in the loop statement of the programming language ).
2. Sort-Merge Join (Sort Merge Join)
The Nested Loop is generally inefficient when both sets are large, and Sort-Merge is much more efficient than it in this case, especially when both JOIN fields have clustered indexes, Sort-Merge performs the best.
Algorithm:
The basic idea is also very simple (review the merging and sorting in the data structure). There are two main steps:
(1) sort by JOIN Fields
(2) Merge and sort two sorted sets, obtain data columns from the source end and compare them (Special "partition" processing needs to be performed based on whether there are duplicate values in the JOIN field)
Cost: (I/O overhead)
There are two factors about Sort-Merge Overhead: whether the JOIN field is sorted and the number of duplicate values in the JOIN field.
• Best case (both columns are sorted and at least one column has no duplicate values): O (n + m) only needs to scan each of the Two Sets
• Worst Case (both columns are not sorted and all values in the two columns are the same): O (n * log n + m * log m + n * m) two sorting times and Cartesian product between all tuples
Usage summary:
As mentioned above, we can consider using these two results when both result sets are large. It is best to have clustered indexes to ensure that the sorting is complete. In practical applications, we often encounter a primary key-foreign key relationship that is a good application of Sort-Merge. In this case, both columns usually have clustered indexes (sorted) and the one-to-many relationship ensures that at least one column has no duplicate values. In this case, the performance of Sort-Merge is the best among the three types.
In addition, if the SQL syntax to be queried needs to be run BY GROUP BY, ORDER BY, and CUBE, the query syntax will be sorted as a whole, so you can reuse the sorting result, merge is also a good choice.
3. Hash Join)
Hash Join is essentially similar to the processing idea of Sort-Merge when both columns have duplicate values-partition ). But there are also differences: Hash Join uses Hash to partition (each bucket is a partition) and Sort-Merge uses sorting to partition (each duplicate value is a partition ).
It is worth noting that the big difference between Hash Join and the above two algorithms is also a big restriction that it can only be applied to equality join ), this is mainly caused by the certainty and disorder of the hash function and its bucket.
Algorithm:
The basic Hash Join algorithm consists of the following two steps:
(1) Build Input Phase: Based on the JOIN field, the hash function h2 is used to Build a in-memory (in-memory) hash table for a small S set, A bucket composed of linked lists with the same key value)
(2) Probe Input Phase: checks the hash table on a large R set to complete the join. The verification operations include:
Foreach tuple r into R do
Hash on the joining attribute using the hash function of step 1 to find a bucket in the hash table
If the bucket is nonempty
Foreach tuple s in the found bucket
If ri = sj then add to result |
Cost:
It is worth noting that for each R tuples in a large set of r, each of the buckets corresponding to r in the hash bucket needs to be compared with r, which is also the most time-consuming part of the algorithm.
CPU overhead is O (m + n * B) B is the average number of tuples for each bucket.
Usage summary:
In general, the query optimizer will first consider Nested Loop and Sort-Merge, but if the two sets are large and there is no suitable index, Hash Join will be considered.
Hash Join is also used for many set comparison operations, such as inner join, left/right/full outer join, intersect, and difference. Of course, it must be ensured that they are all equivalent joins.
In addition, the Hash Join variant can remove duplicates and group data. It only uses one input and serves as the Build and Probe roles.
In fact, product-level optimizers generally improve these basic algorithms, and the improved versions do have a great performance improvement. Here, we only provide an introduction to the principles of the brothers who need to judge the advantages and disadvantages of the execution plan or study the implementation of the query optimizer. in actual application, we also need to make an accurate judgment based on the rich statistics.
|
Click to view Tech? Ed 2007 Microsoft technology conference
|