1 환경

2 Hash Match/Cache

3 두 쿼리의 실행계획 비교

4 SQL Server 2005 이후 버전에서는..

다음과 같은 환경에 놓여져 있다.

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.

--쿼리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.

단순히 논리적 읽기 수만 비교했을 때는 같지만, 연산의 횟수는 눈에 띄게 줄었다. 대략 20배이상. 루프를 2,000번 도는 것과 200,000번 도는 것은 분명히 성능차이가 발생한다.

참고로 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를 사용하는 쿼리에서만 동작한다.