1 ろ碁ゼ 譴觜 #
郁屋 ′語 覿 伎 企螳 蟯螻襯 襾殊 . 螳 ERD螳 朱 譬. 蠍一 MSSQL Server れ伎願係 蟆. れ MSSQL Server 一危磯伎れ Pubs 一危磯伎れ Employees 企螻 Jobs 企 螳讌螻 る. 企 蠍磯蓋 覲企 れ螻 螳.
use pubs
exec sp_helpindex 'employee'
exec sp_helpindex 'jobs'
go
INDEX_KEYS 貉殊 覲企 企 貉殊 碁煙り 焔 讌 覲 . 蠍一 EMPLOYEE企螻 JOBS企 譟一誤 蟆碁 郁屋螻襴(FOREIGN KEY)螳 貉(蠍一 EMPLOYEE.JOB_ID 貉) 碁煙り ″讌 蟆 覲 . 蠏碁る れ螻 螳 貎朱Μ襯 MSSQL SERVER 2000 企至 ろ螻 語 蟆瑚? (覓朱 蠍一 朱 糾覲企ゼ 一危誤螻 る 螳 襷 蟆企.)
2 Loop Join 蠍磯蓋 #
set statistics io on --豢レ 覲願鍵 れ
--1
use pubs
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from employee e inner join jobs j
on e.job_id = j.job_id
go
--企 'jobs'. 蟆 0, 朱Μ 所鍵 86
--企 'employee'. 蟆 1, 朱Μ 所鍵 2
ろ螻 覲企 EMPLOYEE 企覿 渚讌 蟆 覲伎碁. ろ螻 るジ讓 一, 讓 一朱 襾殊 ろ. 碁Μ襯 螳覃 . ろ螻螻 /豢 牛 貎朱Μ襯 覿 覲企襦 . 蟆磯覿 伎手鍵覃 貎朱Μ NESTED LOOP JOIN譟一語企, 企譟一(INNER JOIN)企. 蠏碁Μ螻 EMPLOYEE企 襾殊 渚譟. 讀, れ螻 螳 襦 企 渚讌 蟆企.
蟆郁骸 讌 JOB_ID 襯 覲企 13, 14, 6, 12 ... . EMPLOYEE 企 襾殊 れ 螻殊 JOBS企 企ろ磯 碁煙(JOB_ID) 觜蟲 螳 蟆 蟆郁骸讌朱 觸. /豢 覲企 れ螳 43 蟆 覲 . 願 EMPLOYEE 企 43螳 伎 JOBS 企 JOB_ID襯 觜蟲 蠏碁 豺螳 蟆企. 襾殊 れ(企ろ磯 碁煙 れ れ螻 螳. 企ろ磯 碁煙れ 蟲譟一..) 語 EMPLOYEE.JOB_ID 碁煙り ″讌 蠍 覓語企.
襷 JOBS企 襾殊 暑り る 企 ろ螻 ″讌蟾? れ螻 螳 螳襦 JOBS 企 襾殊 暑襦 貎朱Μ企慨.
--2
use pubs
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from jobs j inner join employee e --襯 覦蠑瑚
on e.job_id = j.job_id
option (force order, loop join) --旧朱 from 企 襦 譟一誤襦 .
go
--企 'employee'. 蟆 1, 朱Μ 所鍵 29
--企 'jobs'. 蟆 1, 朱Μ 所鍵 2
蟆郁骸 れ.
豐 朱Μ 所鍵 (i/o) 豌 覯讌 貎朱Μ 2+86願, 覯讌 貎朱Μ 2+29襦 谿願 . 覲 豌襴 觜 れ伎 螻 . 襷 蟾讌 る Order By job_id襯 貎朱Μ 觜谿襯 觜蟲伎 觜 貎朱Μ襯 .
貎朱Μ襯 貅 觜 觜蟲襯 企慨.
use pubs
--1
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from employee e inner join jobs j
on e.job_id = j.job_id
go
--2
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from jobs j inner join employee e --襯 覦蠑瑚
on e.job_id = j.job_id
option (force order, loop join) --旧朱 from 企 襦 譟一誤襦 .
go
3 牛磯伎 employee 企覿 曙蟾? #
觜 觜蟲 覲 蟆郁骸 貎朱Μ1:貎朱Μ2 = 57:43朱 觜谿願 覿蟲螻 牛磯伎 觜 襷 覦 覦(employee 企覿 暑 覦)朱 ろ螻 語蟾? 襷 employee 企 Row螳 1覦焔 蟇伎企朱 覯讌 貎朱Μ螳 觜 蟆 覦蟾? employee 企 1覦焔 蟇 讌襴 覲旧覲語朱 ろ 企慨.
--drop table #employee
select top 1000000 a.* into #employee
from employee a, employee b, employee c, employee d
ろ碁ゼ 企慨 Loop Join 讌 螻, Hash Join朱 覯襴磯. 朱 蠍一 Loop Join 覲願鍵 螻褐企襦 Join Hint襯 譯殊 Loop Join朱 覦蠖 覲願.
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from #employee e inner loop join jobs j
on e.job_id = j.job_id
--企 'jobs'. 蟆 0, 朱Μ 所鍵 2000000
--企 '#employee___000000000006'. 蟆 1, 朱Μ 所鍵 6455
覿 企慨. 貎朱Μ #employee 企 れ. 6455 伎 襷殊 れ 企. れ朱 jobs 企 曙. jobs 企 Row螳 朱讌 伎 ｌ . 讌襷 Clustered Index螳 焔 伎 2伎襯 曙伎 . Clustered Index Seek襯 企 伎 豕 2伎襯 曙伎 . #employee 企 1覦焔 蟇伎企襦 1覦焔 覯 Loop襯 螳覃伎 jobs 企 れる 蟆企. 蠏碁 jobs企 I/O 2000000朱 螳 蟆企.
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from jobs j inner join #employee e --襯 覦蠑瑚
on e.job_id = j.job_id
option (force order, loop join, maxdop 1)
--企 'Worktable'. 蟆 1, 朱Μ 所鍵 3009266
--企 '#employee___000000000006'. 蟆 1, 朱Μ 所鍵 6455
--企 'jobs'. 蟆 1, 朱Μ 所鍵 2
貎朱Μ 螳 覿 企慨. jobs 企 2 伎襯 曙螻, #employee 企 碁煙り 朱襦 6455 伎襯 jobs 企 Row 襷 曙伎 れ語 . 蠏碁覩襦 螻一朱 企慨覃 14(jobs)蟇 * 6455 = 90370 伎朱 蟆郁骸螳 . 讀, jobs 企 1 れ, #employee 企 14 れ企. 讌襷 一危磯伎 ろ 一危磯 蟆 螻旧 蟆 蠍磯蓋企襦 企 一伎 れ 蟆 蠍( 螻旧蠍) 蠍磯襦 所 觚襦轟 覦蟆 . 譴螳 るジ 碁 豺螻 れ伎 Lock企朱 蟇碁 譟壱 焔レ 蠍蟆 觜讌. 蠏碁 SQL Server 企 一危磯ゼ 曙 14覯 れ Join 一一 企 蟆 煙 碁. set statistics io on 蟆郁骸 'Worktable' 企( 蠏碁 ろ螻
TableSpool) 襷.
螻壱 覲 I/O覲企 3009266朱 襷 I/O . 企蟆 螳覃 . 企 ロ 覦 I/O 企 碁煙り 焔 Seek 3伎 I/O螳 り 螳覃 . れ
TableSpool 襷一るゼ 螳碁 覃 14(jobs)*1000000(#employee) 襷殊 れ 螳 . れ襦 碁ゼ 觜朱 牛磯伎螳 企 伎蟲 ろ螻 語一 朱 蟇煙讌 .
4 employee.job_id Non-Clustered Index襯 燕覃? #
#employee.job_id Non-Clustered Index襯 燕覃 ろ螻 企至 覲蟾? Loop Join 碁ゼ 觜手 ろ 企慨.
create index idx_job_id
on #employee(job_id)
go
--1
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from #employee e inner join jobs j
on e.job_id = j.job_id
go
--Table '#employee'. Scan count 1, logical reads 6455
--Table 'jobs'. Scan count 1, logical reads 2
蟆郁骸
Hash Join企. 朱Μ 所鍵 企 れ 襷殊企. 企 襦蟆 焔 碁煙るゼ 伎蟶朱朱 蟆碁 れ る . Loop Join 朱 Index Seek 一一襯 覲 蟆讌襷 SQL Server Loop Join
Hash Join ろ螻 豕貅磯. 蠏碁り る讌 . 磯Μ螳 #employee 企 emp_id 螳 譬讌 蠍 覓語 燕伎 碁煙る 蟇一 碁螳 . 豕豐 '#employee.job_id 碁煙るゼ 燕覃 ろ螻 企至 覲蟾?'朱 讌覓 豌願 觚 螳 蟆伎.
5 employee.job_id Clustered Index襯 燕覃? #
employee.job_id Clustered Index襯 燕覃 覦. Clustered Index 轟煙 郁屋螻襴螳 Clustered Index朱
Merge Join 螳レ煙 , jobs 企
Hash Join 螳ル 襷れ . 覓朱 Loop Join
Merge Join襷殊企 螳レ煙 . 2000覯 蟆曙磯 企蟆 讓 郁屋螻襴螳 朱
Merge Join朱 ろ螻 碁. 讌襷 2000危 覯 Loop Join朱 ろ螻 襴 螳レ煙 .
select e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from #employee e inner join jobs j
on e.job_id = j.job_id
go
7 Network Join #
覿磯 覯 蟆曙 覓朱Μ朱 覿襴 覯 螳 企螻 譟一誤 蟆曙郁 豺 蟆 . 企 蟆曙磯ゼ 'Network Join'企手 . れ るゼ 覲伎.
SELECT
, A.Col1
, A.Col2
, (SELECT TOP 1 Col3 FROM Remote.TestDB.dbo.TableB WHERE A.Key = B.Key) Col3
FROM dbo.TabeA A
WHERE A.Col4 BETWEEN 1 AND 1000
--蟆郁骸 1000蟇
蟆郁骸螳 1000蟇伎企朱 Localhost Remote Server螳 ろ語 血 1000 譯手 覦朱伎 Loop Join 蟆 . 襷れ 譬 蟆曙一企. 磯殊 企蟆 ろ語襯 牛 血 譯手 覦朱伎 Join 蟆覲企 蟆 覯 郁屋 企 れ伎 螳語 企 伎 , 企螻 Join 蟆 觜襯 蟆曙磯 . 螳ロる Network Join 覦る ろ螻 危ｴ覲伎 . 螳ロ覃 蟆 るゼ 襾殊 暑 覦覯 豬 蟆 覦讌.
