Transcript 커서기반솔루션과_집합기반솔루션의_비교.ppt

PASS2006 기획시리즈#2
커서기반솔루션과
집합기반솔루션의 비교
성대중
필라넷 / DB사업부
책임컨설턴트
강사 소개
성대중
Email: [email protected]
Blog: blog.naver.com/dreamania_73
근무 이력
현) 필라넷 / DB 사업부 / 책임 컨설턴트
전) 영림원 소프트랩 ERP 컨설턴트 / 개발자
주요 업무
SQL Server 컨설팅 / 기술지원 / 개발 / 교육
강사 활동
SQL Server 아카데미,TechNet 웹캐스트, HandsOnLab 등
출판물
SQL Server 2005 관리자 가이드 (Microsoft Korea)
SQL Server 2005 개발자 가이드 (Microsoft Korea)
SQL Server 2005 포켓 컨설턴트 관리자용(정보문화사)
inside SQL Server 2005 T-SQL Programming(정보문화사)
PASS 소개
• Professional Association for SQL Server
• 전세계 SQL Server 전문가 커뮤니티
• 매년 정기적으로 PASS Submit 행사 진행
– 미국, 유럽, 일본 등
• SQL Server 관련 정보 교환/멤버십 서비스
• www.sqlpass.org 웹사이트 참조
PASS 2006 - Seattle
목표
• 커서 기반 솔루션과 집합 기반 솔루션의
선택기준을 제시
– 커서 기반 솔루션과 집합 기반 솔루션 비교
– 집합 기반 솔루션 시나리오
– 커서 기반 솔루션 시나리오
– 커서를 대체하기 위한 SQL Server 2005 새로운 기능
• 관건은 프로그래머의 성숙도
이 주제를 이해하는 데 필요한 지식
• 커서기반(Cursor-Based) 쿼리에 대한 기본 지식
• 집합기반(Set-Based) 쿼리에 대한 기본지식
• SQL Server 2005의 새로운 T-SQL 구문
100
개념 및 소
개 수준
200
중간 수준
300
고급 수준
200에
Level 200
100에
더하여
선수 지식 불
더하여
능숙한 사용
필요
기술적 세부
경험,
사항 설명
아키텍처
지식 필요
400
전문가
수준
목차
• 커서 기반 솔루션과 집합기반 솔루션
비교
• 집합 기반 솔루션 시나리오
• 커서 기반 솔루션 시나리오
커서기반 솔루션과 집합기반 솔루션 비교
배경지식
• 커서 기반 솔루션은 절차적/반복적 로직 사용
• 집합 기반 솔루션은 다수의 대안 중에서 최적의
접근방법을 적용(집합적 로직)
• 집합기반 솔루션의 장점
–
–
–
–
행 단위 처리의 작업부하 제거
쿼리 최적화 프로그램에서 최적의 실행계획을 선택가능
“어떻게”가 아니라 “무엇을”에 집중
더 적은 코드, 유지보수 용이
• 커서기반 솔루션의 장점
– 각 행별로 처리해야 하는 시나리오에 적합
– 정렬 기준 액세스가 가능
데모
커서의 작업부하
목차
• 커서 기반 솔루션과 집합기반 솔루션 비교
• 집합 기반 솔루션 시나리오
• 커서 기반 솔루션 시나리오
집합기반 솔루션 시나리오
집합기반 솔루션
• 관계형 데이터베이스의 기본 전제
• 대부분의 비즈니스 요구사항은 집합 기반
솔루션으로 해결가능
• 더 적은 코드, 더 적은 유지보수 노력
• 대부분 더 빠른 성능
• 절대는 없다!
집합기반 솔루션 시나리오
시나리오-활동중지상태의 거래처 찾기
• 요구사항:
– Shippers 테이블과 Orders 테이블 사용
– 2001 년 1월 1일 이후 활동중지상태의 거래처 찾기
– 활동중지상태의 정의
• 2001 년 1월 1일 이후 주문 건이 없는 거래처
– 주문 건이 없는 신규 거래처는 무시
• 전제조건
– 최적화된 인덱스 존재: Orders(shipperid, orderdate)
집합기반 솔루션 시나리오
커서기반 솔루션(27초)
DECLARE @sid AS VARCHAR(5), @od AS DATETIME,
@prevsid AS VARCHAR(5), @prevod AS DATETIME;
DECLARE ShipOrdersCursor CURSOR FAST_FORWARD FOR
SELECT shipperid, orderdate FROM dbo.Orders
ORDER BY shipperid, orderdate;
OPEN ShipOrdersCursor;
FETCH NEXT FROM ShipOrdersCursor INTO @sid, @od;
SELECT @prevsid = @sid, @prevod = @od;
WHILE @@fetch_status = 0
BEGIN
IF @prevsid <> @sid AND @prevod < '20010101' PRINT @prevsid;
SELECT @prevsid = @sid, @prevod = @od;
FETCH NEXT FROM ShipOrdersCursor INTO @sid, @od;
END
IF @prevod < '20010101' PRINT @prevsid;
CLOSE ShipOrdersCursor;
DEALLOCATE ShipOrdersCursor;
집합기반 솔루션 시나리오
집합기반 솔루션#1
솔루션 쿼리
SELECT shipperid
FROM dbo.Orders
GROUP BY shipperid
HAVING MAX(orderdate) < '20010101';
실행계획
성능측정결과
•
•
경과시간 : 1 초
논리적 읽기: 2,730 페이지
집합기반 솔루션 시나리오
집합기반 솔루션#2
솔루션 쿼리
SELECT shipperid
FROM (SELECT shipperid,
(SELECT MAX(orderdate)
FROM dbo.Orders AS O
WHERE O.shipperid = S.shipperid) AS maxod
FROM dbo.Shippers AS S) AS D
WHERE maxod < '20010101';
실행계획
성능측정결과
• 경과시간 : 1 초, 논리적 읽기: 2,730
집합기반 솔루션 시나리오
집합기반 솔루션#3
솔루션 쿼리
SELECT shipperid
FROM (SELECT shipperid,
(SELECT MAX(orderdate)
FROM dbo.Orders AS O
WHERE O.shipperid = S.shipperid) AS maxod
FROM dbo.Shippers AS S) AS D
WHERE COALESCE(maxod, '20010101') < '20010101';
실행계획
성능측정결과
• 경과시간 : 50 ms 이하, 논리적 읽기: 36
집합기반 솔루션 시나리오
집합기반 솔루션#4
솔루션 쿼리
SELECT shipperid
FROM dbo.Shippers AS S
WHERE NOT EXISTS
(SELECT * FROM dbo.Orders AS O
WHERE O.shipperid = S.shipperid AND O.orderdate >= '20010101')
AND EXISTS
(SELECT * FROM dbo.Orders AS O WHERE O.shipperid = S.shipperid);
실행계획
성능측정결과
• 경과시간 : 50 ms 이하, 논리적 읽기: 36
집합기반 솔루션 시나리오
집합기반 솔루션#4
솔루션 쿼리
SELECT shipperid
FROM (SELECT shipperid,
(SELECT TOP (1) orderdate
FROM dbo.Orders AS O
WHERE O.shipperid = S.shipperid
ORDER BY orderdate DESC) AS maxod
FROM dbo.Shippers AS S) AS D
WHERE maxod < '20010101';
실행계획
성능측정결과
• 경과시간 : 50 ms 이하, 논리적 읽기: 36
데모
집합기반 솔루션 시나리오

활동중지상태의 거래처 찾기
(커서 제거 튜닝사례)
목차
• 커서 기반 솔루션과 집합기반 솔루션 비교
• 집합 기반 솔루션 시나리오
• 커서 기반 솔루션 시나리오
커서 기반 솔루션 시나리오
커서 기반 솔루션
• 제한된 용도로 사용
• 행 단위 처리가 필요한 경우
– 테이블의 행별로 저장 프로시저를 호출해야 하는
경우
– 테이블의 행별로 결과집합을 반환해야 하는 경우
• 정렬기준 액세스가 필요한 경우
• 대부분은 집합기반 솔루션으로 전환가능
• 커서기반솔루션이 더 빠른 특이한 경우 존재
커서 기반 솔루션 시나리오
행 단위 처리
• 정말 행 단위 처리가 필요한가?
– 집합기반 솔루션으로 전환가능한지 확인
• 대안
– 커서보다
기준으로 한 루프구조가 더 빠르다!
– SQL Server 2005의 APPLY 테이블 연산자
예제)
SELECT F.*
FROM dbo.T1
CROSS APPLY dbo.fn1(T1.col1, T1.col2) AS F;
데모
행 단위 처리


커서와 WHILE LOOP 비교
APPLY 테이블 연산자
커서 기반 솔루션 시나리오
정렬 기준 액세스
• 커서를 사용하면 정렬된 순서로 액세스 가능
– 집합기반 솔루션에서는 명시적으로 ORDER BY 절을
지정하지 않는 한 정렬 순서를 보장하지 않음
– 커서의 작업부하보다 집합기반 솔루션에서 데이터를
처리하기 위해 스캔 해야 하는 작업부하가 더 큰
경우에는 커서 기반 솔루션이 더 빠르다
• 정렬 기준 액세스가 필요한 사례
– 누적 집계
– 매핑 문제
• 정렬기준 액세스를 지원하기 위한 새로운 기능
커서 기반 솔루션 시나리오
정렬 기준 액세스를 지원하기 위한 새 기능
• ANSI에서도 정렬기준액세스 지원 기능 필요
인정
• OVER 절은 순위, 집계함수에서 정렬기준
액세스를 지원하기 위한 ANSI 표준
• SQL Server 2005에서는 순위함수에서만
OVER절을 지원
• SQL Server 2005에서는 APPLY 테이블 연산자
지원 (T-SQL 확장기능)
커서 기반 솔루션 시나리오
정렬 기준 액세스-누적합계계산
• 누적합계계산(RUNNING
AGGREGATION)이란?
Running Aggregations Benchmark
400
– 온라인 설명서에서는
실행집계로 번역
– 사례
• 통장잔고
• 커서 솔루션이 집합
솔루션보다 더 빠름!
320
280
Run Time (Sec)
– 정렬된 행별로 누적 값을 계산
360
240
200
160
120
80
40
0
0
10
20
30
40
50
60
70
Rows (thousands)
set-based
cursor
80
90
100
커서 기반 솔루션 시나리오
정렬 기준 액세스-매핑 문제
• 세미나 장소 대관 시나리오
Rooms
R101
40
• 목표
R104
Events
– 최소한의 좌석이 비어 있도록 세미나실을
배정 알고리즘
– 커서 선언(이벤트, 세미나실) – 오름차순
– 최소인원 이벤트로부터 시작해서 해당
이벤트를 수용할 수 있는 최소공간
세미나실을 찾음
R203
2
50
48
R302
6
55
48
R303
4
increasing
• 처리절차
40
55
98
B102
1
100
203
R202
3
100
212
B301
5
600
892
B201
1000
B101
1500
C001
2000
– 찾으면 테이블변수에 저장하고, 없으면
오류 발생
Event
– 반복처리 종료되면 결과반환
Event
No Match
Room
Match
Room
데모
커서기반 솔루션 시나리오


누적 집계
매핑 문제
세션 요약
• 적재적소(適材適所)
• 대부분 커서보다는 집합기반 솔루션이 빠르다!
• 집합기반 솔루션보다 커서가 빠른 특이한
경우가 존재한다
• 프로그래머의 성숙도가 관건이다
• SQL Server 2005의 새로운 기능
– OVER 절
– APPLY 테이블 연산자
참고 자료
• PASS 2006 – AD403 Cursor Based vs. Set Based
• Inside SQL Server 2005 T-SQL Programming
• Inside SQL Server 2005 T-SQL Querying
• http://www.microsoftelearning.com
추천서적: Microsoft Press
IT 전문가를 위한 고급 정보
최신 기술서적에 대한 정보는 여기서 참조하세요.
www.microsoft.com/learning/books/
참고자료 :
SQL Server 활용 리소스
- 도움 받을 수 있는 자료는?
SQL Server 관련 유용한 웹사이트
SQL Server Product
Homepage
SQL Server Tech
Center
SQL Server Developer
Center
SQL Server Support
Center
SQL Server Download
Center
SQL Server Resource
Center
http://www.microsoft.com/korea/sql
http://www.microsoft.com/korea/technet/prodtechnol/sql
http://www.microsoft.com/Korea/MSDN/sql
http://www.support.microsoft.com/ph/2855
http://www.microsoft.com/downloads/Browse.aspx?displaylang=ko&productID=
261BA873-F3AB-420E-96D6-E3004596A551
http://www.microsoft.com/korea/sql/prodinfo/SQL2005_Resources.mspx
SQL Server Newsgroup
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
SQL Server Community
Sites
http://www.microsoft.com/korea/communities/related/windows_server_communi
ty.mspx#server
Microsoft Partner Portal
http://partner.microsoft.co.kr/pds/Mp_Pds.asp
SQL Server 3rd Party Tools
SQL Server 포켓가이드
<완간>
SQL Server 2005 데이터 통합 가이드
<근간>
SQL Server 2005 고가용성 가이드
SQL Server 2005 튜닝 가이드
SQL Server 2005 트러블슈팅 가이드
SQL Server 상업용 서적
SQL Server 교육 과정
SQL Server Product Homepage
어디일까요?
http://www.microsoft.com/korea/sql
SQL Server Tech Center
http://www.microsoft.com/korea/technet/prodtechnol/sql
SQL Server 개발자 센터
http://www.microsoft.com/Korea/MSDN/sql
SQL Server Support Center
http://www.support.microsoft.com/ph/2855
SQL Server Download Center
http://www.microsoft.com/downloads/Browse.aspx?displaylang=ko&productID=261BA873-F3AB-420E-96D6E3004596A551
SQL Server 각종 자료
http://www.microsoft.com/korea/sql/prodinfo/SQL2005_Resources.mspx
Microsoft Partner Portal
http://partner.microsoft.co.kr/pds/Mp_Pds.asp
SQL Server Newsgroup
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
SQL Server Community Sites
http://www.microsoft.com/korea/communities/related/windows_server_community.msp
x#server
SQL Server 관련 국내 사이트 SQL Server 관련 해외 사이트
www.sqlleader.com
www.sqlservercentral.com
www.sqler.pe.kr
www.sqljunkies.com
www.sql-serverperformance.com
www.microsoft-oracle.com
www.mssql.org
www.sqlworld.pe.kr
www.olapforum.com
www.devpia.com
www.mcpworld.com
www.dbguide.net
www.databaser.net
www.analysisservice.net