- 2020年08月04日
- 12分で読める
-
-
s -
M -
M -
p -
j -
+6
-
以下に該当します。 
SQL Server (サポートされているすべてのバージョン)
この記事では、Intelligent Query Processing スイートの機能である、Scalar UDF Inlining を紹介します。 この機能は、SQL Server (SQL Server 2019 (15.x) 以降) でスカラー UDF を呼び出すクエリのパフォーマンスを向上させます。
T-SQL スカラー ユーザー定義関数
Transact-SQL で実装され、単一のデータ値を返すユーザー定義関数 (UDF) は、T-SQL スカラー ユーザー定義関数と呼ばれます。 T-SQL UDFは、Transact-SQLのクエリ全体でコードの再利用とモジュール化を実現するエレガントな方法です。 一部の計算（複雑なビジネスルールなど）は、命令型のUDF形式で表現する方が簡単です。 UDFは、複雑なSQLクエリを書くための専門知識を必要とせずに、複雑なロジックを構築するのに役立ちます。 UDF の詳細については、「ユーザー定義関数の作成（Database Engine）」を参照してください。
Performance of scalar UDFs
スカラー UDF は通常、次のような理由でパフォーマンスが低下します。 UDFは、修飾されたタプルごとに1回、反復的に呼び出されます。 そのため、関数の呼び出しによるコンテキスト スイッチの繰り返しという追加コストが発生します。 特に、定義で Transact-SQL クエリを実行する UDF は深刻な影響を受けます。 最適化の際、リレーショナル演算子のみがコスト化され、スカラー演算子はコスト化されません。 スカラー UDF が導入される前は、他のスカラー演算子は一般的に安価で、コスト計算は必要ありませんでした。 スカラー演算のために追加される小さなCPUコストで十分だったのです。 実際のコストが大きいにもかかわらず、まだ不足しているシナリオがあります。
解釈された実行。 UDF はステートメントのバッチとして評価され、ステートメントごとに実行されます。 各ステートメント自体がコンパイルされ、コンパイルされたプランはキャッシュされます。 このキャッシュ戦略は再コンパイルを避けることができるため時間を節約できますが、各ステートメントは独立して実行されます。
シリアル実行。
スカラー UDF の自動インライン化
スカラー UDF のインライン化機能の目的は、UDF の実行が主なボトルネックとなっている、T-SQL のスカラー UDF を呼び出すクエリのパフォーマンスを向上させることです。
この新機能では、スカラ UDF が自動的にスカラ式またはスカラ副問い合わせに変換され、UDF 演算子の代わりに呼び出した問い合わせに代入されます。 そして、これらの式や副問い合わせは最適化されます。
Example 1 – Single statement scalar UDF
以下のクエリを考えてみましょう。
SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) FROM LINEITEMINNER 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) ASBEGIN RETURN @price * (1 - @discount);ENDこれで、このUDFを呼び出すようにクエリを修正することができます。
SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE先に説明した理由により、UDFを使用したクエリのパフォーマンスは低下しました。 スカラーUDFのインライン化では、UDFのボディにあるスカラー式が直接クエリに置き換えられます。
| Query: | UDFを使わないクエリ | UDFを使ったクエリ（インライン化なし） | スカラーUDFをインライン化したクエリ |
|---|---|---|---|
| 実行時間： | 1.6秒 | 29分11秒 | 1.6秒 |
これらの数値は、10GBのCCIデータベース（TPC-Hスキーマを使用）を、デュアルプロセッサ（12コア）、96GBのRAM、SSDでバックアップされたマシン上で実行した場合のものです。 数値には，コールドプロシージャキャッシュとバッファプールを使用した場合のコンパイル時間と実行時間が含まれています．
Example 2 – Multi-statement scalar UDF
変数の割り当てや条件分岐など、複数のT-SQL文を使って実装されるスカラーUDFもインライン化できます。 次のスカラーUDFを考えてみましょう。このUDFは、顧客キーが与えられると、その顧客のサービスカテゴリーを決定します。 このスカラーUDFは、最初にSQLクエリを使って、その顧客が発注したすべての注文の合計価格を計算して、カテゴリーを決定します。
IF (...) ELSEロジックを使用して、合計価格に基づいてカテゴリーを決定します。
CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) RETURNS CHAR(10) ASBEGIN 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をインライン化したプランは以下のようになります。
前述のように、クエリプランにはユーザー定義関数演算子がなくなりましたが、その効果はビューやインラインTVFのようにプラン内で観察できるようになりました。
- SQL Server は
CUSTOMERORDERSの間の暗黙の結合を推測し、結合演算子によってそれを明示しました。 - SQL Server は暗黙的な
GROUP BY O_CUSTKEY on ORDERSも推論し、IndexSpool + StreamAggregate を使用して実装しました。 - SQL Server は現在、すべての演算子で並列処理を使用しています。 このように、UDF内の演算はもはや不透明なボックスではなく、したがって、クエリオプティマイザはこれらの演算をコスト化して最適化することができます。 また、UDF がプランに含まれなくなったことで、反復的な UDF 呼び出しが、関数呼び出しのオーバーヘッドを完全に回避するプランに置き換えられます。
インライン化可能なスカラー UDF の要件
スカラー T-SQL UDF は、以下の条件がすべて満たされている場合にインライン化できます。
-
SELECT: 単一/複数の変数を割り当てたSQLクエリ 1. -
IFELSE: 任意のレベルの入れ子による分岐。 -
RETURN: 単一または複数のreturn文 -
UDF: 入れ子になった/再帰的な関数呼び出し2. - その他。
EXISTSISNULLのようなリレーショナル操作。
GETDATE()NEWSEQUENTIALID()など）内在的な関数を呼び出さない。EXECUTE AS CALLEREXECUTE AS句が指定されていない場合のデフォルトの動作）。GROUP BY 節でスカラー UDF コールを参照しません。DISTINCT 節を持つ SELECT リスト内でスカラー UDF を呼び出すクエリには ORDER BY 節がありません。ORDER BY句で使用されていません。@@ROWCOUNTなど）への参照を含みません 4。OBJECT_IDなど）を参照しません。TOP 1ORDER BY を持つ SELECT を含みません 5.ORDER BY 句と組み合わせて代入を実行する SELECT クエリが含まれていません（SELECT @x = @x + 1 FROM table1 ORDER BY col1 など） 5.STRING_AGG関数を参照しない 6.GROUPING SETSCUBEROLLUP
SELECT @y = 2@x = UDF(@y)）7。1 SELECTSELECT @val += col1 FROM table1など）。
2 再帰的なUDFは一定の深さまでしかインライン化されません。
3 結果が現在のシステム時間に依存する組込み関数は時間依存型です。 内部のグローバルな状態を更新する可能性のある組込み関数は、副作用のある関数の一例です。
4 Restriction added in SQL Server 2019 (15.x) CU2
5 Restriction added in SQL Server 2019 (15.x) CU4
6 Restriction added in SQL Server 2019 (15.x）CU5
7 SQL Server 2019（15.x）CU6で追加された制限事項
注
最新のT-SQL Scalar UDF Inliningの修正とインライン化の適格性シナリオの変更については、ナレッジベースの記事を参照してください。 FIX: Scalar UDF Inlining issues in SQL Server 2019.
UDFをインライン化できるかどうかの確認
すべてのT-SQLスカラーUDFについて、sys.sql_modulesカタログビューには、is_inlineableというプロパティが含まれており、UDFがインライン化可能かどうかを示します。 コンパイル時にUDFが実際にインライン化可能かどうかはチェックされません。 詳細は、インライン化の条件を参照してください。
値が1の場合はインライン化が可能であることを示し、0の場合はそうでないことを示します。 このプロパティは、すべてのインラインTVFに対しても値が1になります。
スカラ UDF がインライン化可能であっても、常にインライン化されることを意味するものではありません。 SQL ServerはUDFをインライン化するかどうかを(クエリ毎、UDF毎に)決定します。
-
UDF の定義が何千行ものコードに及ぶ場合、SQL Server はインライン化しないことを選択するかもしれません。 この決定は、スカラー UDF を参照するクエリがコンパイルされるときに行われます。
-
UDF が証明書で署名されている場合。 署名はUDFが作成された後に追加したり削除したりできるため、インライン化するかどうかの判断は、スカラーUDFを参照するクエリがコンパイルされるときに行われます。 例えば、システム関数は通常、証明書で署名されます。
SELECT * FROM sys.crypt_properties AS cpINNER JOIN sys.objects AS o ON cp.major_id = o.object_id;
インライン化が行われたかどうかの確認
すべての前提条件が満たされ、SQL Serverがインライン化の実行を決定すると、UDFをリレーショナル式に変換します。
- 計画xmlには、インライン化に成功したUDFの
<UserDefinedFunction>xmlノードはありません。 - 特定のXEventsが発行されます。
Scalar UDF Inliningの有効化
データベースの互換性レベル150を有効にすることで、ワークロードを自動的にScalar UDF Inliningの対象にすることができます。 この設定は、Transact-SQLを使用して行うことができます。
互換性レベルを変更せずにスカラー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;オンにすると、この設定は sys.database_scoped_configurationsDISABLE_TSQL_SCALAR_UDF_INLININGUSE HINTのクエリ・ヒントとして指定することで、特定のクエリに対してScalar UDF Inliningを無効にすることができます。
SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT)) FROM LINEITEMINNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATEOPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));ヒント
USE HINT クエリヒントは、データベース スコープの設定や互換性レベルの設定よりも優先されます。
Scalar UDF のインライニングは、CREATE FUNCTIONALTER FUNCTION ステートメントの INLINE 節を使用して、特定の UDF に対して無効にすることもできます。例えば、
CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))RETURNS DECIMAL (12,2)WITH INLINE = OFFASBEGIN RETURN @price * (1 - @discount);END;上記のステートメントが実行されると、このUDFは、それを呼び出すどのようなクエリにもインライン化されません。
CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))RETURNS DECIMAL (12,2)WITH INLINE = ONASBEGIN RETURN @price * (1 - @discount);END注意
INLINEINLINE句が指定されていない場合は、UDFがインライン化できるかどうかに基づいて、ONOFFが自動的に設定されます。
重要な注意事項
この記事で説明されているように、スカラー UDF のインライン化は、スカラー UDF を持つクエリを、同等のスカラーサブクエリを持つクエリに変換します。
- インライン化により、同じクエリテキストでも異なるクエリハッシュになります。
- 以前は隠れていたUDF内のステートメントの特定の警告（ゼロ除算など）が、インライン化により表示される可能性があります。
- インライン化により新しい結合が導入される可能性があるため、クエリレベルの結合ヒントが有効でなくなる可能性があります。
- インラインのスカラ UDF を参照するビューはインデックスを作成できません。 このようなビューにインデックスを作成する必要がある場合は、参照される UDF のインライン化を無効にしてください。
- UDF のインライン化によるダイナミック データのマスキングの動作には、いくつかの違いがあります。
- UDF が
SCOPE_IDENTITY()@@ROWCOUNT@@ERRORなどの組み込み関数を参照している場合、組み込み関数によって返される値はインライン化によって変化します。 この動作の変化は、インライン化によってUDF内部のステートメントのスコープが変わるためです。 SQL Server 2019 (15.x) CU2からは、UDFが特定の組み込み関数を参照している場合、インライン化がブロックされます（例：@@ROWCOUNT）。