SQL 대용량 데이터 입력 속도 개선

몇 년 전 구축한 정보 시스템이 정보량이 늘어나자 서버가 멈추는 현상이 발생한다.

 

아무래도 당시 개발팀이 상정했던 것보다 데이터의 처리량이 폭주한 때문이지 싶었다.

 

이 참에 SQL 서버의 대용량 데이터 입력 시 속도 저하 및 LOC 문제를 해결하려 정보를 뒤져보았다.

 

 

일단 찾은 정보는 일단 최소 로깅과 테이블 힌트였다.

 

반응형

최소 로깅이란?

 

DB 로그는 시스템 장애나 데이터 손실 시 이를 복구할 수 있도록 트랜젝션의 변경사항을 기록하는 것을 말한다.

 

데이터베이스에서 로깅과 복구는 무결성과 안정성의 확보를 위해 필수사항이라 할 수 있다.

 

 

즉 최소 로깅이란, 대량 데이터의 INSERT 시 이 로그가 입력되는 시간을 최소화하기 위해 최소한의 정보만 등록하도록 하는 것으로 이해된다.

 

 

힌트 사용

 

1) 테이블락 힌트 사용 (TABLOCK)

TABLOCK 힌트는 대용량 데이터 INSERT 시 유용한 기능으로 알려져있다. 

이 힌트를 사용하면 대상 테이블에 즉시 테이블 락이 걸리며, 여러 세션이 행 또는 페이지 락을 걸지 못하게 한다.

따라서 항상 유용한 힌트라기 보다는 특정 프로그램을 통한 단일 세션의 대량 데이터 입력 시 유용한 힌트라고 할 수 있다. 

 

2) NOLOCK 힌트 사용

NOLOCK 힌트는 조회 때 쓰는 테이블 힌트다.

NOLOCK 힌트를 사용하면 해당 테이블의 잠금을 무시하고 데이터를 반환한다. 

다시 말해, 다른 트랜젝션에서 해당 데이터를 수정하는 중이라고 해도 기다리지 않고 데이터를 조회한다.

그래서 이 힌트는 무결한 결과를 항상 보장하지는 않는다. 

다만, 단일 세션이 조회/ 입력/ 수정을 하는 경우라면 불필요한 락 충돌을 피하고 속도 개선을 도모할 수 있다.

728x90

 

최소 로깅 처리 예시

 

INSERT INTO 대상테이블 WITH(TABLOCK)

SELECT ... FROM 정보테이블 WITH(NOLOCK)

 

 

약 백만건 처리 테스트에서 단순한 INSERT INTO .. SELECT ... 구문에 비해 약 10배 정도 속도 개선이 있었다.

다만, 위에서 언급했듯이 단일 세션이 아닌 경우 다른 세션의 트랜젝션을 방해할 수도 있어 더 테스트는 해봐야 할 것 같다.