Post on 04-Jan-2016
description
성대중 필라넷 / DB 사업부책임컨설턴트
PASS2006 기획시리즈 #2 커서기반솔루션과 집합기반솔루션의 비교
강사 소개성대중 Email: djsung@feelanet.com 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 새로운 기능
• 관건은 프로그래머의 성숙도
이 주제를 이해하는 데 필요한 지식
Level 200
• 커서기반 (Cursor-Based) 쿼리에 대한 기본 지식
• 집합기반 (Set-Based) 쿼리에 대한 기본지식
• SQL Server 2005 의 새로운 T-SQL 구문
목차
• 커서 기반 솔루션과 집합기반 솔루션 비교
• 집합 기반 솔루션 시나리오• 커서 기반 솔루션 시나리오
커서기반 솔루션과 집합기반 솔루션 비교배경지식
• 커서 기반 솔루션은 절차적 /반복적 로직 사용• 집합 기반 솔루션은 다수의 대안 중에서 최적의
접근방법을 적용 ( 집합적 로직 )• 집합기반 솔루션의 장점
– 행 단위 처리의 작업부하 제거– 쿼리 최적화 프로그램에서 최적의 실행계획을 선택가능– “ 어떻게” 가 아니라 “ 무엇을” 에 집중– 더 적은 코드 , 유지보수 용이
• 커서기반 솔루션의 장점– 각 행별로 처리해야 하는 시나리오에 적합– 정렬 기준 액세스가 가능
커서의 작업부하
데모데모
목차
• 커서 기반 솔루션과 집합기반 솔루션 비교
• 집합 기반 솔루션 시나리오• 커서 기반 솔루션 시나리오
집합기반 솔루션 시나리오집합기반 솔루션
• 관계형 데이터베이스의 기본 전제
• 대부분의 비즈니스 요구사항은 집합 기반 솔루션으로 해결가능
• 더 적은 코드 , 더 적은 유지보수 노력
• 대부분 더 빠른 성능
• 절대는 없다 !
집합기반 솔루션 시나리오시나리오 - 활동중지상태의 거래처 찾기
• 요구사항 : – 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 = 0BEGIN IF @prevsid <> @sid AND @prevod < '20010101' PRINT @prevsid; SELECT @prevsid = @sid, @prevod = @od; FETCH NEXT FROM ShipOrdersCursor INTO @sid, @od;ENDIF @prevod < '20010101' PRINT @prevsid;CLOSE ShipOrdersCursor;DEALLOCATE ShipOrdersCursor;
집합기반 솔루션 시나리오집합기반 솔루션 #1
• 경과시간 : 1 초• 논리적 읽기 : 2,730 페이지
SELECT shipperidFROM dbo.OrdersGROUP BY shipperidHAVING MAX(orderdate) < '20010101';
솔루션 쿼리
실행계획
성능측정결과
집합기반 솔루션 시나리오집합기반 솔루션 #2
• 경과시간 : 1 초 , 논리적 읽기 : 2,730
SELECT shipperidFROM (SELECT shipperid, (SELECT MAX(orderdate) FROM dbo.Orders AS O WHERE O.shipperid = S.shipperid) AS maxod FROM dbo.Shippers AS S) AS DWHERE maxod < '20010101';
솔루션 쿼리
실행계획
성능측정결과
집합기반 솔루션 시나리오집합기반 솔루션 #3
SELECT shipperidFROM (SELECT shipperid, (SELECT MAX(orderdate) FROM dbo.Orders AS O WHERE O.shipperid = S.shipperid) AS maxod FROM dbo.Shippers AS S) AS DWHERE COALESCE(maxod, '20010101') < '20010101';
실행계획
성능측정결과
솔루션 쿼리
• 경과시간 : 50 ms 이하 , 논리적 읽기 : 36
집합기반 솔루션 시나리오집합기반 솔루션 #4
SELECT shipperidFROM dbo.Shippers AS SWHERE 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 shipperidFROM (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 DWHERE 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
0
40
80
120
160
200
240
280
320
360
400
0 10 20 30 40 50 60 70 80 90 100
Rows (thousands)
Run T
ime
(Sec
)set-based cursor
커서 기반 솔루션 시나리오정렬 기준 액세스 - 매핑 문제
• 세미나 장소 대관 시나리오
• 목표– 최소한의 좌석이 비어 있도록 세미나실을
배정 알고리즘
• 처리절차– 커서 선언 ( 이벤트 , 세미나실 ) – 오름차순
– 최소인원 이벤트로부터 시작해서 해당 이벤트를 수용할 수 있는 최소공간 세미나실을 찾음
– 찾으면 테이블변수에 저장하고 , 없으면 오류 발생
– 반복처리 종료되면 결과반환
2
48
6
48
4
98
1
203
3
212
5
892
R101
40
R104
40
R203
50
R302
55
R303
55
B102
100
R202
100
B301
600
B201
1000
B101
1500
C001
2000
incre
asing
Events
Rooms
No MatchRoomEvent
RoomEventMatch
커서기반 솔루션 시나리오 누적 집계 매핑 문제
데모데모
세션 요약• 적재적소 ( 適材適所 )• 대부분 커서보다는 집합기반 솔루션이 빠르다 !• 집합기반 솔루션보다 커서가 빠른 특이한 경우가
존재한다• 프로그래머의 성숙도가 관건이다• 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 PressIT 전문가를 위한 고급 정보
최신 기술서적에 대한 정보는 여기서 참조하세요 .
www.microsoft.com/learning/books/
참고자료 : SQL Server 활용 리소스- 도움 받을 수 있는 자료는 ?
SQL Server 관련 유용한 웹사이트SQL Server ProductHomepage
http://www.microsoft.com/korea/sql
SQL Server Tech Center
http://www.microsoft.com/korea/technet/prodtechnol/sql
SQL Server Developer Center
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-96D6-E3004596A551
SQL Server Resource Center
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 CommunitySites
http://www.microsoft.com/korea/communities/related/windows_server_community.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 Centerhttp://www.microsoft.com/korea/technet/prodtechnol/sql
SQL Server 개발자 센터http://www.microsoft.com/Korea/MSDN/sql
SQL Server Support Centerhttp://www.support.microsoft.com/ph/2855
SQL Server Download Centerhttp://www.microsoft.com/downloads/Browse.aspx?displaylang=ko&productID=261BA873-F3AB-420E-96D6-E3004596A551
SQL Server 각종 자료http://www.microsoft.com/korea/sql/prodinfo/SQL2005_Resources.mspx
Microsoft Partner Portalhttp://partner.microsoft.co.kr/pds/Mp_Pds.asp
SQL Server Newsgrouphttp://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
SQL Server Community Siteshttp://www.microsoft.com/korea/communities/related/windows_server_community.mspx#server
SQL Server 관련 국내 사이트 SQL Server 관련 해외 사이트
www.sqlleader.com www.sqlservercentral.com
www.sqler.pe.kr www.sqljunkies.com
www.mssql.orgwww.sql-server-performance.com
www.sqlworld.pe.kr www.microsoft-oracle.com
www.olapforum.com
www.devpia.com
www.mcpworld.com
www.dbguide.net
www.databaser.net
www.analysisservice.net