次の方法で共有

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

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

この記事では、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 を使用すると、通常、次の理由でパフォーマンスが低下します。

  • 反復的な呼び出し。 UDF は、該当するタプルごとに 1 回ずつ、反復的な方法で呼び出されます。 このため、関数呼び出しによる反復的なコンテキスト切り替えの追加コストが発生します。 特に、定義内で Transact-SQL クエリを実行する UDF は大きな影響を受けます。

  • コスト計算の欠如。 最適化では、関係演算子のみがコスト計算されて、スカラー演算子はされません。 スカラー UDF が導入される前は、他のスカラー演算子は一般的に低コストであり、コスト計算を必要としませんでした。 スカラー演算用に少し CPU コストを追加すれば十分でした。 実際のコストは大きいのにいまだにコストが低いと認識されているシナリオがあります。

  • 解釈形式の実行。 UDF はステートメントのバッチとして評価されて、ステートメントごとに実行します。 各ステートメント自体はコンパイルされて、コンパイル済みのプランがキャッシュされます。 このキャッシュ対策は再コンパイルを回避できるので若干の時間節約になりますが、各ステートメントは別々に実行されます。 クロスステートメントの最適化は実行されません。

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

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

スカラー UDF インライン化機能の目的は、UDF の実行が主なボトルネックになる、T-SQL スカラー 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 の本体のスカラー式はクエリ内で直接置き換えられます。 このクエリの実行結果は次の表のようになります。

クエリ: 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 に、TOP 1 句のない ORDER BY を持つ 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。
  • UDF が、計算列または CHECK 制約定義で使用されていない。

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 のインライン化の修正、およびインライン化の資格シナリオの変更については、サポート技術情報の記事「修正: 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 インライン化を無効にする

スカラー UDF のインライン化は、データベースの互換性レベルを 150 以上に維持しながら、データベース、ステートメント、または 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 で有効と表示されます。

USE HINT クエリ ヒントとして DISABLE_TSQL_SCALAR_UDF_INLINING を指定することで、特定のクエリについてスカラー 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 内のステートメントの特定の警告 (ゼロ除算など) が表示される場合があります。

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

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

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

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

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

  • 変数がインライン UDF の結果と共に割り当てられ、FORCESEEKQuery ヒント (Transact-SQL) でindex_column_nameとしても使用される場合、エラー 8622 が発生し、クエリで定義されたヒントのためにクエリ プロセッサがクエリ プランを生成できなかったことを示します。

関連するコンテンツ