DataBaser.Net: Function을이용한연결

MSSQL Server 2000부터 함수를 사용할 수 있게 되었다. 함수는 여러 가지 데이터형으로 리턴값을 넘기며, 테이블형 데이터도 리턴할 수 있다. 엄청난 발전이다. 함수를 적절히 이용하면 상당히 성능이 올라갈 수도 있고, 또한 비즈니스 로직을 담을 수도 있으므로 관리상으로도 편하다. 많은 경우 함수가 각각의 행마다 실행되는 경우도 있다. 다음의 경우를 살펴보자.

CREATE FUNCTION fn_SumUnitPrice (@ProductID int)
RETURNS NUMERIC
AS
BEGIN
   DECLARE @result NUMERIC
   SELECT @result = SUM(UnitPrice) FROM SalesOrderDetail
   WHERE ProductID = @ProductID
   RETURN(@result)
END
GO
 
SELECT ProductID, dbo.fn_SumUnitPrice(ProductID)
FROM(
         SELECT ProductID
         FROM SalesOrderDetail
         GROUP BY ProductID) A
WHERE dbo.fn_SumUnitPrice(ProductID) > 10000

fn_SumUnitPrice함수는 SalesOrderDetail 테이블에서 ProductID를 입력받아 ProductID별로 합계를 구하는 함수이다. 이 함수를 이용해서 함수 생성 다음에 나오는 쿼리는 함수의 결과 즉, ProductID의 UnitPrice 함계가 10000 이상인 것만 결과집합을 만들라는 것이다. 그러나 실행을 해보면 깜깜 무소식이다. SalesOrderDetail 테이블의 ProductID의 종류는 266가지이다. 위에서 보면 함수가 두 번 명시되었으므로 266 * 2 번 만큼 함수가 수행되었다. 물론 그냥 ProductID별로 Sum()을 하면 된다. 위 예제는 그러한 경우를 만들기 위한 예제이므로 쿼리가 이상하다는 생각은 잠시 접도록 하자. 그렇다면 InLine View를 이용하면 어떨까? 아래와 같이 해보자.

SELECT ProductID, SumUnitPrice
FROM (
            SELECT ProductID, dbo.fn_SumUnitPrice(ProductID) 'SumUnitPrice'
            FROM(
                     SELECT ProductID
                     FROM SalesOrderDetail
                     GROUP BY ProductID) A ) B 
WHERE SumUnitPrice > 10000

역시 답이 안 나온다. 이유는 InLine View를 사용하더라도 실행계획은 SumUnitPrice > 10000 대신 dbo.fn_SumUnitPrice(ProductID) > 10000 으로 비교를 하기 때문이다. 아래를 보면 알 수 있다.

SELECT TOP 1 ProductID, SumUnitPrice
FROM (
            SELECT ProductID, dbo.fn_SumUnitPrice(ProductID) 'SumUnitPrice'
            FROM(
                     SELECT ProductID
                     FROM SalesOrderDetail
                     GROUP BY ProductID) A ) B 
WHERE SumUnitPrice > 10000

Filter로 fn_SumUnitPrice(ProductID) > 10000 이 사용되었다. 그러므로 이러한 경우는 인라인 뷰를 사용하더라도 좋은 결과를 가져올 수 없음을 알 수 있다. WHERE 조건을 넣는 것보다 넣지 않는 것이 결과는 훨씬 빨리 나온다. 그러므로 이러한 경우는 일단 WHERE 조건의 함수를 없애고 임시테이블을 사용해야 한다.

SELECT ProductID, dbo.fn_SumUnitPrice(ProductID) 'SumUnitPrice'
INTO #SumUnitPrice
FROM(
         SELECT ProductID
         FROM SalesOrderDetail
         GROUP BY ProductID) A 
 
SELECT ProductID, SumUnitPrice
FROM #SumUnitPrice
WHERE SumUnitPrice > 10000
 
DROP TABLE #SumUnitPrice

필자는 함수에 SLECT ~ FROM 을 웬만하면 사용하지 말기를 권장한다. 물론 함수를 사용하면 모듈화되어 변경이 쉽고 또한 더욱 성능을 좋게 할 수 있지만 함수를 잘못 사용하면 엄청난 비효율을 가져올 수 있음을 알아야 한다. 함수를 이용한 성능 튜닝은 함수를 이용한 연결 액세스 튜닝 사례를 참고하기 바란다.

Contents