UDF(User Defined Function) 사용시 잘못된 비용을 예측하여 실행계획을 생성하는 경우가 있어 공유 드립니다.
UDF 사용시에는 실제 실행계획 + SET STATISTICS IO ON 뿐만 아니라 예상 실행 계획 + SET STATISTICS TIME ON+ PROFILER 를 확인하여 검토하여야 할 것 같습니다.
아래는 UDF 사용에 따른 비용 확인을 테스트 한 내용입니다.
TEST 1 . SELECT 내에서 계산과 함수계산에 대한 실행계획을 비교
| --테스트용 테이블 생성 CREATE TABLE dbo.FuncTestTable( idx int ,name varchar(255) ) GO --데이터 입력 INSERT INTO FuncTestTable(idx,name) SELECT top 100000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) ,'TESTNAME' FROM master..spt_values AS a CROSS JOIN master..spt_values AS b; -- 테스트 함수 생성 RETURN(입력값 * 5) CREATE FUNCTION dbo.FuncTest5(@idx int) RETURNS INT AS BEGIN DECLARE @R int; SET @R = @idx *5; RETURN @R END
-- CPU 사용 시간 비용 확인을 위하여 SET STATISTICS TIME ON 을 추가 SET STATISTICS TIME ON SELECT MAX(dbo.FuncTest5(idx)) FROM dbo.FuncTestTable; SELECT MAX(5*idx) FROM dbo.FuncTestTable; SET STATISTICS TIME OFF |
TEST1. 결과확인
실제 실행계획 : 함수 사용시 비용이 49% 로 더 적은 비용을 사용한 것을 확인 할 수 있음
CPU Time 확인 : 실제 실행 계획과는 틀리게 CPU 를 더 소모 하였다는 것을 확인 할 수 있음
예상실행계획 : 예상실행 계획에서는 실제 실행계획에서 확인하지 못했던 UDF의 실행계획을 확인 할 수 있음
TEST 1 결론 : UDF 사용시 함수내에서 사용되는 비용이 계산 되지 않아 실제 실행계획과 함께 SET STATISTICS TIME ON 및 예상실행 계획을 통하여 확인하여야 함.
TEST 2 .함수에 SELECT 문장을 추가하여 추가된 IO 비용 및 실행계획을 확인
--IO 비용을 추가하기 위하여 SELECT 절을 추가한 함수 생성 CREATE FUNCTION dbo.FuncTest5_2(@idx int) RETURNS INT AS BEGIN DECLARE @R int; DECLARE @R2 int; SET @R = @idx *5; SELECT top 1 @R2 = idx FROM dbo.FuncTestTable; RETURN @R END
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT MAX(dbo.FuncTest5(idx)) FROM dbo.FuncTestTable;
SELECT MAX(dbo.FuncTest5_2(idx)) FROM dbo.FuncTestTable;
SELECT MAX(5*idx) FROM dbo.FuncTestTable; SET STATISTICS TIME OFF SET STATISTICS IO OFF |
TEST2. 결과확인
TEST2 실제 실행계획 : 기존 함수와 동일한 비용인 33% 로 SELECT 절에서 계산하는 비용보다 더 적은 비용을 사용하였음을 확인 할 수 있음
CPU Time 확인 : SELECT 를 포함한 UDF 함수가 CPU 를 훨씬 많이 소모 하였다는 것을 확인 할 수 있음
IO 비용 확인 : IO 비용은 함수내에 SELECT 가 포함되어 있어 더 많이 확인 되어야 하는데 동일한 결과가 출력 (이것 역시 믿을 수 없는 결과로 확인)
프로파일러 확인 : 함수를 포함한 쿼리의 IO 비용 및 CPU 비용을 이제서야 확인 할 수 있음
예상 실행 계획 : 예상실행 계획에서는 실제 실행계획에서 확인하지 못했던 UDF의 실행계획을 확인 할 수 있음
최종 결론 .
UDF 사용시 실제 실행 계획 및 SET STATISTICS IO ON 만으로는 비용을 계산할 수 없으므로 PROFILER , 예상 실행계획 , SET STATISTICS TIME ON 을 확인하여 비용을 확인 하여야 할 것 같습니다.