[MSSQL] OPENQUERY 대용량 DML 작업 시 문제점

 

 

OPENQUERY를 이용하여 POSTGRESQL에 DELETE 및 INSERT 작업을 진행하였다.

 

적게는 수천건부터 많게는 수백만건 작업을 해아하는데 어째서인지 만건만 넘어가도 서버가 뻗어버렸다.

 

그 원인은 바로 OPENQUERY의 메커니즘에 있었다.

예를 들어 DELETE FROM MEMBER 가 있다고 가정하면

그냥 POSTGRESQL에서 실행 시 MEMBER의 ROW 수 만큼 삭제되는데 실행계획을 보면 1 EXCUTE된다.

하지만 OPENQUERY 실행 시 ROW 수 = EXCUTE 수가 된다.

즉 하나를 삭제할 때 마다 모든 테이블을 읽어온다는 뜻이다.

 

INSERT도 마찬가지로 INSERT 작업 후 SELECT문을 실행시켜 모든 데이터를 한번 얻어온다고 한다.

직접 확인은 못했지만 아래 링크를 확인하면 될 것 같다.

 

http://zzizim.egloos.com/817935

OPENQUERY의 재발견

 

 

해결 방법은

1. 프로시저를 이용하여 나눠서 작업한다.

- 먼저 지울 데이터들의 COUNT를 얻어와 페이징 쿼리로 조금씩 나눠서 작업한다.

- 서버는 죽지 않지만 너무 느려 사용 불가능했다.

 

2. 리모트에서 작업한다.

- 결국 가장 깔끔하고 빠른 방법은 리모트에서 직접 작업하는 방법이였다.

  하지만 나는 오라클의 데이터를 MSSQL을 GATEWAY로 삼아 POSTGRE로 옮겨야 했기에 INSERT작업은 불가능 했다.

 

3. 리모트로 DELETE, CSV파일을 이용한 INSERT 작업

- 마지막으로 사용한 방법이다.

  MSSQL에서 얻어온 ORACLE 데이터를 TEMP 테이블에 복사 후 CSV 파일을 생성한다. (BCP CMDSHELL 이용)

  이후 POSTGRESQL 리모트에서 DELETE 및 COPY를 이용하여 CSV파일을 읽어와 IMPORT한다.

  하지만 CSV 파일 생성과 COPY과정에서 외부 서버를 사용하다 보니 여러 문제(권한)가 발생하였다.

  CSV 관련 해결 방법은 다음 포스팅에..