SQL Server 2005合并联接最佳使用情况

以下的文章主要描述的是SQL Server 2005合并联接的正确算法，在实际操作中如果遇到两个联接输入而且不小但已在二者，其联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，则合并联接是最快的联接操作。

如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的SQL Server 2005合并联接提供的性能与哈希

如果两个联接输入并不小但已在二者联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，则合并联接是最快的联接操作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的SQL Server 2005合并联接提供的性能与哈希联接相近。

从上次我们分析来看，嵌套循环适合输入和输出都小的情况，那如果输入和输入都比较大情况下，使用合并算法什么情况下最优。

最佳使用:

合并联接本身的速度很快，但如果需要排序操作，选择SQL Server 2005合并联接就会非常费时。然而，如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据，则合并联接通常是最快的可用联接算法。

我们来测试一下，合并连接的最优情况：

测试环境：表：workflowinfo1 约45万条 表workflowbase1 约4.5万条

条件：workflowbase1中列id，creater都建立索引，workflowinfo1中workflowid建立了索引。

如果两个联接输入并不小但已在二者联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，则SQL Server 2005合并联接是最快的联接操作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相近。~：(creater=4028814110830a1e01108fe379e60061’的workflowbase1表有1023条数据)

测试语句：

合并算法

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

hash算法

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

注意:这两条SQL和上一个嵌套循环的例子有区别，一个 select * 和一个是 select a.*

重启数据库服务，查看成本：

执行结果:

(10468 行受影响)

表’workflowinfo1’。扫描计数1，逻辑读取3527 次，物理读取1 次，预读3528 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表’workflowbase1’。扫描计数1，逻辑读取1571 次，物理读取0 次，预读1624 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

(10468 行受影响)

表’workflowbase1’。扫描计数3，逻辑读取1571 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表’workflowinfo1’。扫描计数3，逻辑读取3886 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表’Worktable’。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

这,时，Merge算法比Hash算法少了357次IO。这时发现，成本对比，合并连接要优于hash连接,排序使用了B-tree索引的排序，大表workflowinfo1就没有排序操作。

这里验证了上面的一句话:

如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据，则SQL Server 2005合并联接通常是最快的可用联接算法

如果我们换一下，将select a.*换成select *， 看看成本

这里hash连接是最优的算法

执行结果：

(10468 行受影响)

表’workflowbase1’。扫描计数3，逻辑读取1571 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表’workflowinfo1’。扫描计数3，逻辑读取9604 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

(10468 行受影响)y

表’Worktable’。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表’workflowinfo1’。扫描计数1，逻辑读取9604 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表’workflowbase1’。扫描计数1，逻辑读取1571 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

这里的hash和merge的io次数一样，但merge连接里多了一个排序操作，占到整个成本的60&,的确验证了上面的一句话：

合并联接本身的速度很快，但如果需要排序操作，选择SQL Server 2005合并联接就会非常费时。

两个联接输入并不小但已在二者联接列上排序，则SQL Server 2005合并联接是最快的联接操作。如果没有排序hash连接是最优的操作。

注意：这里的排序指两个输入集合必须按相等列进行分别排序。而不是按其他列排序。

【编辑推荐】

1. SQL Server2000连接错误的缘由有哪些？
2. SQL Server实例中对另个实例的调用
3. SQL Server 2000的安全策略的正确打造
4. SQL Server 数据导入的实际行为规范描述
5. MS SQL Server问题与其正确解答方案