이전에 SQL Server 2005에서의 대량의 데이터를 로딩한 방법 별 수행 성능을 비교한 글을 올린 적이 있습니다. OPENROWSET, BCP, BULK INSERT, SSIS를 이용하여 데이터를 로딩하는 속도 비교를 측정한 글입니다.  (http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005TSQL&intSeq=351)

 

 

이번 글은 BULK INSERT 명령을 이용할 때 여러 경우에 대한 성능 비교 결과를 정리한 것입니다.

 

다음과 같은 형태의 10,000,000 건의 텍스트 파일을 Bulk Insert 명령을 이용하여 테이블로 로딩할 때 옵션에 따른 수행 시간 비교입니다. 각 경우에 대해 3회 반복 수행을 한 후, 평균 소요 시간을 이용하여 비교하였습니다.

 

 

[테스트 파일 - TestFile.txt]

Int형 순번 열, 100자리 문자 열 : 10,000,000 건

 

 

[저장 테이블 - TESTTABLE]

USE TEMPDB

GO

 

----------------------------------------

--TEST TABLE

-----------------------------------------

CREATE TABLE TESTTABLE

(

        SEQ INT,

        COL CHAR(100)

)

GO

   ※ DB File Size 증가로 인한 지연을 막기 위해 DB Size를 충분히 크게 늘린 후 수행함.

 

 

 

TEST 1) 인덱스가 없는 테이블에 로딩

--인덱스가 없는 테이블에 로딩

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n'

      )

GO

처리 시간

              1회 - 00:01:48

              2회 - 00:01:50

              3회 - 00:01:49

             평균 - 00:01:49 (109초)

 

 

 

TEST 2) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용

--인덱스가 없는 테이블에 로딩 + TABLOCK 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         TABLOCK

      )

GO

처리 시간

              1회 - 00:00:34

              2회 - 00:00:33

              3회 - 00:00:34

             평균 - 00:00:34 (34초)

 

 

 

TEST 3) 인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 적용 + 일괄 처리 크기 100,000으로 설정

--인덱스가 없는 테이블에 로딩 + TABLOCK 옵션 + RPB 100,000

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         TABLOCK,

         ROWS_PER_BATCH = 100000

      )

GO

처리 시간

              1회 - 00:00:33

              2회 - 00:00:33

              3회 - 00:00:34

             평균 - 00:00:33 (33초)

 

 

 

 

[Clustered Index 생성]

--Seq 컬럼에 Clustered Index 생성

CREATE CLUSTERED INDEX CIX_TESTTABLE ON TESTTABLE(SEQ)

GO

 

 

 

TEST 4) Clustered Index가 있는 테이블에 로딩

--Clustered Index가 있는 테이블에 로딩

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n'

      )

GO

처리 시간

              1회 - 00:03:40

              2회 - 00:03:39

              3회 - 00:03:39

             평균 - 00:03:39 (219초)

 

 

 

TEST 5) Clustered Index가 있는 테이블에 로딩 + 일괄 처리 크기 100,000

-- Clustered Index가 있는 테이블에 로딩

-- RPB 100,000

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         ROWS_PER_BATCH = 100000

      )

GO

처리 시간

              1회 - 00:02:49

              2회 - 00:02:47

              3회 - 00:02:48

             평균 - 00:02:48 (168초)

 

 

 

TEST 6) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션

-- Clustered Index가 있는 테이블에 로딩

-- ORDER 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         ORDER(SEQ ASC)

      )

GO

처리 시간

              1회 - 00:02:21

              2회 - 00:02:21

              3회 - 00:02:20

             평균 - 00:02:21 (141초)

 

 

 

TEST 7) Clustered Index가 있는 테이블에 로딩 + 일괄 처리 크기 100,000 + ORDER 옵션

-- Clustered Index가 있는 테이블에 로딩

-- RPB 100,000 + ORDER 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         ROWS_PER_BATCH = 100000,

         ORDER(SEQ ASC)

      )

GO

처리 시간

              1회 - 00:02:20

              2회 - 00:02:21

              3회 - 00:02:21

             평균 - 00:02:21 (141초)

 

 

 

TEST 8) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션 + TABLOCK 옵션

-- Clustered Index가 있는 테이블에 로딩

-- ORDER 옵션 + TABLOCK 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         ORDER(SEQ ASC),

         TABLOCK

      )

GO

처리 시간

              1회 - 00:00:37

              2회 - 00:00:37

              3회 - 00:00:37

             평균 - 00:00:37 (37초)

 

 

 

TEST 9) Clustered Index가 있는 테이블에 로딩 + ORDER 옵션 + TABLOCK 옵션 + 일괄 처리 크기 100,000

-- Clustered Index가 있는 테이블에 로딩

-- RPB 100,000 + ORDER 옵션 + TABLOCK 옵션

BULK INSERT TESTTABLE FROM 'D:\TestFile.txt'

WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         ROWS_PER_BATCH = 100000,

         ORDER(SEQ ASC),

         TABLOCK

      )

GO

처리 시간

              1회 - 00:00:36

              2회 - 00:00:37

              3회 - 00:00:37

             평균 - 00:00:37 (37초)

 

 

 

Clustered Index가 없는 힙(Heap) 테이블에 Bulk Insert 명령을 이용하여 로딩할 경우, TABLOCK의 옵션 설정에 따라 3배 정도의 속도 차이가 있습니다. 하지만, 일괄 처리 크기(Rows Per Batch) 설정은 성능에 별 영향을 미치지 않습니다. (34초 : 33초)

TABLOCK 옵션은 Bulk Insert 작업 수행 시 해당 테이블에 테이블 수준의 잠금을 설정함으로써, 하위 수준의 잠금 사용 시 발생하는 잠금 경합(Escalation)을 줄일 수 있을 뿐만 아니라 잠금을 설정하고 해제하는 단계를 줄여주기 때문에 처리 성능이 크게 향상될 수 있습니다. (옵션 적용 전 - 109초, 옵션 적용 - 34초) 하지만, 이 옵션을 적용하여 로딩할 경우에는 WITH (NOLOCK) 옵션 또는 WITH (READUNCOMMITTED) 옵션을 사용하더라도 로딩 중에 해당 테이블의 데이터를 읽을 수 없습니다. 따라서 로딩 중에 다른 부분에서 사용되는 테이블인 경우에는 이 옵션을 적용할 경우 블로킹을 발생시킬 수 있기 때문에 주의해야 합니다.

 

 

 

 

 

 

Clustered Index가 설정되어 있는 테이블인 경우에는 Heap 테이블과는 달리 몇 가지 옵션을 더 설정할 수 있습니다.

우선 옵션 없이 단순히 Bulk Insert를 수행할 경우, 입력된 데이터에 대해 인덱스 구성 작업을 수행해야 하기 때문에 힙 테이블의 로딩과 비교했을 때 훨씬 더 많은 시간이 소요됩니다. (Heap - 109초, Clustered Index - 219초)

일괄 처리 크기(Rows Per Batch)를 100,000건으로 설정하게 되면 전체 건(10,000,000건)의 인덱스를 한 번에 구성하는 대신 100,000건씩 나누어서 구성하기 때문에 처리 시간이 단축될 수 있습니다. (일괄 처리 - 219초, RPB 100,000건 168초)

만약 입력되는 데이터가 적재될 테이블의 Clustered 열과 동일한 순서로 정렬된 데이터인 경우에는ORDER(열 이름 [ASC | DESC]) 옵션을 이용하여 처리 시간을 단축 시킬 수 있습니다. 본 테스트에서는 텍스트 파일의 첫 번째 열로 정렬된 데이터이며, 이 데이터가 테이블의 SEQ열로 입력되기 때문에 ORDER 옵션을 적용할 경우, 처리 시간이 단축됩니다. (옵션 미 적용 - 219초, ORDER 옵션 적용 - 141초)

또한 Heap에서와 마찬가지로 TABLOCK 옵션을 설정하여 처리할 경우, 처리 시간이 크게 단축됩니다. 10,000,000건의 텍스트 파일에 대해 아무런 옵션 없이 Bulk Insert 작업을 수행한 경우에 비해 ORDER, TABLOCK 옵션을 이용하여 처리한 수행 시간이 6배 정도 빠르게 나타났습니다. (219초 : 37초)

 

 

참고로, 이러한 사항은 BULK INSERT 명령뿐만 아니라 SSIS(Integration Services) 패키지의 데이터 로딩 작업에서도 설정할 수 있는 사항입니다.

 

- OLE DB 대상에서 빠른 로드 설정 후, 고급 OLE DB 대상 편집기 중 FastLoadOptions 부분에서 설정

    

 

- SQL Server 대상의 고급 Server 대상 편집기 중, BulkInsertOrder 부분에서 설정

   

 

 

 

 

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 글의 링크를 밝혀주셔야 합니다. 

저작자표시

'SQL' 카테고리의 다른 글

Alias(별칭) "AS"  (0) 2011.05.02
Case When문  (0) 2011.05.02
저작자표시

'SQL' 카테고리의 다른 글

Bulk Insert 옵션에 따른 성능 비교  (0) 2011.05.03
Case When문  (0) 2011.05.02
Case When 문의 의미
Case When문이나 Decode함수를 사용하여 Select절의 결과물을 원하는 대로 변경하는 SQL은 여러 번 본 적이 있다.
하지만 주변 사람들과 이야기해보면 의외로 Select절 뿐만 아니라 Where절, Order By절, Group By절 등에 다 사용할 수 있다는 사실을 모르는 경우가 있었는데 오늘은 이런 사실에 대해 이야기해보고자 한다.

Case When문의 활용범위는 무궁무진하며, 이 것을 사용하지 않고 Static한 SQL을 사용한다는 것이 필자로서는 거의 불가능하게 느껴진다. 앞으로의 포스트에서도 줄기차게 사용할 것이므로 관심이 있으신 분들은 여러 활용방법을 제대로 알아두면 좋을 것 같다.


Case When문
간단하게는 Case When문은 Decode함수의 확장이다.
Decode함수는 Oracle에서 제공되는 SQL 내의 if/else의 조건문 역할을 하는 함수이나 = 연산만 가능하다. Case When문은 이를 확장한 것이며, 논리연산, 산술연산, 관계 연산을 다 지원하며 Oracle 뿐만 아니라 MS-SQL에서도 지원한다.
(예전에 듣기로는 My-SQL에서도 지원한다고 들은 적이 있다. Test해보지는 않았지만.)
복잡한 조건일 수록 Case When문은 Decode문에 비해 가독성이 높고, 수행속도도 빠르다고 할 수 있겠다.

사용하는 문법은 다음과 같다.
  • case when [조건1] then [결과1] when [조건2] then?[결과2] ...else [결과3] end
  • case [컬럼1] when [값1] then [결과1] when [값2] then [결과2] …  else [결과 3] end


Select 절에서의 활용
실제로 가장 많이 사용되는 활용은 Select절에서일 것이다. 실제 우리가 가지고 있는 Data로부터 우리가 원한 Data를 뽑아내는데 자주 사용된다.

Select (Case When name = ‘서울’ Then ‘수도권’  When name = ‘경기도’ Then ‘수도권’ Else ‘비수도권’ End) city_group, name From City

이 SQL은 당연히 다음과 같이도 사용가능하다.

Select (Case name When ‘서울’ Then ‘수도권’ When ‘경기도’ Then ‘수도권’ Else ‘비수도권’ End) city_group, name From City


지역이라는 Column이 없기 때문에 수도권인지 아닌지를 가져오기 위해 위와 같은 SQL을 사용하였다.
(왜 수도권에 '인천'은 없는지에 대해서 궁금해하는 사람은 없기 바란다. PT하다보면 가끔 그런 사람을 만날 수 있는데 정말 한숨만 나온다-_- 집중해야 할 곳에 집중하자.)


Group By 절에서의 활용
사례를 살펴보기 위해서 Cartesian Product(카테시안 곱) 항목에서 예로 들었던 SQL을 다시 가져와 보자.

Select (Case tbl2.no1 When 1 Then tbl1.품목 Else ‘합계’ End), Sum(수량)
From Table1 tbl1, (Select 1 no1 From dual Union All Select 2 From dual) tbl2
Group By (Case tbl2.no1 When 1 Then tbl1.품목 Else ‘합계’ End), tbl2.no1

복제된 Data에서 no1이 1인 것은 품목으로 Group by를 하였고, 2인 것은 "합계" 즉 상수로 Group by를 하였다. 따라서, 1인 경우는 품목별로 합계를 구하게 되고, 나머지 항목들은 전체 Group by가 되게 될 것이다.


Order By 절에서의 활용
회사 직원을 이름 순으로 뽑아오는 쿼리가 있다고 하자. 어느 날 직원 시스템을 한 번도 써보지 않았던 사장님이 시스템에 들어가보니 본인 이름이 중간에 있는 것을 발견하고 명색이 사장님인데 직원들 순서 중에서 내가 제일 앞에 나와야 하지 않는가 하는 의문을 제기하였다.

그런-_- 이유로 고쳐야할 시스템의 요구사항은 다음과 같다.
사장님의 이름만 맨 앞으로 뽑아내고 나머지는 가나다 순으로 정렬하면 되는 것이다. 물론 일단 Data를 Client로 다 가져온 다음 프로그램 상에서 처리할 수도 있겠으나, 나중에 이 사실을 알게 된 이사님도 이의를 제기한다면? 다른 관리자들은? 이런 요구사항들을 다 Client에서 처리한다면 프로그램은 갈수록 복잡해지게 될 것이다.

따라서, 그런 요구사항의 변경은 SQL에서 적용하는 것이 가장 간단할 것이며, 이 것이 우리가 추구하고자 하는 Static SQL의 본질이다. (Static SQL이란 원하는 집합을 하나의 SQL로 뽑아내는 것을 말하며 이 문제영역에서는 사장님이 맨 앞에 나오고 다른 직원들은 이름순으로 정렬된 집합이 우리가 원하는 집합이다. 따라서, 그것을 하나의 SQL로 얻어와야 한다.)

Order by절에서는 오름차순이건, 내림차순이건 사장님의 이름만 맨 앞으로 뽑아올 수 없으나, Case When문을 사용하면 간단하다.

Select 이름
From Emp
Order By (Case When 직함 = '사장' Then 1 Else 2 End), 이름


Where절에서의 활용
외부에서 들어온 조건(:v1)에 따라 서로 다른 동작을 정의하고자 할 때 사용할 수 있다.
Select 이름
From Emp
Where (Case When Length(:v1) = 0 Then 1 Else Instr(이름, :v1) End) > 0

검색할 이름에 대한 입력이 :v1에 들어오면 이름을 검색하여 결과를 보여주고 입력이 없으면 전체 직원의 이름을 Return하는 SQL이다. Where절을 동적으로 조합하지 않고 SQL 내부에서도 이러한 방법을 통해 서로 다른 결과값을 Return할 수 있다.


결론
SQL을 작성하는 여러가지 방법을 배우는 것은 문제에 따라 다양한 Idea를 내기 위한 필요조건이다. Client에서 혹은 PL/SQL에서의 if/else를 상당부분 SQL로 옮겨올 수 있는 Case When문을 익혀두는 것은 그 중에서도 첫 걸음이 될 수 있을 것이다

출처 : http://rtti.tistory.com/ 
저작자표시

'SQL' 카테고리의 다른 글

Bulk Insert 옵션에 따른 성능 비교  (0) 2011.05.03
Alias(별칭) "AS"  (0) 2011.05.02

+ Recent posts

티스토리툴바