MSSQL Server Join 覦 譴 螳 蠍磯蓋 Join 覦 Nested Loop Join る. 伎 一危磯伎れ 譯 覲 Merge Join 伎 る. Merge Join 螳 螳螳 企 觜蟲襯 . 螳 襷れ 螳. 郁屋 '=' 一一 伎 る 曙＾蟇伎 . ('=' 伎語 覈 Loop Join企.)
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.
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
れ襦 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
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 Join 覲 . employee.job_id Clustered Index襯 燕る 企蟾? . 2000覯企朱 郁屋螻襴螳 襯 覲伎ロる Merge Join朱 ろ螻 碁. 讌襷 2005 覯 伎 Order By襯 覈讌 伎 る 蟆 覲伎ロ . 蠏碁覩襦 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 覃 蠏 れ覿磯 觜殊.
