전산이야기

원격지 서버의 테이블에 대한 갱신 작업을 위해서 다음과 같이 Linked Server의 테이블을 JOIN 하여 UPDATE 하였습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'

BEGIN TRAN

UPDATE Trgt
SET
    AcptYn = 'N',
    AcptEmpCode = '', 
    AcptDeptCode = '',
    AcptDateTime = NULL
FROM
    RemoteServer.RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
        JOIN RemoteServer.RemoteDB.dbo.FIAccSlipAggreationNo AS A
            ON a.SlipNo = Trgt.SlipNo
            AND A.AggregationNo = @ano

SET XACT_ABORT OFF;

COMMIT

그러나 동작은 잘 되지만 성능이 너무 나오지 않았습니다.

실행계획을 보니 원격지 테이블을 모두 읽어와서 JOIN 시키다 보니 실제 UPDATE 시킬 행은 1개 인데 수십만 건의 데이터를 읽고 있었습니다.

그래서 OpenQuery로 변경 하려고 했으나, 문제는 OpenQuery의 SQL문이 순수한 문자열만 가능 하도록 되어 있어서 파라미터(변수)를 사용할 수가 없었습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'

BEGIN TRAN

UPDATE x
SET
    AcptYn = ''N'',
    AcptEmpCode = '''', 
    AcptDeptCode = '''',
    AcptDateTime = NULL
FROM
    OpenQuery (RemoteServer,
        '
        SELECT
            Trgt.*
        FROM RemoteDB.dbo.FIAccSlipAggreationNo AS A
            JOIN RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
                ON a.SlipNo = Trgt.SlipNo
        WHERE
            A.AggregationNo = ''' + @ano + '''
        '
    ) x

SET XACT_ABORT OFF;

COMMIT

그래서 최종적으로 OpenQuery를 포함한 전체 쿼리 문장을 문자열 변수에 넣고 EXEC()로 처리 하였습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'
DECLARE @sql varchar(max)

BEGIN TRAN

set @sql = 
    '
    UPDATE x
    SET
        AcptYn = ''N'',
        AcptEmpCode = '''', 
        AcptDeptCode = '''',
        AcptDateTime = NULL
    FROM
        OpenQuery (RemoteServer,
            ''
            SELECT
                Trgt.*
            FROM RemoteDB.dbo.FIAccSlipAggreationNo AS A
                JOIN RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
                    ON a.SlipNo = Trgt.SlipNo
            WHERE
                A.AggregationNo = ''''' + @ano + '''''
            ''
        ) x
    '
EXEC(@sql)

SET XACT_ABORT OFF;

COMMIT

성능도 좋고, 파라미터(변수)도 처리할 수 있어서 만족할 만한 결과가 나왔습니다.

 

원격지 서버에 대한 갱신작업이 필요하실 때 참고 하시기 바랍니다.

Comment +0

아시는 바와 같이 임시 테이블은 #(로컬) 및 ##(전역)이 prefix로 붙은 테이블 입니다.
그리고 테이블변수는 TABLE 타입으로 DECLARE된 로컬변수 입니다.
 
둘 간에 여러가지 차이점이 있지만 여기서는 성능적인 이슈만 언급 하겠습니다.
테이블변수의 문제는 SQL서버가 통계정보를 생성하지 않기 때문에 적절한 실행계획 수립이 안될 수 있다는 점 입니다.
 
아래는 테이블변수를 사용한 특정 SP의 실행계획 입니다 (처리속도는 14초).

NL JOIN 으로 처리되어 과도한 Execute, Rows가 나왔습니다. SQL 서버가 NL을 선택한 것은 예상 행수가 모두 1 이기 때문 입니다.
예상 행수가 1:1 이기 때문에 NL로 처리해도 아무 문제가 없다고 판단이 되었습니다.
실제 테이블변수의 행은 1067 입니다.
 
이제 다른것은 그대로 두고 테이블변수를 임시테이블로만 변경한 후, 다시 실행계획을 확인 해봅니다 (처리속도는 6초).

이번에는 HASH JOIN 으로 처리 되었습니다. 이유는 예상행수가 1067, 617 이기 때문 입니다.
1067건과 617건을 NL JOIN 으로 처리하면 부하가 크다고 판단하고 HASH로 JOIN 전략을 변경한 겁니다.
 
결론적으로 일반적으로 행수가 적은 경우는 테이블변수를 사용해도 무관하지만, 크기가 큰 경우는 임시테이블을 사용해야 성능이슈가 발생하지 않게 됩니다.
 
 

 

Comment +0