SQL Server 2005 Merge Join algorithm

Brief introduction:

If two join inputs are not small but are sorted on the joined columns (for example, if they are obtained by scanning a sorted index), the merge join is the fastest join operation. If two join inputs are large and the size of the two inputs is similar, the predefined merge joins provide a similar performance to the hash join.

From our analysis last time, nested loops are good for both input and output, so if the input and input are large, the combination algorithm is optimal.

Best use:

Merging the joins itself is fast, but selecting a merge join can be time-consuming if you need a sort operation. However, if the data is large and can be obtained from an existing B-tree index, the merged join is usually the fastest available join algorithm.

Let's test the best case for a merged connection:

Test environment: Table: Workflowinfo1 about 450,000 table workflowbase1 about 45,000 articles

Condition: Workflowbase1 The column id,creater are indexed, and WorkflowId in Workflowinfo1 is indexed.

If two join inputs are not small but are sorted on the joined columns (for example, if they are obtained by scanning a sorted index), the merge join is the fastest join operation. If two join inputs are large and the size of the two inputs is similar, the predefined merge joins provide a similar performance to the hash join. ~: (creater=4028814110830a1e01108fe379e60061 ' WORKFLOWBASE1 table has 1023 data)

Test statement:

Merging algorithms

select a.* from workflowbase1 a inner merge join dbo.workflowinfo1 b
on a.id=b.workflowid and a.creater='4028814110830a1e01108fe379e60061'

Hash algorithm

select a.* from workflowbase1 a inner hash join dbo.workflowinfo1 b
on a.id=b.workflowid and a.creater='4028814110830a1e01108fe379e60061'

Note: This two SQL differs from the previous nested loop example, a SELECT * and one is a select a.*

Restart the database service to view costs: