스칼라 사용자 정의 함수를 더 빠르게 동작하도록 하는 방법

어제 아래와 같은 제목의 글을 뉴스레터를 통해 접했습니다.

좋은 글이라 소개합니다.

 

How to Make Scalar UDFs Run Faster

http://www.sqlservercentral.com/articles/T-SQL/91724/

 

위 링크를 통해 원문의 글을 읽어 보셔도 되고, 제 나름의 생각과 경험으로 각색한 아래 글을 읽어보셔도 되겠습니다. ^^

 

 

여러분은 User Defined Function을 실무에 사용하고 계신가요?

 

UDF가 가지는 가장 큰 이점은 "캡슐화"라고 생각합니다.

 

작성하는 SQL문에 반복적으로 나타나는 복잡한 구문을 하나의 기능으로 분리해 내는 것은.. 코드가 간결해지고 무결성 유지에도 도움이 되기 때문이죠.

 

하지만, 성능 문제가 늘 발목을 잡아 DBA 중 UDF의 사용을 기피하는 분이 많은 것 같습니다.

 

먼저, 사전 지식으로 RBAR - Row By Agonizing Row - 에 대해 짚고 넘어 갑시다.

 

행 단위 처리로 인해 발생하는 심각한 성능 문제를 의미하는데, Cursor나 Loop의 사용이 주요 원인이 됩니다.

 

아무래도 숙련된 DBA는 Set Base (집합 기반) 사고를 우선시하기 때문에 절차적 사고를 하는 개발자나 초급 DBA에 비해 RBAR를 경험하는 일이 상대적으로 적겠죠?

 

그런데 UDF 중에서 Scalar Function을 사용하면 Set Base의 시퀄문에서도 RBAR를 경험하게 됩니다.

 

아래는 예전 포스팅에서도 소개한 적이 있는.. int로 저장한 IP주소를 string으로 반환하는 Scalar Function 입니다.

 

CREATE FUNCTION dbo.FN_IPNumber2IP (@intIPNumber int)

RETURNS varchar(15)

AS

BEGIN

  RETURN

    CAST(((@intIPNumber & 0xFF000000) / 16777216) + 128 AS varchar(3)) + '.' +

    CAST((@intIPNumber & 0x00FF0000) / 65536 AS varchar(3)) + '.' +

    CAST((@intIPNumber & 0x0000FF00) / 256 AS varchar(3)) + '.' +

    CAST((@intIPNumber & 0x000000FF) AS varchar(3));

END

 

성능 테스트를 위해 아래와 같이 1 ~ 1,000,000 까지의 정수 컬럼 n을 가진 테이블을 만들어 봅니다.

 

SELECT TOP (1000000) IDENTITY(int, 1, 1) AS n

INTO #Tally

FROM master.dbo.spt_values A

  CROSS JOIN master.dbo.spt_values B;

 

정수 n을 문자열 형태의 IP 주소로 변환하는 SELECT 문의 성능은 과연 어떨까요?

 

- Scalar Function을 사용하지 않았을 때, SET STATISTICS TIME으로 측정한 성능

 

SET STATISTICS TIME ON;

 

DECLARE @IP varchar(15);

 

SELECT @IP =

             CAST(((n & 0xFF000000) / 16777216) + 128 AS varchar(3)) + '.' +

             CAST((n & 0x00FF0000) / 65536 AS varchar(3)) + '.' +

             CAST((n & 0x0000FF00) / 256 AS varchar(3)) + '.' +

             CAST((n & 0x000000FF) AS varchar(3))

FROM #Tally;

 

SET STATISTICS TIME OFF;

 

제 PC에서의 결과는 아래와 같습니다.

 

SQL Server Execution Times:

   CPU time = 484 ms,  elapsed time = 485 ms.

 

- Scalar Function을 사용했을 때, SET STATISTICS TIME으로 측정한 성능

 

SET STATISTICS TIME ON;

 

DECLARE @IP varchar(15);

 

SELECT @IP = dbo.FN_IPNumber2IP(n) FROM #Tally;

 

SET STATISTICS TIME OFF;

GO

 

SQL Server Execution Times:

   CPU time = 2309 ms,  elapsed time = 2358 ms.

 

결과는 함수 사용하는 쪽이 4.8배 느리게 나왔습니다.

 

4.8배라는 수치가 나온 이유에는 2가지 원인이 있습니다.

 

첫째는, 이 구문이 RBAR 이기 때문입니다.

분명 Set Base 구문이지만 dbo.FN_IPNumber2IP(n) 의 실행은 행 단위로 발생합니다.

해당 함수가 1백만번 실행되는 과정에서 RBAR를 경험하게 된 거죠. (프로파일링을 해보셔도 좋겠습니다.)

 

만약, GETDATE()를 사용했다면? 이 경우는 RBAR가 아닙니다.

GETDATE()는 딱 한번 실행되고.. 그 결과가 1백만번 동일하게 반환되기 때문입니다.

 

정리하면..

- 모든 사용자 정의 스칼라 함수는 행 단위로 실행됩니다.

- 시스템 스칼라 함수는 행 단위로 실행되는 것도 있고, 집합 단위로 실행되는 것도 있습니다.

예를 들면.. GETDATE() vs. NEWID()

 

둘째는, SET STATISTICS TIME 옵션으로 측정했기 때문입니다.

Scalar UDF가 느리다는 것은 변함 없는 사실입니다.

그런데 4.8배나 느린게 사실일까요?

 

SET STATISTICS TIME을 사용하지 않고 시간을 재보겠습니다.

 

- Scalar Function을 사용했을 때, DATEDIFF()로 측정한 성능

 

DECLARE @IP varchar(15);

DECLARE @S datetime;

 

SET @S = GETDATE();

 

SELECT @IP = dbo.FN_IPNumber2IP(n) FROM #Tally;

 

PRINT DATEDIFF(ms, @S, GETDATE());

GO

 

제 결과는 1,820ms

 

이 함수의 경우에는 4.8배가 아닌 3.8배 정도 느린게 맞습니다.

 

여기서 포인트는..

 

Scarlar Function의 성능은 SET STATISTICS TIME 옵션으로 측정해선 안된다.

 

라는 점인데요.. SET STATISTICS TIME 자체가 가진 오버헤드가 Scalar Function에서 훨씬 크기 때문에 정당한 비교 수단이 될 수 없기 때문입니다.

 

 

자.. 이제부터가 본론입니다.

 

이렇게 느린 Scalar Function (SF)을 Inline Table Valued Function (iTVF)으로 변환하여 실행하면 RBAR를 극복할 수 있습니다.

 

원래의 함수를 아래와 같이 수정합니다.

 

CREATE FUNCTION dbo.IF_IPNumber2IP (@intIPNumber int)

RETURNS table

AS

RETURN

  SELECT

    CAST(((@intIPNumber & 0xFF000000) / 16777216) + 128 AS varchar(3)) + '.' +

    CAST((@intIPNumber & 0x00FF0000) / 65536 AS varchar(3)) + '.' +

    CAST((@intIPNumber & 0x0000FF00) / 256 AS varchar(3)) + '.' +

    CAST((@intIPNumber & 0x000000FF) AS varchar(3)) AS IP;

 

Begin ~ End가 없는 - 다른 말로는 Body가 없는 - 함수.. 즉, 인라인 함수의 형태로 변환합니다.

 

iTVF는 SF와 호출 방식이 다르기 때문에 SELECT문도 아래와 같이 수정되어야겠군요.

 

SET STATISTICS TIME ON;

 

DECLARE @IP varchar(15);

 

SELECT @IP = F.IP

FROM #Tally T

       CROSS APPLY dbo.IF_IPNumber2IP(T.n) F;

 

SET STATISTICS TIME OFF;

GO

 

결과는?

 

SQL Server Execution Times:

   CPU time = 483 ms,  elapsed time = 487 ms.

 

함수 사용으로 인한 성능 저하는 거의 발생하지 않는 것으로 나타나는군요~

 

 

마치는 말 

 

사용하고 계신 SF가 있다면, 위와 같이 변환하여 테스트해 보시기 바랍니다.

 

막연하게 UDF 사용을 꺼리고 계신 분이시라면, 직접 테스트해보시기 바랍니다.

UDF의 캡슐화는 분명 강점이니까요. ^^