병렬로 쿼리 실행하기

병렬로 쿼리 실행하기

 

 

Microsoft Premier Field Engineer

한대성 

 

Question

같은 프로시져를 동시에 여러 프로세스로 실행하고 싶습니다.  아이디어가 안 떠 오르는데, SSIS 로 할 수 있는 방법 없을까요? 방법1 ) declare @i int set @i = 1 while ( @i < 100 )  begin         EXEC  프로시져명  @i         set @i = @i + 1 end 방법1)  처럼 실행하면 프로시져를 while 로 돌리면 1개 처리되고 다음것이 순차적으로 처리가 되는데, 순차적인 방법이 아닌 병렬로    exec  프로시져명 1  , exec  프로시져명 2 ,  exec 프로시져명 3   ....   exec  프로시져명 100  를  한번에 실행하고 싶은데 방법이 없을까요 ?

같은 프로시저를 동시에 실행시킨다는 것은 간단해 보이지만 꽤 까다로운 주제입니다.

SQL 쿼리 실행 자체가 동기성 작업(Synchronous)이라, 쿼리 분석기이든 다른 어플리케이션이든 쿼리를 실행시키고 결과가 나올 때 까지 기달려야 하는 형태입니다. 이에 반해 SQL Agent의 작업과 같은 형태는 비동기성 작업(Asynchronous)이라 할 수 있습니다. 시작만 시켜놓고 종료 여부에 상관없이 다른 작업들을 할 수 있는 형태입니다.

 

SSIS를 이용해서 동시에 여러 작업을 수행하도록 하는 것을 어떻게든 구현해 봅시다.

어떻게 패키지를 구성할 것이냐. 10개 동시에 수행하는 것으로 단순화 해서 설명드리겠습니다.
 

10개의 SQL 실행 작업을 만들어놓고선, 각 실행 작업들이 수행할 변수를 지정합니다. 변수도 물론 10개가 되겠지요. 파일이든 테이블이든간에 어디선가 쿼리 목록을 읽어온 다음(1), 차례대로 이 변수들에 대입시켜주고선 SQL 실행 작업들을 실행시키는 것이지요.(2)

 

1.     쿼리 목록을 읽어와서 변수에 대입하기.

본 예에서는 다음과 같은 형태의 쿼리가 저장된 파일에서 쿼리를 읽어오도록 구성하겠습니다.

 

SSIS에서 QueryObj이라는 이름의 Object형 변수를 하나 추가한 다음, 아래와 같이 데이터 흐름 작업에서 RecordSet 대상을 이용하여 이 변수에 결과값을 저장하는 것을 구성합니다. RecordSet 대상 편집기에서 저장할 변수를 지정한 후, 입력 열 탭에서 저장되는 열을 선택하셔야 합니다.

 

 

 

이제 SSIS 변수에 Query1, Query2, .. Query10 이라는 10개의 String 형 변수를 추가합니다.

 

데이터 흐름 작업 뒤에 스크립트 작업을 연결한 후, 아래와 같이 변수들을 지정합니다.

 

스크립트 편집기를 연 다음 상단의 프로젝트(P) à 참조 추가(R)을 선택한 후, System.Xml.dll 을 참조로 추가합니다.

 

다음과 같은 스크립트를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO     Public Class ScriptMain                Public Sub Main()                      Dim oLead As New Data.OleDb.OleDbDataAdapter         Dim dt As New Data.DataTable         Dim dr As System.Data.DataRow         Dim seq As Integer = 0             oLead.Fill(dt, Dts.Variables("QueryObj").Value)           For Each dr In dt.Rows               seq += 1             Dts.Variables("Query" & seq.ToString).Value = dr.Item(0).ToString         Next         Dts.TaskResult = Dts.Results.Success       End Sub End Class

 

 

2.     SQL 실행 작업 구성하기

이제 시퀀스 컨테이너를 스크립트 작업 뒤에 연결한 후, 시퀀스 컨테이너 안에 10개의 SQL 실행 작업을 추가합니다.

 

각각의 SQL 실행작업을 열어 실행할 DB 연결을 지정하고, 각 작업들의 속성 중 Expressions을 선택하여 아래와 같이 SQLStatementSource 속성에 대해 Query1 부터 Query10까지 지정합니다.

 

각 작업의 속성 중 DelayValidation 속성도 True로 바꿔줍니다. (필수 사항은 아닙니다.)

그런 다음, 패키지의 빈 곳을 선택한 후, 속성의 MaxConcurrentExecutables 값을 10으로 변경해 줍니다. 동시에 수행할 수 있는 작업의 수를 10개로 하겠다라는 것입니다.

 

완성된 모습은 다음과 같습니다.

 

아래와 같은 테스트 프로시저를 이용하여 결과를 확인해 봅시다.

USE TEMPDB GO   CREATE TABLE ResultValues (              seq int,              insdate datetime default(getdate())              ) GO   CREATE PROC TestProc(@seq int) as              INSERT ResultValues(seq) values(@seq)        WAITFOR DELAY '00:00:01' GO

 

 

10개의 SQL 실행 작업을 동시에 수행하라고는 하였지만, 위의 결과와 같이 약간의 차이가 존재하긴 합니다.

참고삼아 다음과 같이 SQL 쿼리를 이용하여 수행하였을 때의 결과를 비교해 봅시다.

 

만약, 반드시 동일한 시간에 동시에 수행해야 한다면?

SSIS 패키지에서 이를 제어하지 말고, 저장 프로시저를 약간 고쳐서 이를 해결해 봅시다.

 

ALTER PROC TestProc(@seq int, @WAITTIME DATETIME = NULL) as   IF @WAITTIME IS NOT NULL        WAITFOR TIME @WAITTIME                      INSERT ResultValues(seq) values(@seq)        WAITFOR DELAY '00:00:01' GO

 

그리고선, 실행 쿼리문도 다음과 같이 변경합니다.

EXEC dbo.TestProc 1,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 2,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 3,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 4,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 5,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 6,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 7,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 8,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 9,'2009-08-07 23:17:00.000' EXEC dbo.TestProc 10,'2009-08-07 23:17:00.000'