컴퓨토피아

테이블 변수를 사용하는 방법과 임시 테이블을 사용하는 방법 간에는 큰 성능상의 차이가 있다. 대부분의 경우에 임시 테이블이 테이블 변수보다 속도가 빠르다. 쿼리를 테이블 변수를 사용하여 작성한 경우에는 SMP(동기 다중 프로세서) 환경하에서도 병렬처리 쿼리 계획을 생성하지 않게 되지만, 실제로 동일한 역할을 하는 쿼리를 로컬 또는 전역 임시 테이블을 사용하여 작성하게 되면 병렬처리 쿼리 계획을 생성학기 때문에 성능상의 차이가 발생하게 된다.

SQL 서버 개발팀의 고위 관계자는 테이블 변수를 사용하게 되면 내부적인 메타 데이터를 사용하기 때문에, 테이블 변수를 사용하게 되면 SQL서버 엔진내에서 병렬처리 쿼리 계획을 생성하지 못한다는 정보를 제공해 주었다. 또한 SQL 서버는 임시 테이블을 포함하고 있는 쿼리에 대한 통계값은 지속적으로 관리하게 되지만, 테이블 변수를 포함하고 있는 쿼리에 대한 통계값은 유지하지 않는다. 통계값이 없기 때문에 SQL서버는 테이블 변수를 포함하고 있는 쿼리에 대해서 잘못된 쿼리 처리 계획을 생성할 수도 있다. 또한 SQL 서버 개발팀에서는 SQL서버의 테이블 변수는 가능하다면 소규모의 쿼리나 데이터 집합을 위한 용도로 사용하고, 대용량 데이터 집합을 처리하기 위해서는 임시 테이블을 사용하는 것이 바람직하다고 권고한다.

테이블 변수에 대한 이러한 권고는 지금까지 마이크로소프트 측에서 테이블 변수는 메모리 내에서 생성되기 때문에 좀 더 빠른 성능을 제공한다는 주장과 일부 상충하는 면이 있다. 하지만 실제로 테이블 변수의 경우도 임시 테이블과 거의 유사한 디스크 I/O를 발생시킨다.

실제로 테이블 변수를 사용할 것인지 임시 테이블을 사용할 것인지를 결정하려고 할 때는 실제 환경에서 두 가지 경우를 모두 구현한 다음 서로 비교해 보고 선택하는 것이 바람직하다. 결과집합이 커질수록 임시 테이블을 사용하는 것이 더 빠른 성능을 제공한다는 점에 대해서는 약간의 의문을 가지고 있다.

http://support.microsoft.com/default.aspx?scid=kb;kr;305977

질문 1. 임시 테이블을 이미 사용할 수 있는데 테이블 변수를 소개한 이유는 무엇인가?

대답 1. 테이블 변수는 임시 테이블에 비해 다음과 같은 장점이 있다.

- SQL Server 온라인 설명서의 "테이블" 문서에서 언급한 것처럼 로걸 변수 같은 테이블 변수에는 잘 정의된 범위가 있으며, 종료할 때

  자동으로 정리된다.

- 테이블 변수는 임시 테이블에 비해 저장 프로시저(stored procedure)를 재컴파일하는 횟수가 적다.

- 테이블 변수가 포함된 트랜잭션은 테이블 변수의 업데이트 기간 동안만 지속된다. 따라서 테이블 변수에는 잠금과 로깅 리소스가 덜

  필요하다. 테이블 변수의 범위는 제한되어 있고, 영구 데이터베이스의 일부가 아니기 때문에 트랜잭션 롤백의 영향을 받지 않난다.

질문 2. 임시 테이블을 사용할 때보다 테이블 변수가 저장 프로시저를 재컴파일하는 횟수가 적다는 것은 무슨 뜻인가?

대답 2. 저장 프로시저에서 임시 테이블을 사용하면 프로시저가 실행될 때마다 저장 프로시저가 재컴파일 될 수 있다.

          다음의 요구 사항을 충족하도록 저장 프로시저를 변경하여 이러한 문제가 발생하지 않도록 한다.

- 임시 테이블의 이름을 포함하는 모든 문은 다음과 같은 저장 프로시저에서 만들어진 임시 테이블을 참조한다. 호출하거나 호출되는 저장

  프로시저에서 만들어진 임시 테이블 또는 EXECUTE문이나 sp_executesql 저장 프로시저를 사용하여 실행되는 문자열에서 만들어진

  임시 테이블은 참조하지 않는다.

- 임시 테이블의 이름을 포함하는 모든 문은 구문적으로 저장 프로시저 또는 트리거에 있는 임시 테이블 다음에 나타난다.

- SELECT문이 임시 테이블을 참조하는 DECLARE CURSOR문은 없다.

- 임시 테이블의 이름을 포함하는 모든 문은 임시 테이블을 참조하는 모든 DROP TABLE문보다 앞에 온다.

  DROP TABLE문은 저장 프로시저에서 만들어진 임시 테이블에는 필요하지 않다. 프로시저가 완료되면 테이블은 자동으로 삭제된다.

- 임시 테이블을 만다는 문(CREATE TABLE 또는 SELECT ... INTO)은 IF ... ELSE 또는 WHERE과 같은 흐름 제어 문에는 나타나지

  않는다.

        위와 같은 제한은 테이블 변수에 적용되지 않는다.

        테이블 변수는 CREATE 또는 ALTER 문이 실행될 때 '재확인'이 발생하도록 만드는 배치에만 해당된다. 이것은 임시 테이블에 발생

        할 수 있다. 임시 테이블은 중첩된 저장 프로시저에서 테이블을 참조할 수 있도록 '재확인'할 필요가 있다. 테이블 변수는 저장

        프로시저가 이미 컴파일된 계획을 사용할 수 있도록 이 과정을 완벽하게 피하므로 저장 프로시저를 처리할 리소스가 절약된다.

질문 3. 테이블 변수의 단점은 무엇인가?

대답 3. 임시 테이블과 비교하여 다음과 같은 몇가지 단점이 있다.

- PRIMARY 또는 UNIQUE 제약 조건을 위해 만든 시스템 인덱스 이외에 테이블 변수에는 클러스터되지 않은 색인을 만들 수 없다.

  따라서 클러스터되지 않은 인덱스가 있는 임시 테이블과 비교할 때 쿼리 성능에 영향을 미칠 수 있다.

- 테이블 변수는 임시 테이블에서 할 수 있는 것처럼 통계를 유지하자 않는다. 자동 만들기를 통해서 CREATE STATISTICS문을 사용하여

  테이블 변수에 통계를 만들 수 없다. 따라서 큰 테이블에서 복잡한 쿼리를 수행하는 경우 통계가 없으면 최적화 프로그램이 쿼리를 위한

  최적의 계획을 확인하는 것을 방해하여 해당 쿼리의 성능이 영향을 받을 수 있다.

- 초기 DECLARE문 다음에 테이블 정의를 변경할 수 없다.

- INSERT EXEC 또는 SELECT INTO문에서 테이블 변수를 사용할 수 없다.

- 테이블 유형 선언에서 CHECK 제약조건, DEFAULT 값 및 계산된 열은 사용자 정의 함수를 호출할 수 없다.

- EXEC문 또는 sp_executesql 저장 프로시저 외부에서 테이블 변수를 만든 경우 EXEC문 또는 sp_executesql 저장 프로시저를 사용하여 테이블 변수를 참조하는 동적 SQL Server 쿼리를 실행할 수 없다. 테이블 변수는 로컬 범위에서만 참조할 수 있기 때문에 EXEC문과 sp_executesql 저장 프로시저는 테이블 변수의 범위 밖에 있게 된다. 그러다 테이블 변수 로컬 범위가 EXEC문이나 sp_executesql 저장 프로시저에 있기 때문에 EXEC문이나 sp_executesql 저장 프로시저 내에서 테이블 변수를 만들고 모든 처리를 수행할 수 있다.

질문 4. 실제 디스크에 있는 데이터베이스에서 유지되기 때문에 임시 또는 영구 테이블에 비해 향상된 성능을 보장하는 테이블 변수 메모리 전용 구조가 있나?

답변 4. 테이블 변수는 메모리 전용 구조가 아니다. 테이블 변수에는 메모리에 저장할 수 있는 것보다 많은 데이터를 저장할 수 있기 때문에 디시크에 데이터를 저장할 위치가 있어야 한다. 테이블 변수는 임시 테이블과 유사한 tempdb 데이터베이스에 만들어 진다. 메모리를 사용할 수 있는 경우 테이블 변수와 임시 테이블 모두 메모리에 만들어지고 처리된다.(데이터 캐시)

질문 5. 임시 테이블 대신 테이블 변수를 사용해야 하나?

답변 5. 대답은 다음 세가지 요소에 따라 달라진다.

- 테이블에 삽입된 행 수

- 쿼리가 저장된 재컴파일 수

- 쿼리 유형 및 성능에 대한 인덱스와 통계의 종속성

경우에 따라 임시 테이블이 있는 저장 프로시저를 작은 저장 프로시저로 나누면 재컴파일이 더 작은 단위로 발생하므로 유용하다.

일반적으로 상당히 많은 양의 데이터가 있고 테이블 사용이 반복될 때를 제외하고는 가능하면 테이블 변수를 사용하는 것이 좋다. 이 경우 임시 테이블에 인덱스를 만들어 쿼리 성능을 높일 수 있다. 그러나 각 시나리오는 다를 수 있다. 테이블 변수가 특정 쿼리나 저장 프로시저에 대한 임시 테이블 보다 유용한지 테스트 하는 것이 좋다.