최근에 프로젝트 진행하면서 APPLY 연산자를 적용해볼만한 사례가 있었습니다. 간단히 APPLY 연산자에 대해서 소개를 드리고, 테스트했던 내용을 보기로 하겠습니다.
BOL 내용을 바탕으로 APPLY 연산자에 대해서 간단히 소개드립니다. (BOL에서 발취)
APPLY 연산자를 사용하면 쿼리의 외부 테이블 식에서 반환한 각 행에 대해 테이블 반환 함수를 호출할 수 있습니다. 테이블 반환 함수는 오른쪽 입력이 되고 외부 테이블 식은 왼쪽 입력이 됩니다. 오른쪽 입력은 왼쪽 입력의 각 행에 대해 평가된 후 생성된 행이 조합되어 최종 출력에 표시됩니다. APPLY 연산자에 의해 생성되는 열 목록은 왼쪽 입력의 열 집합 뒤에 오른쪽 입력에서 반환된 열 목록을 추가한 것입니다.
APPLY 연산자에는 CROSS APPLY와 OUTER APPLY라는 두 가지 형태가 있습니다.
> CROSS APPLY는 테이블 반환 함수로부터 결과 집합을 생성하는 외부 테이블의 행만 반환합니다.
> OUTER APPLY는 결과 집합을 생성하는 행과 그렇지 않은 행을 모두 반환하고, 테이블 반환 함수에 의해 생성된 열에는 NULL 값을 표시합니다.
APPLY 연산자 문법은 아래와 같습니다.
left_table_source { CROSS | OUTER } APPLY right_table_source
> APPLY 연산자의 좌우 피연산자는 모두 테이블 식입니다. 이 피연산자 간의 주된 차이점은 right_table_source가 left_table_source의 열을 함수의 인수 중 하나로 사용하는 테이블 반환 함수를 사용할 수 있다는 것입니다.
> left_table_source는 테이블 반환 함수를 포함할 수 있지만 right_table_source의 열인 인수는 포함할 수 없습니다.
> left_table_source의 각 행에 대해 right_table_source를 계산하여 행 집합을 생성합니다.
> right_table_source의 값은 left_table_source에 따라 달라집니다. right_table_source는 TVF(left_table_source.row)와 같이 표현될 수 있습니다. 여기서 TVF는 테이블 반환 함수입니다.
APPLY에 대해서 간단히 요약해보았습니다.
|
항목 |
내용 |
|
장점 |
서브쿼리와는 다르게 왼쪽 테이블의 데이터를 기준으로 오른쪽에서 개별 데이터마다 여러 행의 결과를 가지고 올 수 있음 |
|
단점 |
NL Join 형태로 동작하기 때문에 대량 데이터 처리 시 비용이 높다. Join으로 처리가 가능한 경우는 굳이 APPLY를 사용하기를 추천하지 않으며, 반드시 필요한 경우에만 제한적으로 사용하기를 권장함. |
|
특이사항 |
APPLY를 사용하려면 데이터 베이스 호환성 수준이 적어도 90 이어야 함. |
그럼 이제 사례를 가지고 테스트를 진행해보도록 하겠습니다.
[구현 기능에 대한 소개]
1. 웹 게시판의 게시물 리스트 화면을 구현합니다. (하나의 페이지는 5개의 글로 구성됨)
2. 리스트 화면에서 각 게시물별로 최근에 등록된 댓글 3개를 추가로 보여주어야 합니다.
3. 테스트 내용은 게시물 리스트 화면에 보여줄 글 번호를 알고 있는 상태에서 2번 항목만 구현하였습니다.
4. 테스트는 APPLY, Rank 함수, CTE를 사용하여 진행하여 보았습니다.
[테스트 테이블 생성]
use tempdb
go
drop table Article
drop table Article_comment
-- 게시물 테이블 생성
create table Article (
ArticleNo int identity (1,1)
, title varchar(1000)
, Contents varchar(1000)
)
create unique clustered index idx_Article_ArticleNo on Article (ArticleNo)
-- 테스트 데이터 입력(게시물)
insert into Article (title, contents)
select 'title' + replicate('1', 100), replicate('1', 1000)
union all select 'title' + replicate('2', 100), replicate('2', 1000)
union all select 'title' + replicate('3', 100), replicate('3', 1000)
union all select 'title' + replicate('4', 100), replicate('4', 1000)
union all select 'title' + replicate('5', 100), replicate('5', 1000)
union all select 'title' + replicate('6', 100), replicate('6', 1000)
union all select 'title' + replicate('7', 100), replicate('7', 1000)
union all select 'title' + replicate('8', 100), replicate('8', 1000)
union all select 'title' + replicate('9', 100), replicate('9', 1000)
union all select 'title' + replicate('0', 100), replicate('0', 1000)
create table Article_comment (
ArticleNo int not null
, commentNo int identity(1,1)
, contents varchar(1000)
)
create clustered index idx_Article_comment_ArticleNo_commentNo on Article_comment (ArticleNo, CommentNo)
declare @articleNo int
set @ArticleNo = 0
while (@ArticleNo < 10)
begin
insert into Article_comment (ArticleNo, contents)
select @ArticleNo, replicate('1', 1000)
union all select @ArticleNo, replicate('2', 1000)
union all select @ArticleNo, replicate('3', 1000)
union all select @ArticleNo, replicate('4', 1000)
union all select @ArticleNo, replicate('5', 1000)
union all select @ArticleNo, replicate('6', 1000)
union all select @ArticleNo, replicate('7', 1000)
union all select @ArticleNo, replicate('8', 1000)
union all select @ArticleNo, replicate('9', 1000)
union all select @ArticleNo, replicate('0', 1000)
set @ArticleNo = @ArticleNo + 1
end
[테스트 데이터 확인]
select * from Article with (nolock)
select * from Article_comment with (nolock)
[CPU, IO 사용 지표 설정]
set statistics io on
set statistics time on
[APPLY 연산자를 사용한 구현]
select o.ArticleNo, o.CommentNo, o.Contents
from Article as a with (nolock)
cross apply
( select top 3 b.*
from Article_comment b with (nolock)
where a.ArticleNo = b.ArticleNo
order by b.CommentNo desc ) as o
where a.ArticleNo in ( 1, 2, 4, 6, 9)
[CTE를 이용한 구현]
with comment_cte (articleNo, commentNo, Depth, RankNo)
as
(
select ArticleNo, 0 commentNo, 0 as Depth, cast(0 as bigint) as RankNO
from Article as a with (nolock)
where a.ArticleNo in ( 1, 2, 4, 6, 9)
union all
SELECT b.ArticleNo, b.CommentNo, Depth + 1, RANK() over (partition by b.articleNo order by b.commentNo desc) as RankNo
FROM dbo.Article_comment AS b with (nolock, index(idx_Article_comment_ArticleNo_commentNo ))
inner join comment_cte c on b.ArticleNo = c.articleNo
where Depth = 0
)
select y.ArticleNo, y.commentNo, y.Contents
from comment_cte x
join Article_comment y with (nolock) on x.ArticleNo = y.ArticleNo and x.CommentNo = y.CommentNo
where RankNo between 1 and 3
order by x.articleNo, x.RankNo
[Rank 함수를 이용한 구현]
select y.ArticleNo, y.commentNo, y.Contents
from (
select articleNo, commentNo, rank() over (partition by ArticleNo order by commentNo desc ) RankNo
from Article_comment with (nolock)
where ArticleNo in ( 1, 2, 4, 6, 9) ) x
join Article_comment y with (nolock) on x.ArticleNo = y.ArticleNo and x.CommentNo = y.CommentNo
where x.RankNo < 4
order by x.articleNo, x.RankNo
[테스트 결과]
APPLY 연산자를 사용하는 경우 각 게시물마다 최근 3개씩 NL Join으로 가지고 오기 때문에 이번의 경우에는 쿼리 비용이 가장 적음을 볼 수 있었습니다. 논리적인 읽기수를 보았을 때, CTE에 비해서는 약 17배 이상 비용이 절감되어짐을 볼 수 있습니다. APPLY 연산자를 사용한 방식의 경우 저장된 데이터량에 상관없이 늘 동일한 비용이 들어간다는 점도 다른 방식에 비해서 장점이였습니다.
이번 테스트르 하면서 느낀 점은 다양한 연산자나 구현 방식이 존재하지만 각 방식마다 장단점을 잘 이해하고, 해당 기능을 적절하게 사용하는 것이 중요하다는 것입니다. 좋은 기술도 사용 방식이나 경우에 따라서는 더 안좋은 결과를 보여줄 수 있습니다. (APPLY 연산자를 대할 때, DMV를 볼 때만 공식처럼 사용하고, 이해나 사용하려고 하지 않았던 점에 대해서 반성했습니다. ^^)
[참고문헌]
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.ko/s10de_1devconc/html/0208b259-7129-4d9f-9204-8445a8119116.htm
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.ko/s10de_6tsql/html/36b19e68-94f6-4539-aeb1-79f5312e4263.htm
http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx