반응형

SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상

 

·         Version : SQL Server 2017 later, Azure SQL Database

 

SQL Server 2017 Azure SQL Database 런타임 환경에 최적화된 쿼리 처리 개선 기능을 도입했다. 이러한 개선 사항에는 Batch mode Adaptive Joins, Batch mode memory grant feedback, Interleaved execution for multi-statement table valued functions 포함된다.

·         Batch mode Adaptive Joins : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-adaptive-joins

·         Batch mode memory grant feedback  : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback

·         Interleaved execution for multi-statement table valued functions : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#interleaved-execution-for-multi-statement-table-valued-functions

 

SQL Server 2019에서는 Intelligent query processing(QP) 제품군에서 가지 새로운 기능으로 쿼리 처리 기능을 더욱 확장하고 있다.

·         Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

 

이번 포스트에서는 SQL Server 2019 CTP 2.1에서 사용할 있는QP 기능 하나인 Scalar T-SQL UDF  inline 대해서 살펴본다.

 

T-SQL UDF SQL 쿼리에서 코드 재사용 모듈화를 구현한다. 복잡한 비즈니스 규칙과 같은 일부 계산은 명령형 UDF 형식으로 표현하기가 쉽다. UDF 복잡한 SQL 조회를 작성하는 전문 지식 없이 복잡한 논리를 해결하는데 도움이 된다. 하지만 이러한 장점에도 불구하고 열악한 성능문제 때문에 최대한 사용을 자제하거나 금지한다.

Scala UDF inline 기능의 목표는 UDF 실행의 주요 병목인 Scala UDF 호출하는 쿼리의 성능을 향상 시키는 것이다.  Scala UDF 느렸던 이유는 Scala UDF 도입되었을 , 변수 할당, IF-ELSE 분기, 루프 등과 같은 친숙한 구문을 사용하여 사용자가 비즈니스 로직을 표현하였다. 아래 스크립트는 @ckey값이 주어지면 스칼라 UDF 사용하여 고객이 지정한 모든 주문의 가격을 계산한 다음 IF-ELSE 논리를 사용하여 가격을 기준으로 카테고리 값을 반환한다.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)

RETURNS CHAR(10) AS

BEGIN

       DECLARE @total_price DECIMAL(18,2);

       DECLARE @category CHAR(10);

     

       SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

  

       IF @total_price < 500000

              SET @category = 'REGULAR';

       ELSE IF @total_price < 1000000

              SET @category = 'GOLD';

       ELSE

              SET @category = 'PLATINUM';

       RETURN @category;

END

 

UDF 여러 조회에서 사용할 있으며 임계 값을 갱신하거나 카테고리를 추가해야하는 경우 UDF에서만 변경해야한다. 아래 스크립트는 위에서 만든 UDF 호출하는 간단한 쿼리이다.

-- Q1:

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

 

SQL Server 2017(호환성 수준 140 이전 버전) 버전에서는 쿼리의 실행 계획은 아래와 같다.


SQL Server CUSTOMER 테이블의 모든 튜플에 대해 UDF 호출하고 결과를 출력한다. 실행계획  매우 비효율적이다. 이러한 쿼리는 다음과 같은 이유로 성능에 좋지 않다.

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

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

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

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

 

새로운 Scalar UDF inline 기능으로 변경된 사항은 스칼라 UDF 표현식이나 서브쿼리로 변환되어 UDF 연산자의 호출 조회에서 대체된다. 그러면 이러한 표현식과 서브쿼리가 최적화 된다. 결과적으로 쿼리 계획에는 이상 사용자 정의 함수 연산자가 없지만 또는 인라인 TVF 같은 계획에서 효과가 관찰된다. 이를 이해하기 위해서 먼저 아래 예제를 살펴 본다.

-- Q2 (Query with no UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

쿼리는 광고 항목의 할인 가격 합계를 계산하고 출하 날짜 배송 우선 순위별로 그룹화 하여 결과를 표시한다. 표현식 L_EXTENDEDPRICE * (1 - L_DISCOUNT) 해당 광고 항목의 할인 가격에 대한 수식이다. 할인된 가격을 계산해야하는 곳이면 어디서든 사용할 있도록 계산 함수를 만드는 것이 합리적이다.

-- Scalar UDF to encapsulate the computation of discounted price

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))

RETURNS DECIMAL (12,2) AS

BEGIN

       RETURN @price * (1 - @discount);

END

 

이제 다음과 같이 쿼리 Q2 수정하여 UDF 사용할 있다.

-- Q3 (Query with UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

아래 표는 3개의 쿼리를 실행한 결과이다. 앞에서 설명한 이유 때문에 Q3 Q2 비해 성능이 좋지 않다. 이제 Scala UDF inline 통해 SQL Server 스칼라 표현식을 쿼리로 직접 대체하여 UDF 성능의 한계를 극복한다.

[2CPU (12 core), 96GB RAM, SSD, TPC-H 10GB CCI 데이터 사용]

Query

Q2(UDF 없음)

인라인 업이 Q3

인라인이 포함된 Q3

Execution Time

1.6 second

29 minute 11 second

1.6 second

 

수있듯이 인라인 없이 Q3 Q2 비해 속도가 매우 느리다. 그러나 Scala UDF inline 사용하면 Q3 성능은 거의 오버헤드 없이 거의 Q2 동일하다. 쿼리성능을 저하시키지 않으면서 UDF 모든 이점을 누릴 있다. 또한 쿼리나 UDF 수정이 없었음을 확인할 있다. Scala UDF inline 통해 SQL Server 다중문 UDF 인라인 있다. 위에서 주어진 dbo.customer_category 함수와 Q1함수를 살펴보고 이것이 어떻게 동작하는지 살펴보자. 쿼리 Q1 경우 UDF 인라인된 쿼리 계획은 아래와 같다.


위의 계획에서 얻은 가지 주요 관찰 내용은 아래와 같다.

1.       SQL Server CUSTOMER ORDERS 사이의 암시적 조인을 유추했으며 Join 연산자를 통해 이를 명시적으로 만들었다.

2.       SQL Server ORDERS 대한 암시적 GROUP BY O_CUSTKEY 유추했으며 이를 구현하기 위해 Index Spool Stream Aggregate 사용했다.

3.       SQL Server 모든 연산자에서 병렬처리를 사용한다.

 

UDF 논리 복잡성에 따라 결과 쿼리 계획이 커지고 복잡해 수도 있다. 있듯이 UDF 내부의 작업은 이제 이상 블랙 박스가 아니므로 쿼리 최적화 프로그램은 비용을 절감하고 이러한 작업을 최적화 있다. 또한 UDF 이상 계획에 없으므로 반복 UDF 호출은 함수 호출 오버헤드를 회피하는 계획으로 대체된다.

 

Scala UDF inline 장점은 성능 오버헤드에 대한 걱정없이 사용자가 Scala UDF 사용할 있다.  이로써 사용자는 모듈화되고 재사용가능한 응용프로그램을 구축 있다. UDF 사용한 쿼리에 대해 집합 지향, 병렬 계획을 수행할 있을뿐만 아니라 기능은 다른 장점이 있다. Scala UDF 이상 interpreted 되지 않으므로 ( : 명령문마다 실행됨) 데드코드 제거, 상수 폴딩 상수 전파와 같은 최적화가 가능하다. 이러한 기술은 UDF 따라 간단하고 효율적인 쿼리 계획으로 이어질 있다.

 

Scala UDF inline 가능한 범위는 아래 링크를 참고 한다.

·         Inlineable Scalar UDFs requirements : https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sqlallproducts-allversions#inlineable-scalar-udfs-requirements

 

Scala UDF inline 가능한지 여부는 sys.sql_modules 카탈로그 뷰에서 is_inlineable라는 속성이 있다. 1 인라인이 가능함을 나타내고 0 그렇지 않다. 속성값 정의에 의해 인라인 가능하기 때문에 인라인 테이블 반환 함수의 값은 1이다.

 

Scala UDF inline UDF 실행이 쿼리의 주요 병목일때 가장 유용하다. 병목 현상이 다른곳에 있다면 이점이 없을 수도 있다. 인라인은 CREATE /ALTER FUNCTION문에서 INLINE = OFF 옵션을 사용하여 UDF 대해 해제할 있다. Scala UDF inline 테스트하려면 기본적으로 데이터베이스 호환성 150 사용해야한다. SQL Server 2019 CTP 2.1에서 Scala UDF inline 사용하려면 쿼리를 실행할 연결된 데이터베이스에 대해 데이터베이스 호환성 수준 150 활성화 한다.

USE [master];

GO

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 150;

GO

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/introducing-scalar-udf-inlining/

 

 

2018-11-12 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, UDF, Scala UDF Inline, 스칼라 UDF 인라인




반응형

+ Recent posts