_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › HashMatch/Cache

Contents

1
2 Hash Match/Cache
3 貎朱Μ ろ螻 觜蟲
4 SQL Server 2005 危 覯..


1 #

れ螻 螳 蟆曙 .
use tempdb
go
select * into test_details from Northwind..[Order Details]
Go

create index idx_test
on test_details(ProductID, UnitPrice)
Go

select * into test from Northwind..Products
Go

create index idx_test
on test(ProductID)
go

--蟆郁骸
ProductID    Max_UnitPrice                     
----------- --------------------- 
1           18.0000
2           19.0000
3           10.0000
4           22.0000
5           21.3500
6           25.0000
7           30.0000
8           40.0000
9           97.0000
10          31.0000

朱朱 覿覿 れ 螳 蟆曙 螳 SQL 襦 譯朱 燕. 讌襷 願 80%企. Index襯 豕 伎 朱 Hash Match/Cache 一一 伎覃 譟一語 襯 譴 .
--貎朱Μ2
select 
	a.ProductID
,	Max(b.UnitPrice) Max_UnitPrice
from test a inner join test_details b
on a.ProductID = b.ProductID
where a.ProductID <= 10
group by a.ProductID
Go

--'test_details' 企. れ  10, 朱Μ 所鍵  20, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.
--'test' 企. れ  1, 朱Μ 所鍵  1, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.
hash_match_cache01.jpg

2 Hash Match/Cache #

--貎朱Μ1
select 
	a.ProductID
,	(select top 1 UnitPrice from test_details b 
where a.ProductID = b.ProductID order by UnitPrice desc)
from test a
where ProductID <= 10
Go

--'test_details' 企. れ  10, 朱Μ 所鍵  20, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.
--'test' 企. れ  1, 朱Μ 所鍵  1, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0.
hash_match_cache02.jpg

3 貎朱Μ ろ螻 觜蟲 #

hash_match_cache03.jpg

朱Μ 所鍵 襷 觜蟲 螳讌襷, 一一 蟆 譴. 20覦一伎. 襭襯 2,000覯 蟆螻 200,000覯 蟆 覿覈 焔レ姶願 覦.

4 SQL Server 2005 危 覯.. #

谿瑚襦 2005 覯 危覿磯 Hash Match/Cache れ螳 伎. 讌襷 SET STATISTICS PROFILE ON 朱 ろ螻 覲企 SQL Server 2000豌 2005, 2008 る 蟆 . 讌 Hash Match/Cache襯 覲 訖企. 企 一一朱 螳豢 蟆 螳 蠍 譬 蟆 螳豬讌 . 伎 Hash Match/Cache 螻朱ゼ 覲願鍵 伎 Index Design 譴. Hash Match/Cache Covered Index襯 貎朱Μ襷 .
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

螳 覯 螳 譬 覦覯 殊殊 酔 螳 蠏豺 蟆 狩 蟆企. (C. )