ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • OpenXML 을 이용한 MS-SQL 대용량 데이터 삽입
    Development/Database 2011. 5. 5. 21:00
    데이터베이스 서버나 개발시 로컬 인스턴스 환경에서 유용하게 사용될 수 있는 것이 벌크 인서트(Bulk Insert) 기능이다. 샘플 데이터 라던가 코드성 데이터들을 한번에 쉽게 테이블에 넣을 수 있는 방법으로 많이 애용되고 있다. 그런데 벌크 인서트는 로컬의 파일 시스템에 저장된 텍스트 파일을 이용하는 방식이기 때문에 리모트에서 동작하는 ASP.NET 어플리케이션과 같은 클라이언트는 사용할 수 없는 기술이다.

    그렇다면 대용량의 데이터를 한번에 넣을 수 있는 좋은 방법은 없을까? 수백건의 데이터를 한번에 테이블에 넣는 것과 같은 작업을 insert 문을 이용해 행의 갯수만큼 돌린다면 트랜잭션의 관리를 포함하여 도저히 감당하기 힘든 느린 속도를 경험하게 될 것이다. 이럴 때 유용하게 사용할 수 있는 것이 바로 OpenXML 을 이용한 대용량 데이터의 삽입이 아닐까 싶다.

    원리는 간단하다. 한번에 넣어야 하는 데이터를 XML 형태로 구성해서 데이터베이스 서버에 전달하고 이를 서버측 스토어드 프로시저가 OpenXML 을 이용하여 DTD 에 맞추어 데이터를 추출하고 테이블에 넣는 방법이다. 서버측 자원을 사용하게 되는 단점이 있지만 insert 문을 행의 갯수만큼 반복하는 것보다는 훨씬 효과적인 방법이라고 생각된다.
    functio foo()
    {
    
    }
    


    SET TEXTSIZE 100000


    DECLARE @docHandle int

    DECLARE @data varchar(max)


    SET @data = '

    <Logs Hostname="MyServer">

       <Log C="/Central Process Unit/Process Time Limit is never comes along with me yeah1" I="/Item Name Can Be many Things1" V="0.306316345601545" D="2011-05-04T01:01:01" />

    </Logs>

    '


    EXEC sp_xml_preparedocument @docHandle OUTPUT, @data


    SELECT *

      FROM OPENXML(@docHandle, '/Logs/Log', 2)   -- 0,1 : Attibute-Centric , 2 : Element-Centric

      WITH (Hostname varchar(50) '../@Hostname',

            CounterCategory varchar(100) '@C',

            CounterItem varchar(100) '@I',

            VounterValue decimal(13,1) '@V',

            CheckDate datetime '@D')


    대용량 데이터의 저장을 위해서 XML 을 구성하면 필연적으로 그 용량이 커지게 된다. MS-SQL 의 varchar 타입은 최대 8000 바이트까지 밖에 허용하지 않고 DECLARE 로 선언 가능한 내부 변수에 text 타입은 빠져있다. 따라서 SET TEXTSIZE 구문을 통해서 max 값을 충분히 늘려준뒤 varchar(max)를 사용하면 해결할 수 있다.

    XML 로 구성하는 데이터의 형식은 자유이다. DTD 를 별도로 만들지 않아도 되고 형식만 잘 맞추어주면 된다. OpenXML 은 노드를 구조를 지정하고 애트리뷰트 단위(Attribute) 혹은 엘레멘트 단위(Element)로 지정할 수 있다. 디렉토리 이동시와 마찬가지로 ../ 와 같은 구문을 이용하면 XML 의 모든 요소에 엑세스가 가능하다.



    - NoPD - 

    댓글 0

NoPD