次の方法で共有

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

  • [アーティクル]

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

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

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

Transact-SQL で実装されていて単一のデータ値を返すユーザー定義関数 (UDFs) は、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) など、その効果がプランで観察されます。

例

このセクションの例では、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 インライン化ありのクエリ
実行時間: 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 の間の暗黙的な結合を推論し、結合演算子を使用して明示的にします。

  • SQL Server は暗黙的 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 はインライン化しないことを選択する可能性があります。

1 変数の累積/集計を使用した SELECT は、インライン化ではサポートされていません (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 呼び出し元のクエリには、割り当て (たとえば、 SELECT @y = 2、 @x = UDF(@y)) 2の UDF パラメーターとして使用される変数が含まれません。
  • 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 *
FROM sys.crypt_properties AS cp
     INNER JOIN sys.objects AS o
         ON cp.major_id = o.object_id;

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

すべての前提条件が満たされていて、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 内のステートメントでの特定の警告 (0 による除算など) が、インライン化によって表示されるようになる場合があります。

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

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

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

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

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

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

関連するコンテンツ