반응형
SQL Server에서 대량 데이터를 처리할 때,
- 원천 테이블에서 계산한 집계 결과
- 여러 단계로 분해된 복잡한 조인 결과
를 “중간 결과” 형태로 저장해두고 다시 쓰는 패턴이 자주 나옵니다.
이럴 때 주로 세 가지를 선택합니다.
- 임시 테이블 (
#temp_table) - 테이블 변수 (
@table_variable) - 공통 테이블 식 (
WITH cte_name AS (...))
이 세 가지는 메모리 사용량, 통계 정보의 유무, 인덱스 생성 가능 여부가 다르기 때문에, 성능과 실행 계획에 큰 영향을 줍니다.
실무에서는 단순히 “가독성 좋다” 수준이 아니라, 데이터 크기·사용 빈도·조인 패턴에 따라 선택해야 합니다.
반응형
1. 기본 개념과 생애 주기
1.1 임시 테이블 (Temporary Table)
# 또는 ##로 시작하는 이름을 가진 테이블입니다.#은 세션 임시 테이블, ##은 전역 임시 테이블입니다.
#temp: 현재 세션(또는 현재 스토어드 프로시저/스코프)에서만 보이고, 세션이 종료되거나DROP TABLE시까지 유지됩니다.##temp: 여러 세션에서 공유 가능한 전역 임시 테이블이지만, 마지막 참조가 사라지면 사라집니다.
내부적으로는 모두 tempdb 데이터베이스에 생성됩니다.
그래서 디스크 I/O와 tempdb 스페이스에 영향을 줍니다.
-- 예시: 임시 테이블 생성 후 사용
DROP TABLE IF EXISTS #SalesSummary;
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales,
COUNT(*) AS OrderCount
INTO #SalesSummary
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;
-- 이후 여러 쿼리에서 #SalesSummary 재사용
SELECT *
FROM #SalesSummary
WHERE TotalSales > 100000;1.2 테이블 변수 (Table Variable)
@로 시작하는 변수입니다.DECLARE @table_variable TABLE ... 형태로 선언합니다.
-- 예시: 테이블 변수
DECLARE @HighValueCustomers TABLE (
CustomerID INT,
TotalSales DECIMAL(18, 2)
);- 생명주기는 현재 배치(Batch) 또는 스토어드 프로시저 내에서만 유지됩니다.
- 내부적으로는 tempdb에 저장되지만, 메모리가 우선이라는 인상이 있습니다.
- 트랜잭션·로그·잠금 오버헤드가 비교적 적어, 소량 데이터 + 간단한 로직에 적합합니다.
1.3 공통 테이블 식 (CTE)
WITH 구문으로 정의하는 임시 결과 집합입니다.
-- 단순 CTE 예시
WITH RecentSales AS (
SELECT
CustomerID,
SalesAmount,
OrderDate
FROM Sales
WHERE OrderDate >= '2026-01-01'
)
SELECT *
FROM RecentSales
WHERE SalesAmount > 50000;- CTE는 새로운 테이블을 물리적으로 생성하지 않습니다.
- 옵티마이저는 보통 CTE를 인라인 뷰처럼 처리해서, 쿼리 실행 시마다 다시 계산할 수 있습니다.
- 재귀 쿼리나 계층 구조(예: 조직도, 부모-자식 계층)를 표현할 때 매우 유용합니다.
2. 메모리 사용량과 tempdb 관점 비교
2.1 임시 테이블과 tempdb
- 임시 테이블은 tempdb에 실제 테이블 구조가 들어갑니다.
INSERT,UPDATE,DELETE모두 tempdb 로그와 데이터 페이지를 사용합니다.- 따라서 대량 데이터를
INSERT INTO #temp SELECT ...하거나 정렬/그룹핑/해시 조인 시 이런 작업들이 tempdb 공간과 I/O에 부담을 줄 수 있습니다.
실무 팁
tempdb는 여러 데이터 파일 분산, 초기 크기 설정, 자동 성장 비활성화 등을 미리 구성해 두는 것이 좋습니다.- 특히
#temp를 많이 쓰는 ETL/배치 쿼리에서는 tempdb 모니터링이 필수입니다.
2.2 테이블 변수와 메모리
- 테이블 변수는 tempdb에 저장되지만, 기본적으로는 작은 크기의 로우셋을 메모리 버퍼에 유지하는 구조입니다.
DECLARE @table TABLE (...)로 선언하면, 이후에 들어오는 데이터가 적을수록 메모리 캐시에 머물 가능성이 높습니다.
주의점
@table에 수백만 행을 INSERT하면, 여전히 tempdb를 사용하고, 메모리/정렬/해시 오버헤드가 커져 성능이 떨어질 수 있습니다.- 따라서 대량 데이터 처리에는 테이블 변수보다 임시 테이블을 권장합니다.
2.3 CTE와 물리 저장
- CTE는 기본적으로 물리 테이블이나 임시 저장소를 만들지 않습니다.
- CTE 결과는 쿼리 실행 시마다 다시 계산되는 경우가 많습니다.
- 다만, 옵티마이저가 중간 결과를 임시 테이블로 materialize하도록 결정할 수도 있습니다.
- 이 경우 내부적으로 tempdb 테이블이 생성되지만, 사용자가 직접 제어할 수는 없습니다.
3. 통계 정보의 유무와 실행 계획
3.1 임시 테이블과 통계
- 임시 테이블은 통계 정보(Statistics)가 생성됩니다.
SELECT INTO #temp또는INSERT INTO #temp후 실제 로우 수와 분포를 기반으로 실행 계획(Execution Plan)을 작성합니다.
-- 통계가 생성되는 패턴
SELECT *
INTO #LargeResult
FROM VeryLargeTable
WHERE SomeFilter = 1;
-- 이후 JOIN
SELECT *
FROM AnotherTable a
JOIN #LargeResult l ON a.ID = l.ID;- 이 경우
#LargeResult에 대한 row count 추정이 비교적 정확해Hash Join/Merge Join/Nested Loop중에서 옵티마이저가 적절한 조인 알고리즘을 선택할 가능성이 높습니다.
3.2 테이블 변수와 통계 부재
- 테이블 변수는 기본적으로 통계가 생성되지 않습니다.
- SQL Server 옵티마이저는
@table_variable을 단일 행(1 row) 또는 매우 적은 수로 가정하는 경향이 있습니다.
-- 테이블 변수 통계 문제 예시
DECLARE @AnalysisData TABLE (
CustomerID INT,
TotalSales DECIMAL(18, 2)
);
INSERT INTO @AnalysisData (CustomerID, TotalSales)
SELECT
CustomerID,
SUM(SalesAmount)
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;
-- 이후 JOIN
SELECT *
FROM @AnalysisData a
JOIN ProductCatalog p ON a.CustomerID = p.CustomerID;- 이 경우에
- 실제
@AnalysisData에 수만~수백만 행이 들어가더라도 - 옵티마이저는 1 row로 생각해서
Nested Loop같은 조인을 선택할 수 있습니다.
- 실제
- 대량 데이터라면 성능이 나빠질 수 있습니다.
3.3 CTE와 통계
- CTE 자체는 통계를 가지지 않습니다.
- CTE는 인라인 뷰처럼 처리되기 때문에 옵티마이저는 전체 쿼리의 통계를 기반으로 실행 계획을 만듭니다.
-- CTE 예시
WITH RecentOrders AS (
SELECT
OrderID,
CustomerID,
OrderDate
FROM Orders
WHERE OrderDate >= '2025-01-01'
)
SELECT *
FROM RecentOrders r
JOIN Customers c ON r.CustomerID = c.CustomerID;- 이 경우
RecentOrders는 실제 통계가 없고, 백그라운드에서 Orders 테이블 통계를 사용합니다.- CTE가 여러 번 참조되면, 매번 동일 쿼리를 다시 계산할 수 있어 CPU/메모리 비용이 커질 수 있습니다.
4. 인덱스 생성 가능 여부와 실무 패턴
4.1 임시 테이블과 인덱스
- 임시 테이블에는 클러스터형, 넌클러스터형 인덱스를 명시적으로 생성할 수 있습니다.
-- 임시 테이블에 인덱스 생성 예시
DROP TABLE IF EXISTS #CustomerSummary;
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales,
COUNT(*) AS OrderCount
INTO #CustomerSummary
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;
-- 인덱스 생성 후 사용
CREATE INDEX IX_CustomerSummary_CustomerID
ON #CustomerSummary (CustomerID);
CREATE INDEX IX_CustomerSummary_TotalSales
ON #CustomerSummary (TotalSales);
-- 인덱스 이용 쿼리
SELECT *
FROM #CustomerSummary
WHERE TotalSales > 100000;- 이처럼 대량 데이터를 중간 결과로 남기고, 이후 여러 쿼리에서 필터/조인을 한다면 임시 테이블 + 인덱스 조합이 매우 유리합니다.
4.2 테이블 변수와 인덱스 한계
- 테이블 변수는 선언 시에만 기본 인덱스 형태를 지정할 수 있습니다.
PRIMARY KEY또는UNIQUE제약을 줘서 클러스터형 인덱스 효과를 일부 얻을 수 있습니다.
-- 테이블 변수에 제약을 통한 인덱스
DECLARE @UniqueCustomers TABLE (
CustomerID INT PRIMARY KEY,
TotalSales DECIMAL(18, 2)
);
INSERT INTO @UniqueCustomers (CustomerID, TotalSales)
SELECT
CustomerID,
SUM(SalesAmount)
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;- 위처럼
CustomerID에PRIMARY KEY를 주면 내부적으로 클러스터형 인덱스가 생성됩니다. - 하지만
CREATE INDEX ...문을 나중에 추가로 실행할 수 없습니다.- 복잡한 조인/필터링 패턴에서는 유연성이 떨어집니다.
4.3 CTE와 인덱스
- CTE에는 직접 인덱스를 만들 수 없습니다.
- 만약 CTE 결과를 여러 번 참조하고 싶다면, CTE 결과를 임시 테이블로 저장한 뒤 인덱스를 만드는 패턴을 사용합니다.
-- CTE 결과를 임시 테이블로 materialize
WITH HighValueSales AS (
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID
HAVING SUM(SalesAmount) > 50000
)
SELECT *
INTO #HighValueSales
FROM HighValueSales;
CREATE INDEX IX_HighValueSales_CustomerID
ON #HighValueSales (CustomerID);- 이 패턴은 CTE의 가독성 + 임시 테이블의 인덱스/통계를 동시에 활용할 수 있는 실무 패턴입니다.
5. 언제 무엇을 쓸지: 실무 선택 기준
5.1 대용량 데이터 처리 (수천~수백만 행)
- 권장: 임시 테이블(#temp)
- 통계 생성 + 인덱스 생성 + 여러 단계 재사용 가능
- tempdb 성능, 파일 크기, I/O 모니터링이 필요
-- 대량 데이터 처리 패턴
DROP TABLE IF EXISTS #AggregatedData;
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales,
AVG(DiscountRate) AS AvgDiscount
INTO #AggregatedData
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;
CREATE INDEX IX_AggregatedData_CustomerID
ON #AggregatedData (CustomerID);
-- 이후 여러 분석 쿼리에서 재사용
SELECT *
FROM #AggregatedData
WHERE TotalSales > 100000;5.2 소량 데이터 + 단순 저장 (몇 행~수백 행)
- 권장: 테이블 변수(@table)
- 트랜잭션/로그 오버헤드 최소
- 코드가 간단, 가독성 좋음
-- 테이블 변수 사용 패턴
DECLARE @ErrorLog TABLE (
RowID INT,
ErrorMessage NVARCHAR(500)
);
INSERT INTO @ErrorLog (RowID, ErrorMessage)
SELECT
LineNo,
Message
FROM RawLog
WHERE Severity = 'ERROR';
SELECT *
FROM @ErrorLog;- 단, 대량 데이터일 경우 임시 테이블로 전환을 고려합니다.
5.3 복잡한 서브쿼리 분해, 재귀 쿼리
- 권장: CTE
- 재귀 쿼리(계층 구조 조회)에 매우 유리
- 가독성·유지보수성 향상
-- 재귀 CTE: 조직 계층 조회
WITH OrgHierarchy AS (
SELECT
EmployeeID,
ManagerID,
EmployeeName,
0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT
e.EmployeeID,
e.ManagerID,
e.EmployeeName,
oh.Level + 1
FROM Employees e
JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)
SELECT *
FROM OrgHierarchy
ORDER BY Level, EmployeeName;- 재귀 쿼리는 CTE가 없으면 매우 복잡한 서브쿼리와 임시 테이블 조합이 필요합니다.
5.4 중간 결과를 여러 번 재사용할 때
- 권장: 임시 테이블
- CTE나 테이블 변수는 각 참조마다 재계산/재평가가 일어날 수 있습니다.
- 임시 테이블은 한 번만 계산해서 여러 쿼리에서 재사용 가능합니다.
-- 여러 쿼리에서 재사용하는 패턴
DROP TABLE IF EXISTS #FinalAnalysis;
SELECT *
INTO #FinalAnalysis
FROM (
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales,
SUM(Profit) AS TotalProfit
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID
) src;
CREATE INDEX IX_FinalAnalysis_CustomerID
ON #FinalAnalysis (CustomerID);
-- Part 1
SELECT *
FROM #FinalAnalysis
WHERE TotalSales > 100000;
-- Part 2
SELECT *
FROM #FinalAnalysis
WHERE TotalProfit > 20000;- 이렇게 한 번만 materialize하면, 이후 여러
SELECT에서 동일한 결과를 사용할 수 있어, 전체 쿼리 성능이 향상될 수 있습니다.
5.5 실행 계획 안정성이 중요할 때
- 권장: 임시 테이블(통계 + 인덱스)
- 옵티마이저가 정확한 행 수와 분포를 보고, 안정적인 실행 계획을 선택할 수 있습니다.
- 테이블 변수는 행 수를 과소평가해서
Nested Loop같은 계획을 선택하게 되면 대량 데이터에서는 성능 저하가 발생할 수 있습니다.
6. 실무 쿼리 패턴 정리 (복사 붙여넣기용)
6.1 대량 데이터 + 인덱스: 임시 테이블
DROP TABLE IF EXISTS #SalesSummary;
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales,
COUNT(*) AS OrderCount
INTO #SalesSummary
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID;
CREATE INDEX IX_SalesSummary_CustomerID
ON #SalesSummary (CustomerID);
CREATE INDEX IX_SalesSummary_TotalSales
ON #SalesSummary (TotalSales);
SELECT *
FROM #SalesSummary
WHERE TotalSales > 100000;6.2 소량 데이터 + 단순 로직: 테이블 변수
DECLARE @CriticalOrders TABLE (
OrderID INT,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(18, 2)
);
INSERT INTO @CriticalOrders (OrderID, CustomerID, OrderDate, TotalAmount)
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM Orders
WHERE TotalAmount > 100000;
SELECT *
FROM @CriticalOrders;6.3 재귀 계층 조회: CTE
WITH OrgHierarchy AS (
SELECT
EmployeeID,
ManagerID,
EmployeeName,
0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT
e.EmployeeID,
e.ManagerID,
e.EmployeeName,
oh.Level + 1
FROM Employees e
JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)
SELECT *
FROM OrgHierarchy
ORDER BY Level, EmployeeName;6.4 CTE + 임시 테이블 조합 패턴
WITH HighValueSales AS (
SELECT
CustomerID,
SUM(SalesAmount) AS TotalSales
FROM Sales
WHERE OrderDate >= '2025-01-01'
GROUP BY CustomerID
HAVING SUM(SalesAmount) > 50000
)
SELECT *
INTO #HighValueSales
FROM HighValueSales;
CREATE INDEX IX_HighValueSales_CustomerID
ON #HighValueSales (CustomerID);
SELECT *
FROM #HighValueSales
WHERE TotalSales > 100000;7. 실무 체크리스트 (배포 전 점검)
- 중간 결과가 예상되는 행 수는 어느 정도인가?
- 같은 결과를 몇 번 다시 참조하는가?
- JOIN / WHERE / ORDER BY에서 인덱스가 필요하는가?
tempdb용량과 I/O 성능은 충분한가?- 현재 사용하는 실행 계획(Execution Plan)을 확인해서,
Nested LoopvsHash/Merge Join,Clustered Index ScanvsSeek- 등이 예상과 맞는지 점검했는가?
- 트랜잭션 범위와 잠금 오버헤드를 고려해서 임시 테이블 vs 테이블 변수 선택을 검토했는가?
반응형
✨
놓치면 아쉬운 추천 글, 함께 읽어보세요!
- 추천 글을 불러오는 중입니다...
