UDF와 Scalar T-SQL UDF Inlining

UDF(User-Defined Functions: 사용자 정의 함수)란?

매개변수를 받아서, 복잡한 계산/쿼리문 수행 후, 결과를 함수 값으로 반환합니다. 반환 값은 스칼라 값 또는 테이블 형태의 결과 집합이 됩니다.

●  모듈식 프로그래밍

함수를 생성하여 데이터베이스에 저장 후에는, 프로그램에서 여러 번 호출하며 모듈 형식으로 프로그래밍이 가능합니다. 데이터베이스 안에 함수 형태로 로직이 저장되어 함수 내의 로직만 변경하면 모든 응용프로그램이 코드 수정 없이 영향을 받게 할 수 있습니다.

●   캐시 저장

실행 계획을 프로시저 캐시에 저장합니다. 반복실행 시 이를 재사용하여 쿼리문의 컴파일 비용을 줄입니다.

 

ScalarUDF 란?

매개변수를 받아 로직을 처리한 뒤 단일 데이터 값을 반환하는 UDF(User-Defined Functions: 사용자 정의 함수)를 스칼라 사용자 정의 함수라고 합니다.

/*스칼라 UDF의 예시*/ CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2)) RETURNS DECIMAL (12,2) AS BEGIN   RETURN @price * (1 - @discount); END

 

TVF 란?

TVF(Table-Valued UDF)는 테이블을 반환하는 사용자 정의 함수입니다. TVF는 크게 Inline TVF와 multi-statement TVF로 나뉩니다. 기능적으로는 Inline TVF는 parameterized view 와 동일하나, multi-statement TVF는 return값을 테이블 변수로 출력합니다. 테이블 변수는 통계정보를 사용하지 않아 성능을 저하시킵니다.

/*multi-statement TVF 예시*/ CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int) RETURNS @retContactInformation TABLE (  [PersonID] int NOT NULL,  [FirstName] [nvarchar](50) NULL,  [LastName] [nvarchar](50) NULL,  [JobTitle] [nvarchar](50) NULL,  [BusinessEntityType] [nvarchar](50) NULL ) AS BEGIN  IF @PersonID IS NOT NULL  BEGIN   IF EXISTS(SELECT * FROM [HumanResources].[Employee] e   WHERE e.[BusinessEntityID] = @PersonID)   INSERT INTO @retContactInformation   SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'   FROM [HumanResources].[Employee] AS e   INNER JOIN [Person].[Person] p              ON p.[BusinessEntityID] = e.[BusinessEntityID]   WHERE e.[BusinessEntityID] = @PersonID;  END  RETURN; END;

 

 

	Inline TVFs	MultiStatement TVFs
Accepts Parameters	Y	Y
Expanded out by Optimiser	Y	N
Can be Materialized in advance	N	N
Is Updatable	Y	N
Can contain Multiple Statements	N	Y
Can have triggers	N	N
Can use side-effecting operator	N	N

 

Scala UDF가 느린 이유는 무엇일까요? 간단하게 말씀드리자면 병렬처리를 하지 않고 ROW만큼을 호출하기 때문입니다. 

●   반복 호출: UDF는 대상 튜플 당 반복적으로 호출된다. 이로 인해 함수 호출로 인한 반복된 컨텍스트 전환의 추가 비용이 발생한다. 특히 본문에서 SQL 조회를 실행하는 UDF는 심각한 영향을 준다.

●   비용 증가: 최적화 중에는 관계 연산자만 비용이 청구되고 스칼라 연산자는 계산되지 않는다. Scala UDF가 도입되기 전에 다른 스칼라 연산자는 일반적으로 저렴했으며 비용이 필요하지 않았다. 스칼라 작업에 추가된 작은 CPU 비용으로 충분했다.

●   Interpreted 실행: UDF는 명령문 별로 실행되고 명령문의 일괄 처리로 평가 된다. 각 명령문 자체는 컴파일 되고 컴파일된 계획은 캐시 된다. 이 캐싱 전략은 재컴파일이 발생할때 까지 시간을 절약하지만 각 문은 독립적으로 실행된다. 교차 명령문 최적화는 수행되지 않는다.

●   Serial 실행: SQL Server는 UDF를 호출하는 쿼리에서 쿼리 내 병렬 처리를 사용하지 않는다.

 

위와 같은 이유 때문에 Scala UDF를 Inline TVF로 변환하여 성능을 향상시키는 사례가 있어왔고, 이를 반영하고자 SQL Server 2019에서 도입된 기능이 Scalar T-SQL UDF Inlining입니다.

 

사용방법

●  호환성 레벨(compatibility level)을 150 이상으로 설정합니다.

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 150;

●  호환성 레벨 150 이상을 유지하면서 해당 기능을 비활성 시키고자 한다면 아래 구문을 실행합니다.

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

●  함수 수준으로 적용하고자 할 때는, WITH INLINE = ON | OFF 를 사용합니다.

●  호환성 레벨 150이상에서 쿼리 수준으로 해당 기능을 비활성 시키고 싶다면 OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))구문을 사용합니다.

 

적용하면 어떻게 달라질까? ([AdventureWorks2019] 테스트 셋 사용)

/* Scalar UDF 예시*/ ALTER FUNCTION [dbo].[customer_taxtotal_test001](@ckey INT) RETURNS CHAR(10) AS BEGIN   DECLARE @total_price DECIMAL(18,2);   DECLARE @category CHAR(10);     SELECT @total_price = SUM(TaxAmt) FROM [Sales].[SalesOrderHeader] WHERE CustomerID = @ckey;     IF @total_price < 10000     SET @category = 'REGULAR';   ELSE IF @total_price < 50000     SET @category = 'GOLD';   ELSE     SET @category = 'PLATINUM';     RETURN @category; END

 

호환성 수준 140 이하에서 아래 쿼리를 실행하면 CUSTOMER 테이블의 모든 튜플에 대해 UDF를 호출하고 결과를 출력합니다. 

SELECT CustomerID, dbo.customer_taxtotal_test001(CustomerID) FROM [Sales].[Customer]

XML로 실행계획을 확인하면 UserDefinedFuction이라는 부분이 눈에 띕니다.

 

 

호환성 수준 150 이상에서 실행하면 실행계획은 아래와 같습니다.

호환성 수준 150 이상에서의 실행계획은 더 복잡하여 비효율적으로 보일 수 있으나 실제로는 더 좋고, 만약 이 쿼리가 병렬 처리에 적합했다면 이제 병렬로도 실행될 수 있습니다!

·        SQL Server는 Customer 와 SalesOrderHeader간의 암시적 조인을 유추하고 명시적으로 만들었습니다.

·        또한 SQL Server는 SalesOrderHeader의 CustomerID에 대한 암시적 GROUP BY를 유추하고 IndexSpool + StreamAggregate를 사용했습니다.

 

Query:	Query without UDF	Query with UDF (without inlining)	Query with Scalar UDF Inlining
Execution time:	1.6 seconds	29 minutes 11 seconds	1.6 seconds

 

 

모든 Scalar UDF에 적용될까?

그렇지 않습니다. 아래 사이트에서 Scalar T-SQL UDF Inline을 가능하게 하는 요구사항에 대해 확인하실 수 있습니다. 더 자세한 내용에 대해서 궁금하신 분도 아래 링크 참고 부탁드립니다.

 

참고링크: https://learn.microsoft.com/ko-kr/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver16