外部APIやCSVから取得したキーの一覧を使って、データベースから該当レコードを取得したいことがある。単一列のキーならContainsで書けるが、複合キーになると少し厄介である。
たとえば、DB側のDbRecordとメモリ上のCompositeKeyをKey1、Key2で結合するものとする。
素直に書けば、次のようなJoinになる。
しかし、db.RecordsはSQLへ変換されるIQueryableであるのに対し、keysはメモリ上のオブジェクト集合である。この両者を複合キーで結合するJoinはSQLへ変換できず、実行時に例外となる。先にAsEnumerableを呼べばLINQ to Objectsとして結合できるが、そのためには照合前のDBレコードをクライアントへ読み込まなければならない。
照合をDB側で実行するには、複合キーの行をSQLへ渡せる形式に変換する必要がある。この記事では、そのために実装した次の2方式を比較する。
- 複合キーの行を
WHERE条件へ展開するCompositeKeyPredicateBuilder - 複合キーの行をJSONで渡し、SQL Serverの
OPENJSONで行セットに展開するOpenJsonQueryBuilder
コードは要点だけを抜粋している。完全な実装とベンチマーク一式はGitHubリポジトリで公開している。
式木でWHERE条件へ展開する
1つ目のCompositeKeyPredicateBuilderは、複合キーの列ごとの比較条件をANDでつなぎ、さらに複合キーの行ごとの条件をORでつないで、1つの述語を生成する。複合キーを2行渡した場合、次のような条件を生成する。
この形の式なら、EF Coreは通常のWhere条件としてSQLへ変換できる。呼び出し側は、エンティティ側と入力キー側の対応するプロパティを指定し、生成された述語を渡すだけである。
ビルダーの中心部分では、複合キーの各列に対応する比較式を作り、それらを行ごとに1つのExpression<Func<TEntity, bool>>へまとめている。
複合キーの行が0件の場合は常にfalseとなる述語を生成するため、呼び出し側に特別な分岐を設ける必要はない。また、プロパティ数の不一致、存在しないプロパティ名、型の不一致といった設定誤りは、ビルダーの構築時に例外として検出する。
式木方式はSQL Server固有の構文を使わず、既存のLINQへ組み込みやすい。一方、複合キーの各行について、複合キーの列ごとに比較条件を生成する。そのため、式とSQLの大きさは複合キーの行数×複合キーの列数に比例し、式の生成、SQLへの変換、メモリ割り当てのコストも増える。
複合キーの行をJSONで渡し、OPENJSONで展開する
2つ目のOpenJsonQueryBuilderは、複合キーの行をJSON配列にして1つのSQLパラメータで渡す。SQL Server側ではOPENJSONによってJSONを行セットに展開し、元のテーブルとJOINする。
生成されるSQLは次のようになる。
呼び出し側は、式木方式と同じくコンストラクターでエンティティ側と入力キー側の対応するプロパティを指定する。ただし、クエリの組み立て方は式木方式とは異なり、DbSetと複合キーの行をBuildへ渡しIQueryableを直接受け取る。
Buildは、エンティティの型、テーブル、スキーマ、プロパティの対応関係ごとにSQL文を初回利用時に生成してキャッシュし、以後の呼び出しで再利用する。複合キーの行は呼び出しのたびにJSONへシリアライズし、SQLパラメータとしてキャッシュ済みのSQLとともにFromSqlRawへ渡す。
生成するSQLには、EF Coreのモデルから取得した実際のテーブル名、列名、SQL型を使う。エンティティ側のプロパティが列にマッピングされていない、入力キー側にプロパティがない、CLR型が一致しない、といった設定誤りはビルダーが検出する。
CreateSqlでは、検証済みのプロパティからSELECT列、OPENJSONの入力列、JOIN条件をそれぞれ生成し、1つのSQL文に埋め込む。複合キーの行が重複した場合も結果を式木方式とそろえるため、JOIN前の行セットにSELECT DISTINCTを適用している。
式木方式と異なりOPENJSON方式では、複合キーの行数が増えても増えるのはJSON配列の要素だけである。SQL文自体は変わらず、パラメータもJSON文字列1つのままである。複合キーの列が1つ増えると、OPENJSONのWITH句の列定義とJOINの比較条件が1つずつ増えるが、この増加は行数には左右されない。
OPENJSON方式はSQL Server固有の構文と生SQLに依存するため、他のDBプロバイダーへそのまま移植はできない。
ベンチマーク
2方式を次の環境で測定した。
- .NET 10 / EF Core 10.0.0
- SQL Server 2022
- BenchmarkDotNet 0.15.6
- 複合キーの列の型は
intとvarchar(50) - DBレコード数は100,000件で固定
- 複合キーの行数は100、1,000、10,000
- 追跡処理の影響を除くため、両方式とも
AsNoTrackingを使用
DBには100,000件のレコードを用意し、複合キーの各行はテーブル全体から均等に選んでいる。すべての複合キーに一致するレコードが1件ずつ存在する。ベンチマークメソッドは、クエリで取得したList<DbRecord>をそのまま返す。
ベンチマークから呼び出す2つのメソッドは、次のクエリを実行する。どちらもAsNoTrackingを適用してから結果を取得している。
結果は次のとおりである。
実行時間は平均値 ± 標準偏差で示している。
| 複合キーの行数 | 式木 | OPENJSON | 式木のメモリ割り当て | OPENJSONのメモリ割り当て |
|---|---|---|---|---|
| 100 | 1.613 ± 0.053 ms | 1.536 ± 0.045 ms | 116.2 KB | 58.52 KB |
| 1,000 | 6.222 ± 0.072 ms | 4.927 ± 0.086 ms | 1,086.5 KB | 436.2 KB |
| 10,000 | 79.269 ± 1.278 ms | 49.578 ± 0.583 ms | 11,006.13 KB | 4,330.59 KB |
100行では、OPENJSON方式の実行時間は式木方式より0.077 ms短いだけで微差だが、メモリ割り当て量は約50%である。1,000行では、実行時間の差は1.295 msに広がり、OPENJSON方式の実行時間は式木方式の約79%、メモリ割り当て量は約40%となった。10,000行では、実行時間の差は29.691 msまで広がり、OPENJSON方式の実行時間は式木方式の約63%、メモリ割り当て量は約39%である。
この結果は、Windows 11（10.0.26200.9457）、AMD Ryzen 7 3800X（8コア、16スレッド）、.NET SDK 10.0.400、.NET 10.0.11 x64の環境で、BenchmarkDotNet 0.15.6のLongRunジョブ（起動3回、ウォームアップ15回、測定100回）を使って測定したものである。SQL Server 2022は同じPC上のDockerコンテナで実行した。
どちらを使うか
判断基準は、複合キーの行数とDB固有の実装を許容できるかどうかである。
| 条件 | 選びやすい方式 |
|---|---|
| SQL Server以外でも使いたい | 式木 |
| 通常のLINQとして組み立てたい | 式木 |
| 複合キーの行数が少ない | 式木 |
| SQL Serverに固定できる | OPENJSON |
| 複合キーの行数が多い | OPENJSON |
| SQLの肥大化とメモリ割り当てを抑えたい | OPENJSON |
複合キーの行数が少ない場合は、単純で扱いやすく、DB固有のコードも不要な式木方式が適している。複合キーの行数が多い場合、利用するDBをSQL Serverに限定できるなら、行数が増えてもSQL文が長くならないOPENJSON方式が有力である。
実行方法
リポジトリのDocker Compose構成を使えば、SQL Serverを起動できる。
接続文字列を設定し、Release構成でベンチマークを実行する。
まとめ
インメモリの複合キーの行をIQueryableへ上記のように直接Joinすると、EF CoreはそのクエリをSQLへ変換できない。DB側で照合するには、複合キーの行をSQLへ渡せる形式に変換する必要がある。
式木方式は、複合キーの行をANDとORからなるWhere条件へ変換する。LINQとして扱いやすくDB固有の機能を避けられるが、複合キーの行数×複合キーの列数に応じて式とSQLが大きくなる。
OPENJSON方式は、複合キーの行を1つのJSONパラメータで送り、SQL Server上で行セットに展開してJoinする。行数が増えてもSQL文は変わらず、複合キーの列が増えると、その分だけ列定義と比較条件が増える。SQL Serverに依存するが、今回の測定では行数が多いほど実行時間とメモリ割り当ての両方で有利であった。