スカラー UDF のインライン化

適用対象:SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウスSql データベース

この記事では、SQL データベースにおけるインテリジェントなクエリ処理機能スイートに含まれる機能であるスカラー UDF のインライン化について説明します。 この機能により、SQL Server 2019 (15.x) 以降のバージョンでスカラー UDF を呼び出すクエリのパフォーマンスが向上します。

T-SQL スカラーのユーザー定義関数

Transact-SQL に実装され、単一のデータ値を返すユーザー定義関数 (UDF) は、T-SQL スカラー ユーザー定義関数と呼ばれます。 T-SQL の UDF は、Transact-SQL クエリ間でコードの再利用とモジュール性を実現するためのすばらしい方法です。 複雑なビジネス ルールなどの一部の計算は、命令型 UDF 形式で表現する方が簡単です。 UDF は、SQL クエリの記述に専門知識を必要とせずに、このようなロジックを構築するのに役立ちます。 UDF の詳細については、「ユーザー定義関数の作成 (データベース エンジン)」を参照してください。

スカラー UDF のパフォーマンス

スカラー UDF は、通常、次の理由によりパフォーマンスが低下します。

  • 反復的な呼び出し。 SQL データベース エンジンは、条件を満たすタプルごとに 1 回、UDF を繰り返し呼び出します。 このプロセスでは、関数呼び出しによるコンテキストの切り替えが繰り返されるため、追加のコストが追加されます。 定義で Transact-SQL クエリを実行する UDF は重大な影響を受けます。

  • コスト計算の欠如。 最適化中、データベース エンジンは関係演算子のみをコスト処理しますが、スカラー演算子のコストはかからありません。 スカラー UDF が導入される前は、他のスカラー演算子は一般的に低コストであり、コスト計算を必要としませんでした。 スカラー演算用に少し CPU コストを追加すれば十分でした。 実際のコストが大きいが、オプティマイザーが引き続きそれを過小評価しているシナリオがあります。

  • 解釈形式の実行。 データベース エンジンは UDF をステートメントのバッチとして評価し、ステートメントごとにステートメントを実行します。 各ステートメントがコンパイルされ、コンパイルされたプランがキャッシュされます。 このキャッシュ戦略では、再コンパイルを回避することで時間を節約できますが、各ステートメントは分離して実行されます。 データベース エンジンは、ステートメント間の最適化を実行しません。

  • 直列実行。 SQL Server では、UDF を呼び出すクエリでクエリ内の並列処理を行うことはできません。

スカラー UDF の自動インライン化

スカラー UDF インライン化機能の目的は、UDF の実行が主なボトルネックである T-SQL スカラー UDF を呼び出すクエリのパフォーマンスを向上することです。

UDF インライン化機能を使用すると、データベース エンジンはスカラー UDF をスカラー式またはスカラー サブクエリに自動的に変換します。 データベース エンジンは、UDF 演算子の代わりに、呼び出し元のクエリでこれらの式またはサブクエリを置き換えます。 その後、クエリ オプティマイザーは、これらの式とサブクエリを最適化します。 その結果、クエリ プランにはユーザー定義関数演算子がなくなりましたが、ビューやインライン テーブル値関数 (TVF) など、プラン内でその効果を確認できます。

Microsoft Fabric Data Warehouse でのスカラー UDF の自動インライン化

Microsoft Fabric Data Warehouse では、関数本体と呼び出し元クエリがインライン化の要件を満たすと、コンパイル時にスカラー UDF (現在プレビュー段階) が自動的にインライン化されます。 詳細については、「 CREATE FUNCTION と スカラー UDF のインライン化」を参照してください。

例

このセクションの例では、TPC-H ベンチマーク データベースを使用します。 詳細については、TPC-H ホームページを参照してください。

A. 単一ステートメントのスカラー 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) は、特定の品目の割引価格の式です。 このような式は、モジュール化と再利用のために関数として抽出できます。

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

そして、この 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;

前述の理由により、UDF を使用したクエリのパフォーマンスは低下します。 スカラー UDF のインライン化を使用すると、UDF の本体のスカラー式はクエリ内で直接置き換えられます。 このクエリの実行結果は次の表のようになります。

Query UDF なしのクエリ UDF ありのクエリ (インライン化なし) スカラー UDF インライン化ありのクエリ
Execution time 1.6 秒 29 分 11 秒 1.6 秒

これらの値は、10 GB の CCI データベース (TPC-H スキーマを使用) を使用し、デュアル プロセッサ (12 コア)、96 GB の RAM、SSD を備えたコンピューターで実行した場合のものです。 値には、コールド プロシージャ キャッシュとバッファー プールを使用したコンパイルと実行の時間が含まれます。 既定の構成が使用され、他のインデックスは作成されませんでした。

B: 複数ステートメントのスカラー UDF

変数の割り当てや条件付き分岐など、複数の T-SQL ステートメントを含むスカラー UDF をインライン化することもできます。 カスタマー キーを指定されて、その顧客のサービス カテゴリを決定する、次のようなスカラー UDF について考えます。 まず、SQL クエリを使用して顧客が行ったすべての注文の合計価格を計算することで、カテゴリに到着します。 次に、IF (...) ELSE ロジックを使用して、総額に基づいてカテゴリを決定します。

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS 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 を呼び出すクエリを検討します。

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

SQL Server 2017 (14.x) (互換性レベル 140 およびそれ以前) では、このクエリの実行プランは次のようになります。

インライン化を行わないクエリ プランのスクリーンショット。

プランに示すように、SQL Server では、 CUSTOMER テーブル内のすべてのタプルに対して UDF を呼び出し、結果を出力する、という基本的な戦略が採用されています。 この方法は単純で非効率的です。 インライン化を使用すると、このような UDF を同等のスカラー サブクエリに変換できます。このサブクエリは、呼び出し元のクエリによって UDF の代わりに置き換えられます。

同じクエリの場合、UDF をインライン化したプランは次のようになります。

インライン化を行うクエリ プランのスクリーンショット。

前述のように、クエリ プランにはユーザー定義関数演算子はなくなりましたが、ビューやインライン TVF など、プランでその効果を確認できるようになりました。 前回の計画からの主な観察事項は次のとおりです。

  • SQL Server は、CUSTOMER と ORDERS の間の暗黙的な結合を推論し、結合演算子を使用して明示的にします。

  • また、暗黙の GROUP BY O_CUSTKEY on ORDERS を推論し、IndexSpool と StreamAggregate を使用してそれを実装します。

  • すべての演算子で並列処理が使用されるようになっています。

UDF のロジックの複雑さによっては、結果として得られるクエリ プランも大きくなり、複雑になる場合があります。 ご覧のように、UDF 内の操作は不透明ではなくなったため、クエリ オプティマイザーはそれらの操作のコストと最適化を行うことができます。 また、UDF がプランに含まれなくなったため、反復的な UDF の呼び出しは、関数呼び出しのオーバーヘッドがまったくないプランに置き換えられています。

インライン化可能なスカラー UDF の要件

関数定義で許可された構造が使用され、関数がインライン化を有効にするコンテキストで使用される場合、スカラー T-SQL UDF をインライン化できます。

UDF 定義の次の条件はすべて true である必要があります。

  • UDF が、次のコンストラクトを使用して書かれている。
    • DECLARE、SET: 変数の宣言と代入。
    • SELECT: 単一/複数の変数代入を含む SQL クエリ 1。
    • IF / ELSE: 任意の入れ子レベルでの分岐。
    • RETURN: 1 つまたは複数の return ステートメント。 SQL Server 2019 (15.x) CU5 以降では、UDF に含めることができるのは、インライン化で考慮すべき 1 つの RETURN ステートメントのみです 6。
    • UDF: 入れ子になった関数または再帰関数の呼び出し 2。
    • その他: EXISTS、IS NULL などの関係演算。
  • UDF で、時間に依存する組み込み関数 (GETDATE() など) または副作用のある組み込み関数 3 (NEWSEQUENTIALID() など) が呼び出されていない。
  • UDF で、EXECUTE AS CALLER 句が使用されている (EXECUTE AS 句が指定されていない場合の既定の動作)。
  • UDF で、テーブル変数またはテーブル値パラメーターが参照されていない。
  • UDF がネイティブでコンパイルされていない (相互運用機能はサポートされます)。
  • UDF で、ユーザー定義型が参照されていない。
  • UDF にシグネチャが追加されていない 9。
  • UDF がパーティション関数ではない。
  • UDF に、共通テーブル式 (CTE) への参照が含まれていない。
  • UDF に、インライン化されると結果が変わる可能性がある組み込み関数 (@@ROWCOUNT など) への参照が含まれていない 4。
  • UDF に、パラメーターとしてスカラー UDF に渡される集計関数が含まれていない 4。
  • UDF で、組み込みのビュー (OBJECT_ID など) が参照されていない 4。
  • UDF で、XML メソッドが参照されていない 5。
  • UDF に、ORDER BY 句のない TOP 1 を持つ SELECT が含まれていない 5。
  • UDF に、ORDER BY 句 (SELECT @x = @x + 1 FROM table1 ORDER BY col1 など) と組み合わせて代入を実行する SELECT クエリが含まれていない 5。
  • UDF に、複数の RETURN ステートメントが含まれていない 6。
  • UDF で、STRING_AGG 関数が参照されていない 6。
  • UDF で、リモート テーブルが参照されていない 7。
  • UDF で、暗号化された列が参照されていない 8。
  • UDF に WITH XMLNAMESPACES への参照が含まれていない 8。
  • UDF の定義が数千行のコードになる場合、SQL Server はインライン化しないことを選択する可能性があります。

1SELECT は変数の累積/集計を含む場合 (SELECT @val += col1 FROM table1 など)、インライン化ではサポートされません。

2 再帰的な UDF は、特定の深さまでのみインライン化されます。

3 現在のシステム時刻によって結果が異なる組み込み関数は、時間に依存します。 内部のグローバル状態を更新する可能性のある組み込み関数は、副作用のある関数の例です。 このような関数は、内部の状態に基づいて、呼び出されるたびに異なる結果を返します。

4 SQL Server 2019 (15.x) CU 2 で追加された制限事項

5 SQL Server 2019 (15.x) CU 4 で追加された制限事項

6 SQL Server 2019 (15.x) CU 5 で追加された制限事項

7 SQL Server 2019 (15.x) CU 6 で追加された制限事項

8 SQL Server 2019 (15.x) CU 11 で追加された制限事項

9 UDF が作成された後に署名を追加および削除できるため、スカラー UDF を参照するクエリをコンパイルするときに、インライン化するかどうかの決定が行われます。 たとえば、システム関数は通常、証明書で署名されます。 sys. crypt_properties を使用して、署名されているオブジェクトを見つけることができます。

実行コンテキストの次の要件はすべて true である必要があります。

  • UDF は ORDER BY 句では使用されていない。
  • スカラー UDF を呼び出すクエリは、その GROUP BY 句でスカラー UDF 呼び出しを参照していない。
  • DISTINCT 句でその選択リストのスカラー UDF を呼び出すクエリには、ORDER BY 句は含まれていない。
  • UDF が、RETURN ステートメントから呼び出されていない 1。
  • UDF を呼び出すクエリには、共通テーブル式 (CTE) が含まれていない 3。
  • UDF 呼び出しクエリでは、GROUPING SETS、CUBE、ROLLUP が使用されない 2。
  • UDF 呼び出し元のクエリには、割り当て用の UDF パラメーターとして使用する変数 (たとえば、 SELECT @y = 2、 @x = UDF(@y)) 2 が含まれません。
  • 計算列または check 制約定義では UDF を使用しません。

1 SQL Server 2019 (15.x) CU 5 で追加された制限事項

2 SQL Server 2019 (15.x) CU 6 で追加された制限事項

3 SQL Server 2019 (15.x) CU 11 で追加された制限事項

最新の T-SQL スカラー UDF インライン化の修正プログラムとインライン化の適格性シナリオの変更については、サポート技術情報の記事「FIX: SQL Server 2019 のスカラー UDF インライン化の問題」を参照してください。

UDF をインライン化できるかどうかを確認する

すべての T-SQL スカラー UDF について、sys.sql_modules カタログ ビューに is_inlineable という名前のプロパティが含まれており、これは UDF がインライン化可能かどうかを示します。

is_inlineable プロパティは、UDF 定義内にあるコンストラクトから派生します。 コンパイル時に UDF が実際にインライン化可能かどうかは確認されません。 詳細については、インライン化の条件を参照してください。

1 の値は UDF がインライン可能であることを示し、0 はそれ以外の場合を示します。 すべてのインライン TVF についても、このプロパティの値は 1 になります。 他のすべてのモジュールでは、値は 0 になります。

スカラー UDF がインライン化できる場合でも、常にインライン化されているわけではありません。 SQL Server は、UDF をインライン化するかどうかを (クエリごと、UDF ごとに) 決定します。 この記事で前述した要件の一覧を参照してください。

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

インライン化が発生したかどうかを確認する

すべての前提条件が満たされていて、SQL Server がインライン化の実行を決定した場合、UDF は関係式に変換されます。 クエリ プランから、インライン化が発生したかどうかを確認できます。

  • プラン XML には、正常にインライン化された UDF の <UserDefinedFunction> XML ノードは含まれません。
  • 特定の拡張イベントが出力されます。

スカラー UDF のインライン化を有効にする

データベースに対して互換性レベル 150 を有効にすることで、自動的にワークロードをスカラー UDF インライン化の対象にすることができます。 これは Transact-SQL を使って設定できます。 例えば次が挙げられます。

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

この機能を利用するために UDF またはクエリに加える必要のある変更はこの手順以外にはありません。

互換性レベルを変更せずにスカラー UDF インライン化を無効にする

データベース互換性レベル 150 以上を維持したまま、データベース、ステートメント、または UDF スコープでスカラー UDF インライン化を無効にすることができます。 データベース スコープでスカラー UDF インライン化を無効にするには、該当するデータベースのコンテキスト内で次のステートメントを実行します。

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

データベースのスカラー UDF インライン化を再度有効にするには、該当するデータベースのコンテキスト内で次のステートメントを実行します。

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

このオプションを ONに設定すると、 sys.database_scoped_configurationsで有効として表示されます。

DISABLE_TSQL_SCALAR_UDF_INLINING クエリ ヒントとして USE HINT を指定することで、特定のクエリについてスカラー UDF のインライン化を無効にすることもできます。

USE HINT クエリ ヒントは、データベース スコープの構成または互換性レベルの設定より優先されます。

例えば次が挙げられます。

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
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

また、 CREATE FUNCTION または ALTER FUNCTION ステートメントの INLINE 句を使用して、特定の UDF のスカラー UDF インライン化を無効にすることもできます。 例えば次が挙げられます。

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

前のステートメントを実行した後、この UDF は、それを呼び出すクエリにインライン化されることはありません。 この UDF のインライン化を再度有効にするには、次のステートメントを実行します。

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

INLINE 句は必須ではありません。 INLINE句を指定しない場合、UDF をインライン化できるかどうかに基づいて、ONまたはOFFに自動的に設定されます。 INLINE = ONを指定しても、UDFがインライン化の対象外であると判明した場合は、エラーが発生します。

注釈

この記事で説明したように、スカラー UDF のインライン化では、スカラー UDF を含むクエリが、同等のスカラー サブクエリを含むクエリに変換されます。 この変換により、次のシナリオでは動作に多少の違いが見られる場合があります。

  • インライン化により、同じクエリ テキストに対して異なるクエリ ハッシュが生成されます。

  • UDF 内のステートメント内の特定の警告 (0 で除算するなど) は、前に非表示になっている可能性があります。これは、インライン化が原因で表示される可能性があります。

  • インライン化によって新しい結合が導入される場合があるため、クエリ レベルの結合ヒントが有効ではなくなる可能性があります。 代わりにローカル結合ヒントを使用する必要があります。

  • インライン スカラー UDF を参照するビューにインデックスを作成することはできません。 そのようなビューにインデックスを付ける必要がある場合は、参照されている UDF のインライン化を無効にします。

  • UDF のインライン化により、動的データ マスクの動作に多少の違いが生じる可能性があります。

    特定の状況では (UDF のロジックに応じて)、出力列のマスキングに関してはインライン化のほうが保守的になる場合があります。 UDF で参照されている列が出力列ではない場合、それらはマスクされません。

  • UDF で SCOPE_IDENTITY()、@@ROWCOUNT、@@ERROR などの組み込み関数が参照されている場合、組み込み関数によって返される値はインライン化によって変わります。 このような動作の変化は、UDF 内のステートメントのスコープがインライン化によって変化するためです。 SQL Server 2019 (15.x) CU2 以降では、UDF で特定の組み込み関数 (@@ROWCOUNT など) が参照されている場合、インライン化はブロックされます。

  • インライン UDF の結果を含む変数を割り当て、index_column_nameFORCESEEK のとしても使用すると、エラー 8622 になります。 このエラーは、クエリで定義されているヒントのためにクエリ プロセッサがクエリ プランを生成できなかったことを示します。

関連するコンテンツ

  • Last updated on