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

適用対象:SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

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

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

Transact-SQL で実装され、1 つのデータ値を返すUser-Defined関数 (UDF) は、T-SQL スカラー User-Defined関数と呼ばれます。 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 などのように、その効果はプランに反映されます。

例 1 - 単一ステートメントスカラー 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 インライン化を使用したクエリ
実行時間: 1.6 秒 29 分 11 秒 1.6 秒

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

例 2 - 複数ステートメントスカラー 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 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 を呼び出すクエリを検討します。

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 により、CUSTOMERORDERS の間に暗黙の結合が推論され、それが結合演算子によって明示化されています。
  • また、SQL Server によって暗黙の GROUP BY O_CUSTKEY on ORDERS が推論され、IndexSpool と StreamAggregate を使用して実装されています。
  • SQL Server では、すべての演算子で並列処理が使用されるようになっています。

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

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

以下のすべての条件に該当する場合、スカラー T-SQL UDF はインライン化できます。

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

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

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

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

4 SQL Server 2019 (15.x) CU2 に制限を追加

5 SQL Server 2019 (15.x) CU4 に制限を追加

6 SQL Server 2019 (15.x) CU5 に制限を追加

7 SQL Server 2019 (15.x) CU6 に制限を追加

8 SQL Server 2019 (15.x) CU11 で追加された制限

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

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

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

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

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

スカラー UDF がインライン化可能な場合、常にインライン化されることを意味するものではありません。 UDF をインライン化するかどうかは、SQL Server によって (クエリごと、UDF ごとに) 決定されます。 UDF をインライン化できない場合のいくつかの例を次に示します。

  • UDF の定義が数千行のコードになる場合、SQL Server はインライン化しないことを選択する可能性があります。

  • 句内の GROUP BY UDF 呼び出しはインライン化されません。 この決定は、スカラー UDF を参照しているクエリのコンパイル時に行われます。

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

    SELECT *
    FROM sys.crypt_properties AS cp
    INNER JOIN sys.objects AS o ON cp.major_id = o.object_id;
    

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

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

  • プラン xml には、正常に <UserDefinedFunction> インライン化された UDF の xml ノードがありません。
  • 特定の XEvent が生成されています。

スカラー 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 を含むクエリが同等のスカラー サブクエリを持つクエリに変換されます。 この変換のため、次のようなシナリオでは、示される動作が異なる場合があります。

  1. クエリ テキストが同じでも、インライン化が行われるとクエリ ハッシュが異なります。

  2. 以前は表示されなかった UDF 内のステートメントでの特定の警告 (0 による除算など) が、インライン化によって表示されるようになる場合があります。

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

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

  5. UDF をインライン化すると、動的データ マスクの動作が変化する可能性があります。

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

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

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

こちらもご覧ください