Contents

1 SQL Server 2000 Merge Join
2 SQL Server 2005 伎 覯 Merge Join
3 merge join
4 企 蟆曙一 襴螳?
5 谿瑚襭


MSSQL Server Join 覦 譴 螳 蠍磯蓋 Join 覦 Nested Loop Join る. 伎 一危磯伎れ 譯 覲 Merge Join 伎 る. Merge Join 螳 螳螳 企 觜蟲襯 . 螳 襷れ 螳. 郁屋 '=' 一一 伎 る 曙^蟇伎 . ('=' 伎語 覈 Loop Join企.)

merge_join01.jpg

1 SQL Server 2000 Merge Join #

SQL Server 2000 蟆渚 郁屋螻襴螳 覃 覓伎^蟇 Merge Join. れ 襯 覲伎.

use pubs
go
 
select *
from employee a inner join jobs b
on a.job_id = b.job_id

--'jobs' 企. れ  43, 朱Μ 所鍵  86, 覓朱Μ 所鍵  1, 覩碁Μ 所鍵  0.
--'employee' 企. れ  1, 朱Μ 所鍵  2, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.


merge_join02.jpg

Nested Loop Join朱 碁. Merge Join朱 覦蠑瑚鍵 伎 碁ゼ 企 讌襷, Merge Join 螳 覦朱 觜蟲襯 襦 企慨.

select *
from (select top 100 percent * from employee order by job_id) a inner join
        (select top 100 percent * from  jobs order by job_id) b
on a.job_id = b.job_id

merge_join03.jpg

れ襦 Order By 伎 Job_Id襦 讌襷 Jobs 企 伎 覲伎伎 . 伎 Clustered Index螳 Job_Id襦 朱襦 り 牛磯伎螳 蠍 覓語企. 郁屋 企 覈 觜蟲襯 Merge Join朱 碁. SQL Server 2000 企蟆 朱 蟇磯 郁屋螻襴螳 覈 Clustered Index覃 覿覿 Merge Join朱 碁. 2000 危 覯 企蟾?

2 SQL Server 2005 伎 覯 Merge Join #

SQL Server 2008 螳 貎朱Μ覓語 碁慨蟆.
--employee.job_id clustered index螳 譟伎覃 .
select *
from (select top 100 percent * from employee order by job_id) a inner join
        (select top 100 percent * from  jobs order by job_id) b
on a.job_id = b.job_id

merge_join04.jpg

Loop Join朱 碁. employee 企 襾殊 曙讌襷 job_id襦 讌 . Merge Join 蟆 job_id襦 蟆郁骸襯 詞朱り . 讌襷 SQL Server 2008 襦 讌 譯殊 . SQL Server 2005 危 覯 る 覦 手 SQL 襯 伎 . 伎 Ordered襯 谿瑚蠍 覦. 蠏碁 れ螻 螳 SQL覓語 .

select *
from (select top 100 percent * from employee order by job_id) a inner join
        (select top 100 percent * from  jobs order by job_id) b
on a.job_id = b.job_id
order by a.job_id

--
select *
from employee a inner join jobs b
on a.job_id = b.job_id
order by a.job_id
merge_join05.jpg

伎 Merge Join 覲 . employee.job_id Clustered Index襯 燕る 企蟾? . 2000覯企朱 郁屋螻襴螳 襯 覲伎ロる Merge Join朱 ろ螻 碁. 讌襷 2005 覯 伎 Order By襯 覈[1]讌 伎 る 蟆 覲伎ロ . 蠏碁覩襦 employee.job_id Clustered Index螳 焔 り 企 Merge Join朱 ろ螻 襴伎 . 覓朱Μ朱 り 企 2005覯 危覿磯 Merge Join朱 襴伎 ろ . 覃 Ordered覓語 瑚 伎 Clustered Index襯 伎り 企 覲伎ロ 蠍 覓語企. 譴 蟆 企 DBMS 牛磯伎螳 覦襯 襦 譬 蟆曙 襷れ伎朱 蟆企, 譬 蟆曙 ろ螻 覲 る DB企れ 牛磯伎 蟆磯 伎襯 蟇語伎 る 蟆企. 蠏碁覩襦 DB企 ろ螻 襷れ 伎 . 覓朱 ろ螻 伎 譴 蟆 襾殊蟆讌襷..


3 merge join #

襾殊 #temp1, #temp2 企 襷れ. #temp1 1覦焔 蟇, #temp2 1覦炎唄企. 谿瑚襦 64bit, 32core/64thread, 256GB ram ろ.
select top 1000000
    row_number() over(order by (select 1)) rownum
,   a.name
into #temp1
from master.dbo.spt_values a
    cross join master.dbo.spt_values b
    cross join master.dbo.spt_values c

select top 100
    row_number() over(order by (select 1)) rownum
,   a.name
into #temp2
from master.dbo.spt_values a
    cross join master.dbo.spt_values b
    cross join master.dbo.spt_values c

蟇伎螳 襷 企 襾殊 暑 蟆曙
set statistics time on

select b.*
from #temp1 a
    inner merge join #temp2 b
        on a.rownum = b.rownum
option(force order, maxdop 4)
/*
 SQL Server ろ 螳: 
 CPU 螳 = 1686覦襴豐, 蟆所骸 螳 = 481覦襴豐
SQL Server 蟲覓 覿 覦 貉危 螳: 
   CPU 螳 = 0ms, 蟆所骸 螳 = 0ms.

 SQL Server ろ 螳: 
 CPU 螳 = 0覦襴豐, 蟆所骸 螳 = 0覦襴豐
*/

蟇伎螳 企 襾殊 暑 蟆曙
set statistics time on

select a.*
from #temp2 a
    inner merge join #temp1 b
        on a.rownum = b.rownum
option(force order, maxdop 4)
/*
 SQL Server ろ 螳: 
 CPU 螳 = 172覦襴豐, 蟆所骸 螳 = 65覦襴豐
SQL Server 蟲覓 覿 覦 貉危 螳: 
   CPU 螳 = 0ms, 蟆所骸 螳 = 0ms.

 SQL Server ろ 螳: 
 CPU 螳 = 0覦襴豐, 蟆所骸 螳 = 0覦襴豐
*/

旧螳 蟇伎螳 企 襾殊 暑 蟆曙郁 觜襯企. 企 ssis merge join . 譟一碁 企 讓 企 覈 build input 覃 蠏 れ覿磯 觜殊.

4 企 蟆曙一 襴螳? #

Merge Join 觜蟲 一危一 襷螻 企 一危 ′語れ 觜訣 襴. 一危一 企 襾殊 暑 蟆 焔レ 襴る 蟆 merge join . OLTP 蟆曙 Merge Join企 Table Spool, Index Spool 襷 覲伎碁る 朱 襷 豌襴蟇磯 Index り螳 讌 蟆曙郁 襷 . 覿覿 MSSQL Server 牛磯伎螳 伎殊襷 蟆[2] 譬讌 る 讌 ろ螻 語讌 .

5 谿瑚襭 #