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