UDF(사용자 정의 함수)로 인한 성능저하 ①

게임회사에서 근무하다가 교육업체로 업종을 변경하면서, 쿼리에 대해서 많은 차이점이 보입니다.

물론 이곳(?)의 스타일 일수도 있지만, UDF를 상당히 많이 사용합니다. 

게임회사에서는 UDF를 그렇게 많이는 사용하지 않았는데, 이곳에서는 아주 많은 쿼리에 UDF를 사용하는 경우를 보았습니다. 

UDF가 무조건 안좋은 것은 아니지만 모르고 쓸 경우 성능에 치명적인 영향을 미칠 수 있다는 것을 테스트 하였습니다.

이 테스트는 샘플 데이터베이스인 AdventureWorks2012 에서 테스트 하였으며, SQL MVP 61인 Chapter 20의 사용자 정의 함수(UDF)를 참조하였습니다.

책의 내용이 2가지가 언급되어 있어, 2개로 나누어서 글을 정리 합니다. 

이 테스트 쿼리는 책에서 제공하는 쿼리와 약간 다릅니다. (책은 2005 버전이므로, 몇몇 테이블이 존재 하지않아, 제가 임의로 수정하였습니다.)

Chapter 1. 차이 비교

인덱스를 탈 수 있는지 확인하기 위해서 인덱스를 한개 만들어 줍니다.

CREATE NONCLUSTERED INDEX idx1 ON Person.Person 
(LastName, FirstName,MiddleName)
GO

그 다음으로 사용 할 UDF를 만듭니다. 이 UDF는 단순하게 LastName , FirstName , MiddleName을 붙여서 Full Name으로 만들어 주는 것 입니다.

CREATE FUNCTION dbo.fn_FullName (@FirstName nvarchar(50), @MiddleName nvarchar(50), @LastName nvarchar(50))
RETURNS nvarchar(153) WITH SCHEMABINDING
AS
BEGIN
   RETURN @LastName + N', ' + @FirstName + N' ' + ISNULL(@MiddleName, N'')
END
GO

우선 함수를 사용하여, 아래의 쿼리를 SELECT 하겠습니다. fn_FullName 이라는 UDF는 총 2번 사용되었습니다. 

첫번째로 SELECT 구문에서 결과를 표시 하기 위해서 나타났으며, 두번째로 WHERE 조건에서 검색을 위해서 사용되었습니다.

SELECT dbo.fn_FullName(c.FirstName, c.MiddleName, c.LastName) as FullName,
       od.ProductID,
       SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice
  FROM Sales.SalesOrderHeader oh
 INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID
 INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID
 INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = sp.BusinessEntityID
 INNER JOIN  Person.Person  c ON c.BusinessEntityID = e.BusinessEntityID
 WHERE dbo.fn_FullName(c.FirstName, c.MiddleName, c.LastName) = N'Abbas, Syed E'
 GROUP BY c.LastName, c.FirstName, c.MiddleName, od.ProductID

좀 더 자세히 확인하게 위해서 몇가지 성능등을 보면 다음과 같습니다. 

Read가 상당히 높은 것을 확인 할 수 있습니다.

실행계획을 한번 확인해 보면 다음과 같습니다.

현재 쿼리에 JOIN 구문의 ON절 및 WHERE 조건에 모두 인덱스가 존재하지만, Scan을 타는 실행계획이 존재하며, "실제 행 수"와 "예상 행 수"가 매우 차이납니다.

이것은 쿼리 실행 계획이 매우 비효율적임을 나타냅니다. 통계정보를 옵티마이저가 정상적으로 사용하지 못하고 있음을 의미합니다. 물론 인덱스도 정상적으로 

활용하고 있지 못하다고 볼 수 있습니다. 

현재 이 쿼리가 단순(?)하고 Row가 많지 않아서 이렇게 나름데로 빠른 시간에 끝나지만 만약 Row가 매우 많고 복잡하다면 더욱 긴 Duration을 유발하고 Read,CPU등

많은 리소스를 필요로 할 것 입니다.

그에 따라 다음과 같이 쿼리를 수정하여, 사용 할 수 있습니다.

SELECT c.LastName + N', ' + c.FirstName + N' ' + ISNULL(c.MiddleName, '')
       as FullName, od.ProductID
	   ,SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice
  FROM Sales.SalesOrderHeader oh
 INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID
 INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID
 INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = sp.BusinessEntityID
 INNER JOIN  Person.Person  c ON c.BusinessEntityID = e.BusinessEntityID
 WHERE c.LastName = N'Abbas'
   AND c.FirstName = N'Syed'
   AND c.MiddleName = N'E'
 GROUP BY c.LastName, c.FirstName, c.MiddleName, od.ProductID

위의 쿼리는 SELECT,WHERE에서 UDF를 제거 하고, 그대로 WHERE 조건을 쓰고, SELECT 문에서 직접 이어 붙였습니다.

그로 인하여, 다음과 같은 성능을 볼 수 있습니다.

이로 인하여, 실행계획도 많이 차이가 납니다.

예상 행수가 실제 행수보다 아직 많기는 하지만, 많이 줄어든 모습을 볼 수 있습니다. 또한 가장 크게 변경된 것이 인덱스를 활용하여, Read 수가 현저하게 줄었습니다.

Chapter 2. 왜? 차이가 날까?

이 쿼리가 많은 차이가 나는 것은 UDF를 통해서 계산을 하면서 조인을 하느냐의 차이와 모두 조인한 결과를 UDF로 변환하냐의 차이입니다.

아래에 그림에서 보는 바와 같이 Where 조건에서 1건씩 변환 한 뒤에 조인을 하므로, 인덱스를 탈 수 없으며, 1건당 테이블에서 찾아야 하므로 Read가 높습니다.

그렇다면, 2번째 쿼리에서는 어떻게 될까요?

아래의 그림과 같이 Join의 결과에 대해서 UDF를 적용하여, 나타난 결과를 보여주므로, 여러번 검색이 필요 없습니다.

이것을 실제로 보기 위해서 프로파일러를 이용하여, 보면 좀 더 확실 합니다.

첫번째 쿼리에 대해서 프로파일러에 잡히는 내역을 보면 다음과 같습니다. 프로파일로 잡으면, UDF 를 적용하느라 RowCount가 1건씩 잡히는 쿼리가 

반복적으로 들어옵니다. 수행되는 쿼리는 똑같으며, 최종적으로 찍히는 곳에 건수와 Read 가 나옵니다. 결국 위에서 이야기 한 것과 같이 반복적으로

수행되고 있음을 볼 수 있습니다.

두번째 쿼리를 프로파일러로 잡으면 다음과 같습니다. 딱 1줄 실행된 것이 보입니다. 

두 쿼리의 차이가 분명합니다.

Chapter 3. 또 다른 궁금증

인터넷 등에서 UDF로 인한 성능 저하를 없애기 위해서 WHERE 조건에서 제거하고 SELECT 조건에 추가하기를 권고합니다.

그래서, 다음과 같이 쿼리를 만들고, 테스트를 진행 하였습니다.

SELECT dbo.fn_FullName(c.FirstName, c.MiddleName, c.LastName) as FullName,
       od.ProductID,
       SUM((UnitPrice - UnitPriceDiscount) * OrderQty) AS TotalPrice
  FROM Sales.SalesOrderHeader oh
 INNER JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = oh.SalesOrderID
 INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = oh.SalesPersonID
 INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = sp.BusinessEntityID
 INNER JOIN  Person.Person  c ON c.BusinessEntityID = e.BusinessEntityID
-- WHERE dbo.fn_FullName(c.FirstName, c.MiddleName, c.LastName) = N'Abbas, Syed E'  -- WHERE에서 UDF 제거
WHERE c.LastName = N'Abbas'
AND c.FirstName = N'Syed'
AND c.MiddleName = N'E'
 GROUP BY c.LastName, c.FirstName, c.MiddleName, od.ProductID

보시는 바와 같이 SELECT 문에는 fn_FullName이 존재합니다. 이 쿼리를 실행하면 다음과 같습니다.

실행계획은 완전히 똑같이 나옵니다. 여기까지는 훨씬 더 좋은 결과로 보여집니다. 

하지만 프로파일러는 어떨까요? 

아래와 같이 또한 여러번을 수행합니다. 다만 차이가 있다면, WHERE 조건에 UDF를 제거함으로써 인덱스를 탈 수 있다는 장점(?)이 존재합니다.

Chapter 4. 결론

결론적으로 UDF는 아직까지는 MSSQL에는 큰 성능효과를 기대하기 어렵습니다. 여러 인터넷에서 WHERE 조건에만 제거하면 좋다고 나오지만, 아무리 인덱스를 

SEEK하고, 있다고 하지만 그 건수가 많아진다면, 부하가 있을 수 밖에 없습니다. 그러므로 적절한 위치와 꼭 필요한 부분에서만 UDF를 사용하여, 부하를 줄이는 

것이 바람직 하다고 생각 됩니다. 

감사합니다.

참고 

책 : SQL MVP 61인 - 20장 사용자 정의 함수 (UDF)

MSDN : https://msdn.microsoft.com/en-us/library/ms191320.aspx