Transcript SQLWorld_임시테이블_테이블변수.ppt

임시 저장소

임시 저장소
- 임시 테이블, 테이블 변수, 뷰, 인라인 테이블값 사용자 정의 함수,
파생 테이블, 공통 테이블식(CTE)

임시 저장의 필요성
- 성능상의 이슈
- 중간 결과의 보존 필요
- 프로그램 구현상 용이성
- 로직 해독의 편이

임시 저장 사례
- 일정 기준에 따라 집계된 데이터를 통한 재처리 쿼리
- 페이징 처리용 쿼리
- 중간 결과를 활용한 쿼리
- CUBE/ROLLUP의 재사용
- 계층구조(BOM) 구현
- 오류나 특정 로직에 의한 예외 처리용
SQLWorld.pe.kr
임시 테이블과 테이블 변수

임시 테이블
- 임시로 데이터를 저장하기 위한 테이블 Object
- CREATE TABLE “#table_name”으로 생성
- Tempdb에 저장(?)
- OBJECT_ID로 확인

임시 테이블의 종류
- 로컬 임시 테이블 : # 사용
세션에 독립적
세션 연결이 끊기면 삭제
- 전역 임시 테이블 : ## 사용
모든 세션에서 사용
생성 세션이 끊기고 사용중인 세션 없을 시 삭제

테이블 변수
- 임시 테이블을 저장하기 위한 테이블 Variable
- “DECLARE @table_name TABLE” 사용
- 메모리에 저장(?)
- 확인 불가(?)
SQLWorld.pe.kr
Tempdb로의 물리적 저장
저장 여부
• 임시 테이블과 테이블 변수 모두
- 현재 DB Context와 상관없이 tempdb에 생성
- 메모리 여유 있을 시 캐시에, 없을 시 tempdb에 저장
저장 확인
• 임시 테이블
- tempdb.dbo.sysobject에서 확인
• 테이블 변수
- tempdb.information_schema.tables에서 확인
SQLWorld.pe.kr
참조 범위와 가시성
참조 수준
• 임시 테이블
- 생성된 세션에서만 사용 가능
- 생성된 세션에서 호출되는 하위 수준의 배치도 사용 가능
참조범위? 가시성?
SET CONTEXT_INFO?
- 상위 수준에서는 사용 불가
- 세션 종료 시 자동 삭제
• 테이블 변수
- 생성된 세션에서만 사용 가능
- 상위/하위 수준 배치에서 접근 불가
- 세션 종료 시 해제
SQLWorld.pe.kr
트랜잭션 컨텍스트
트랜잭션과 잠금
• 임시 테이블
- 외부 트랜잭션의 일부로 동작
외부 트랜잭션?
- 롤백 지원, 롤포워드 지원 않음
문장 단위 롤백?
- 로그 기록
- 잠금은 최소한으로 유지
• 테이블 변수
- 외부 트랜잭션으로 동작하지 않음
- 문장 단위 롤백 기능 지원
- 로그 기록
SQLWorld.pe.kr
통계
통계 정보 관리 및 성능
• 임시 테이블
- 분포 통계 정보 및 카디널리티 정보 관리
- 실행 계획 최적화, 성능에 유리
- 임시 테이블 통계로 인한 재컴파일 유발
- 통계 관리 비용, 재컴파일 비용 비교
카디널리티?
재컴파일 임계값(RT)?
KEEP PLAN?
2000과 2005의
recompile수준?
• 테이블 변수
- 통계 정보 유지 하지 않음
- 비효율적 실행 계획 생성
- 재컴파일 유발 가능성 적음
SQLWorld.pe.kr
사용 제한
제한사항
• 다른 세션의 호출
- 임시 테이블, 테이블 변수 모두 불가
• 하위 배치 사용
임시 테이블 이름 확인
(resolution)?
- 임시 테이블만 가능
- Resolution으로 혼란 발생
• 인덱스 생성
- 임시 테이블은 모두 가능
- 테이블 변수는 PK, UK외에 인덱스 생성 불가
• 스키마 변경
- 임시 테이블은 생성 후 alter로 스키마 변경 가능
- 테이블 변수는 declare 이후 변경 불가
• insert~ exec 사용
- 임시 테이블 가능
- 테이블 변수도! 가능
SQLWorld.pe.kr
Tempdb 고려 사항
Tempdb 최적화
• Tempdb 최적화 필요성
- 다양한 저장 내역으로 병목 현상 발생
- tempdb에 대한 경합 발생
• 전용 디스크 어레이 사용
- 전용 디스크 어레이 고려(spindle 높임)
- RAID 1+0 최적
• tempdb 증가에 따른 자동 증가 부하 발생
- 재시작시 적절한 기본 크기 미리 설정
• 재시작시 tempdb는 재생성
- tempdb의 모든 개체는 휘발성
- 영구 전역 임시 테이블 필요시 sp_procoption에서 설정
• 다수 사용자, 동시 세션에서의 경합 현상 해결
- CPU개수만큼 tempdb 데이터 파일 생성
- CPU당 스레드 생성으로 경합 감소
SQLWorld.pe.kr